SpatiaLite 5.0.0          SQL functions reference list

back

SQL Version Info [and build options testing] functions

Function Syntax Summary
spatialite_version spatialite_version( void ) : String returns the current SpatiaLite version as a text string
spatialite_target_cpu spatialite_target_cpu( void ) : String returns the current SpatiaLite Target CPU as a text string
check_strict_sql_quoting rcheck_strict_sql_quoting( void ) : String returns TRUE orFALSE depending on the actual behavior of current SQLite.
Note:SQLite can effectively enforce strict SQL quoting (single-quoted text constants and double-quoted table and column names) only since version 3.29.0, depending on build-time settings.
freexl_version freexl_version( void ) : String returns the current FreeXL version as a text string
or NULL if FreeXL is currently unsupported
proj_version proj_version( void ) : String
proj4_version( void ) : Sting		 returns the current PROJ version as a text string
or NULL if PROJ is currently unsupported.
Due to historical reasons there are two alias names for the same functionality.
Note: proj4_version() is now deprecated and will be possibly removed in future versions.
geos_version geos_version( void ) : String returns the current GEOS version as a text string
or NULL if GEOS is currently unsupported
rttopo_version rttopo_version( void ) : String returns the current RTTOPO version as a text string
or NULL if RTTOPO is currently unsupported
libxml2_version libxml2_version( void ) : String returns the current LibXML2 version as a text string
or NULL if LibXML2 is currently unsupported
HasIconv HasIconv( void ) : Boolean TRUE if the underlying library was built enabling ICONV
HasMathSQL HasMathSQL( void ) : Boolean TRUE if the underlying library was built enabling MATHSQL
HasGeoCallbacks HasGeoCallbacks( void ) : Boolean TRUE if the underlying library was built enabling GEOCALLBACKS
HasProj HasProj( void ) : Boolean TRUE if the underlying library was built enabling PROJ
HasProj6 HasProj6( void ) : Boolean TRUE if the underlying library was built enabling PROJ version 6 or any later
HasGeos HasGeos( void ) : Boolean TRUE if the underlying library was built enabling GEOS
HasGeosAdvanced HasGeosAdvanced( void ) : Boolean TRUE if the underlying library was built enabling GEOSADVANCED
HasGeosTrunk HasGeosTrunk( void ) : Boolean TRUE if the underlying library was built enabling GEOSTRUNK
HasGeosReentrant HasGeosReentrant( void ) : Boolean TRUE if the underlying library was built enabling GEOSREENTRANT
HasGeosOnlyReentrant HasGeosOnlyReentrant( void ) : Boolean TRUE if the underlying library was built enabling GEOSONLYREENTRANT
HasMiniZip HasMiniZip( void ) : Boolean TRUE if the underlying library was built enabling MINIZIP
HasRtTopo HasRtTopo( void ) : Boolean TRUE if the underlying library was built enabling RTTOPO
HasLibXML2 HasLibXML2( void ) : Boolean TRUE if the underlying library was built enabling LibXML2
HasEpsg HasEpsg( void ) : Boolean TRUE if the underlying library was built enabling EPSG
HasFreeXL HasFreeXL( void ) : Boolean TRUE if the underlying library was built enabling FREEXL
HasGeoPackage HasGeoPackage( void ) : Boolean TRUE if the underlying library was built enabling GeoPackage support (GPKG)
HasGCP HasGCP( void ) : Boolean
HasGroundControlPoints ( void ) : Boolean		 TRUE if the underlying library was built enabling Ground Control Points support (GGP)
HasTopology HasTopology( void ) : Boolean TRUE if the underlying library was built enabling Topology (RTTOPO) support
HasKNN HasKNN( void ) : Boolean TRUE if the underlying library was built enabling VirtualKNN (KNN) support
HasRouting HasRouting( void ) : Boolean TRUE if the underlying library was built enabling VirtualRouting support

Generic SQL functions

Function Syntax Summary
IsInteger IsInteger( value Text ) : Integer Checks a TEXT string testing if it corresponds to an Integer Number.
The function returns 1 if TRUE and 0 if FALSE; -1 is returned when the argument is not a Text string.
Examples of valid Integer strings:
  • '1234'
  • '+5678'
  • '-4321'
  • '      +12345678'
IsDecimalNumber IsDecimalNumber( value Text ) : Integer Checks a TEXT string testing if it corresponds to a Decimal Number.
The function returns 1 if TRUE and 0 if FALSE; -1 is returned when the argument is not a Text string.
Examples of valid Decimal Number strings:
  • '1234.56'
  • '+5678.09'
  • '-4321.67'
  • '-4321.67e12'
  • '6789.123E+6'
  • '      +1234.56e-6'
IsNumber IsNumber( value Text ) : Integer Checks a TEXT string testing if it corresponds to a Number.
The function returns 1 if TRUE and 0 if FALSE; -1 is returned when the argument is not a Text string.
convenience predicate: same as IsInteger(value) OR IsDecimalNumber(value).
CastToInteger CastToInteger( value Generic ) : Integer returns the intput value possibly casted to the Integer data-type; NULL if no conversion is possible.
CastToDouble CastToDouble( value Generic ) : Double precision returns the intput value possibly casted to the Double data-type; NULL if no conversion is possible.
CastToText CastToText( value Generic ) : Text
CastToText( value Generic , zero_pad Integer ) : Text		 returns the intput value possibly casted to the Text data-type; NULL if no conversion is possible.
If an optional argument zero_pad is passed and the input value is of the Integer or Double type, then the returned string will be padded using as many trailing ZEROs needed to ensure the required length.
CastToBlob CastToBlob( value Generic ) : Blob
CastToBlob( value Generic , hex_input Boolean ) : Blob		 returns the intput value possibly casted to the BLOB data-type: if the optional argument hex_input is set to TRUE the input value will be expected to correspond to an HexaDecimal string, e.g. 01ab89EF; if this conversion fails then NULL will be returned.
returns NULL if no conversion is possible.
ForceAsNull ForceAsNull( val1 Generic , val2 Generic) : Generic if val1 and val2 are equal (and of the same data-type) NULL will be returned; otherwise val1 will be returned unchanged, preserving its original data-type.
GetDbObjectScope GetDbObjectScope( db-prefix Text , obj-name Text ) : Text
  • db-prefix can be NULL, and in this case the "MAIN" database will be assumed.
  • obj-name must identify any valid DB-Object (Table. View, Trigger or Index).
Returns a short description about the intended scope of any valid DB-Object.
NULL on invalid DB-objects.
CreateUUID CreateUUID( void ) : Text returns a Version 4 (random) UUID (Universally unique identifier).
MD5Checksum MD5Checksum( BLOB | TEXT ) : Text returns the MD5 checksum corresponding to the input value.
Will return NULL for non-BLOB or non-TEXT input.
MD5TotalChecksum MD5TotalChecksum( BLOB | TEXT ) : Text returns a cumulative MD5 checksum.
aggregate function
EncodeURL EncodeURL( url Text ) : Text
EncodeURL( url Text , charset Text ) : Text		 returns the percent encoded URL corresponding to the input value.
Will return NULL for invalid input.
  • the input URL is always assumed to be an UTF-8 string.
  • the output URL will be converted into the charset encoding specified by the charset argument.
    if not specified UTF-8 will be assumed by default.
DecodeURL DecodeURL( url Text ) : Text
DecodeURL( url Text, charset Text ) : Text		 returns a plain URL from its corresponding percent encoding.
Will return NULL for invalid input.
  • the output URL will be always returned as an UTF-8 string.
  • the input URL will be converted from the charset encoding specified by the charset argument.
    if not specified UTF-8 will be assumed by default.
DirNameFromPath DirNameFromPath( TEXT ) : Text returns the Directory Name from a relative or absolute Pathname.
Will return NULL for invalid input of for any simple path lacking a Directory.
FullFileNameFromPath FullFileNameFromPath( TEXT ) : Text returns the Full File Name (including an eventual File Extension) from a relative or absolute Pathname.
Will return NULL for invalid input of for any path lacking a File Name.
FileNameFromPath FileNameFromPath( TEXT ) : Text returns the File Name (excluding an eventual File Extension) from a relative or absolute Pathname.
Will return NULL for invalid input of for any path lacking a File Name.
FileExtFromPath FileExtFromPath( TEXT ) : Text returns the File Extension from a relative or absolute Pathname.
Will return NULL for invalid input of for any path lacking a File Name or when no Extension is present.
RemoveExtraSpaces RemoveExtraSpaces( TEXT ) : Text returns a text string containing no repeated whitespaces (SPACE or TAB characters).
Will return NULL for invalid input.
MakeStringList MakeStringList( value ) : Text
MakeStringList( value , delimiter text) : Text		 returns a comma-delimited list of integer or text values.
the optional argument delimiter can be used so to specify an alternative delimiter different from comma.
aggregate function
Will return NULL for invalid arguments.
eval eval( X TEXT [ , Y TEXT ) : Text Evaluate the SQL text in X. Return the results, using string Y as the separator.
If Y is omitted, use a single space character.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.
PostgreSQL_GetLastError PostgreSQL_GetLastError() : Text returns the most recent error message raised by PostgreSQL.
NULL if there is no pending error message available.
PostgreSQL_ResetLastError PostgreSQL_ResetLastError() : Integer Resets the most recent error message raised by PostgreSQL.
Returns 1 on success and 0 on failure.
Only intended for internal usage by the VirtualPostgres extension module.
PostgreSQL_SetLastError PostgreSQL_SetLastError( TEXT ) : Integer Permanently sets the most recent error message raised by PostgreSQL.
Returns 1 on success and 0 on failure; -1 if the argument is not a Text string.
Only intended for internal usage by the VirtualPostgres extension module.

Global settings per connection

Function Syntax Summary
EnableGpkgMode EnableGpkgMode( void ) : void Enables the Geopackage mode
All connections are initially started with a disabled GPKG mode, that must be explicitly enabled whenever required.
Enabling GPKG mode is a supported option only if the currently connected DB-file presents a GPKG layout.
DisableGpkgMode DisableGpkgMode( void ) : void Disables the Geopackage mode
GetGpkgMode GetGpkgMode( void ) : boolean Returns TRUE if the Geopackage mode is currently enabled, otherwise FALSE
EnableGpkgAmphibiousMode EnableGpkgAmphibiousMode( void ) : void Enables the Geopackage amphibious mode
All connections are initially started with a disabled amphibious mode, that must be explicitly enabled whenever required.
Note: GPKG mode and GPKG amphibious mode are mutually exclusive options.
DisableGpkgAmphibiousMode DisableGpkgAmphibiousMode( void ) : void Disables the Geopackage amphibious mode
GetGpkgAmphibiousMode GetGpkgAmphibiousMode( void ) : boolean Returns TRUE if the Geopackage amphibious mode is currently enabled, otherwise FALSE
SetDecimalPrecision SetDecimalPrecision( integer ) : void Explicitly sets the number of decimal digits (precision) to be displayed by ST_AsText() for coordinate values: the standard default setting is 6 decimal digits.
Passing any negative precision will automatically restore the initial default setting.
The spatialite_gui tool will honor this setting for all floating point values to be displayed on the screen.
GetDecimalPrecision GetDecimalPrecision( void ) : integer Returns the currently set decimal precision.
A negative precision identifies the default setting.
EnableTinyPoint EnableTinyPoint( void ) : void Enables the TinyPoint BLOB encoding for all Point-Geometries being created.
All connections are initially started with a disabled TinyPoint BLOB encoding, that must be explicitly enabled whenever required.
Exception: if the environment variable SPATIALITE_TINYPOINT=1 is set, then all connections will initially start by enabling the TinyPoint BLOB encoding.
DisableTinyPoint DisableTinyPoint( void ) : void Disables the TinyPoint BLOB encoding; all Point-Geometries will then be created applying the classic BLOB-Geometry encoding.
IsTinyPointEnabled IsTinyPointEnabled( void ) : boolean Returns TRUE if the TinyPoint BLOB encoding is currently enabled, otherwise FALSE
BufferOptions_Reset BufferOptions_Reset( void ) : boolean Will reset all BufferOptions to their initial default settings.
Returns TRUE on success, FALSE on failure.
BufferOptions_SetEndCapStyle BufferOptions_SetEndCapStyle( style Text ) : boolean Will set the current EndCap Style. Accepted styles (case insensitive) are: ROUND, FLAT, SQUARE
Returns TRUE on success, FALSE on failure.
BufferOptions_GetEndCapStyle BufferOptions_GetEndCapStyle( void ) : string Will return the name of the currently set EndCap Style.
NULL on failure.
BufferOptions_SetJoinStyle BufferOptions_SetJoinStyle( style Text ) : boolean Will set the current Join Style. Accepted styles (case insensitive) are: ROUND, MITRE or MITER, BEVEL
Returns TRUE on success, FALSE on failure.
BufferOptions_GetJoinStyle BufferOptions_GetJoinStyle( void ) : string Will return the name of the currently set Join Style.
NULL on failure.
BufferOptions_SetMitreLimit BufferOptions_SetMitreLimit( limit Double ) : boolean Will set the current Mitre Limit value.
Returns TRUE on success, FALSE on failure.
BufferOptions_GetMitreLimit BufferOptions_GetMitreLimit( void ) : double Will return the value of the currently set Mitre Limit.
NULL on failure.
BufferOptions_SetQuadrantSegments BufferOptions_SetQuadrantSegments( points Integer ) : boolean Will set the current Quadrant Segments value.
Returns TRUE on success, FALSE on failure.
BufferOptions_GetQuadrantSegments BufferOptions_GetQuadrantSegments( void ) : integer Will return the value of the currently set Quadrant Segments.
NULL on failure.

SQL functions manipulating Sequences

Function Syntax Summary
sequence_nextval sequence_nextval ( seq_name Text ) : Integer advances to the next value of the Sequence, which is then returned.
Will return NULL if any error occurred.
sequence_currval sequence_currval ( seq_name Text ) : Integer returns the value most recently obtained by sequence_nextval() for the Sequence identified by seq_name
Will return NULL if the Sequence identified by seq_name has not yet been used.
sequence_lastval sequence_lastval ( void ) : Integer returns the value most recently obtained by sequence_nextval()
Will return NULL if sequence_nextval() has not yet been used.
sequence_setval sequence_setval ( seq_name Text , value Integer ) : Integer sets the current value for the Sequence identified by seq_name; if the Sequence doesn't yet exist it will be created on-the-fly.
Will return value on success or NULL on failure.

SQL math functions

Function Syntax Summary
Abs Abs( x Double precision ) : Double precision returns the absolute value of x
Acos Acos( x Double precision ) : Double precision returns the arc cosine of x, that is, the value whose cosine is x
returns NULL if x is not within the range -1 to 1
Asin Asin( x Double precision ) : Double precision returns the arc sine of x, that is, the value whose sine is x
returns NULL if x is not in the range -1 to 1
Atan Atan( x Double precision ) : Double precision returns the arc tangent of x, that is, the value whose tangent is x
Atan2 Atan2( y Double precision , x Double precision ) : Double precision returns the principal value of the arc tangent of y/x in radians, using the signs of the two arguments to determine the quadrant of the result. The return value is in the range[-pi, pi].
Ceil
Ceiling		 Ceil( x Double precision ) : Double precision
Ceiling( x Double precision ) : Double precision		 returns the smallest integer value not less than x
Cos Cos( x Double precision ) : Double precision returns the cosine of x, where x is given in radians
Cot Cot( x Double precision ) : Double precision returns the cotangent of x, where x is given in radians
Degrees Degrees( x Double precision ) : Double precision returns the argument x, converted from radians to degrees
Exp Exp( x Double precision ) : Double precision returns the value of e (the base of natural logarithms) raised to the power of x
the inverse of this function is Log() (using a single argument only) or Ln()
Floor Floor( x Double precision ) : Double precision returns the largest integer value not greater than x
Ln
Log		 Ln( x Double precision ) : Double precision
Log( x Double precision ) : Double precision		 returns the natural logarithm of x; that is, the base-e logarithm of x
If x is less than or equal to 0, then NULL is returned
Log Log( x Double precision , b Double precision ) : Double precision returns the logarithm of x to the base b
If x is less than or equal to 0, or if b is less than or equal to 1, then NULL is returned
Log(x, b) is equivalent to Log(x) / Log(b)
Log2 Log2( x Double precision ) : Double precision returns the base-2 logarithm of x
Log2(x) is equivalent to Log(x) / Log(2)
Log10 Log10( x Double precision ) : Double precision returns the base-10 logarithm of x
Log10(x) is equivalent to Log(x) / Log(10)
PI PI( void ) : Double precision returns the value of PI
Pow
Power		 Pow( x Double precision , y Double precision ) : Double precision
Power( x Double precision , y Double precision ) : Double precision		 returns the value of x raised to the power of y
Radians Radians( x Double precision ) : Double precision returns the argument x, converted from degrees to radians
Sign Sign( x Double precision ) : Double precision returns the sign of the argument as -1, 0, or 1, depending on whether x is negative, zero, or positive.
Sin Sin( x Double precision ) : Double precision returns the sine of x, where x is given in radians
Sqrt Sqrt( x Double precision ) : Double precision returns the square root of a non-negative number x
Stddev_pop Stddev_pop( x Double precision ) : Double precision returns the population standard deviation of the input values
aggregate function
Stddev_samp Stddev_samp( x Double precision ) : Double precision returns the sample standard deviation of the input values
aggregate function
Tan Tan( x Double precision ) : Double precision returns the tangent of x, where x is given in radians
Var_pop Var_pop( x Double precision ) : Double precision returns the population variance of the input values (square of the population standard deviation)
aggregate function
Var_samp Var_samp( x Double precision ) : Double precision returns the sample variance of the input values (square of the sample standard deviation)
aggregate function

SQL functions reporting PROJ / GEOS / RTTOPO errors and warnings

Function Syntax Summary
PROJ_GetLastErrorMsg PROJ_GetLastErrorMsg( void ) : String PROJ Will return the most recent error message returned by PROJ (if any).
NULL will be returned if there is no pending PROJ error.
Note: this SQL function will be available only when SpatiaLite is built on PROJ.6 (or any later version).
GEOS_GetLastWarningMsg GEOS_GetLastWarningMsg( void ) : String GEOS Will return the most recent warning message returned by GEOS (if any).
NULL will be returned if there is no pending GEOS warning.
GEOS_GetLastErrorMsg GEOS_GetLastErrorMsg( void ) : String GEOS Will return the most recent error message returned by GEOS (if any).
NULL will be returned if there is no pending GEOS error.
GEOS_GetLastAuxErrorMsg GEOS_GetLastAuxErrorMsg( void ) : String GEOS Will return the most recent error message (auxiliary) returned by GEOS (if any).
NULL will be returned if there is no pending GEOS (auxiliary) error.
GEOS_GetCriticalPointFromMsg GEOS_GetCriticalPointFromMsg( void ) : Point
GEOS_GetCriticalPointFromMsg( SRID Integer ) : Point		 GEOS Will (possibly) return a Point Geometry extracted from the latest error / warning message returned by GEOS.
NULL will be returned if there is no pending GEOS message, or if the current GEOS message doesn't contain a critical Point.
RTTOPO_GetLastWarningMsg RTTOPO_GetLastWarningMsg( void ) : String RTTOPO Will return the most recent warning message returned by RTTOPO (if any).
NULL will be returned if there is no pending RTTOPO warning.
RTTOPO_GetLastErrorMsg RTTOPO_GetLastErrorMsg( void ) : String RTTOPO Will return the most recent error message returned by RTTOPO (if any).
NULL will be returned if there is no pending RTTOPO error.

SQL length/distance unit-conversion functions

Function Syntax Summary
Kilometer CvtToKm( x Double precision ) : Double precision
CvtFromKm( x Double precision ) : Double precision		 meters / kilometers
Decimeter CvtToDm( x Double precision ) : Double precision
CvtFromDm( x Double precision ) : Double precision		 meters / decimeters
Centimeter CvtToCm( x Double precision ) : Double precision
CvtFromCm( x Double precision ) : Double precision		 meters / centimeters
Millimeter CvtToMm( x Double precision ) : Double precision
CvtFromMm( x Double precision ) : Double precision		 meters / millimeters
International Nautical Mile CvtToKmi( x Double precision ) : Double precision
CvtFromKmi( x Double precision ) : Double precision		 meters / international nautical miles
International Inch CvtToIn( x Double precision ) : Double precision
CvtFromIn( x Double precision ) : Double precision		 meters / international inches
International Foot CvtToFt( x Double precision ) : Double precision
CvtFromFt( x Double precision ) : Double precision		 meters / international feet
International Yard CvtToYd( x Double precision ) : Double precision
CvtFromYd( x Double precision ) : Double precision		 meters / international yards
International Statute Mile CvtToMi( x Double precision ) : Double precision
CvtFromMi( x Double precision ) : Double precision		 meters / international statute miles
International Fathom CvtToFath( x Double precision ) : Double precision
CvtFromFath( x Double precision ) : Double precision		 meters / international fathoms
International Chain CvtToCh( x Double precision ) : Double precision
CvtFromCh( x Double precision ) : Double precision		 meters / international chains
International Link CvtToLink( x Double precision ) : Double precision
CvtFromLink( x Double precision ) : Double precision		 meters / international links
U.S. Inch CvtToUsIn( x Double precision ) : Double precision
CvtFromUsIn( x Double precision ) : Double precision		 meters / U.S. inches
U.S. Foot CvtToUsFt( x Double precision ) : Double precision
CvtFromUsFt( x Double precision ) : Double precision		 meters / U.S. feet
U.S. Yard CvtToUsYd( x Double precision ) : Double precision
CvtFromUsYd( x Double precision ) : Double precision		 meters / U.S. yards
U.S. Statute Mile CvtToUsMi( x Double precision ) : Double precision
CvtFromUsMi( x Double precision ) : Double precision		 meters / U.S. statute miles
U.S. Chain CvtToUsCh( x Double precision ) : Double precision
CvtFromUsCh( x Double precision ) : Double precision		 meters / U.S. chains
Indian Foot CvtToIndFt( x Double precision ) : Double precision
CvtFromIndFt( x Double precision ) : Double precision		 meters / indian feet
Indian Yard CvtToIndYd( x Double precision ) : Double precision
CvtFromIndYd( x Double precision ) : Double precision		 meters / indian yards
Indian Chain CvtToIndCh( x Double precision ) : Double precision
CvtFromIndCh( x Double precision ) : Double precision		 meters / indian chains

SQL conversion functions from DD/DMS notations (longitude/latitude)

Function Syntax Summary
DD to DMS LongLatToDMS( longitude Double precision , latitude Double precision ) : String
LongLatToDMS( longitude Double precision , latitude Double precision , decimal_digits Integer ) : String		 will return a DMS string (Degrees, Minutes and Seconds) using the given DD (Decimal Degrees) input coordinates
  • the optional argument decimal_digits, when explicitly specified, will determine how many decimal digits must be used for representig Seconds.
    Zero (or a negative value) intends that Seconds will be represented by an integer value (default setting).
NULL will be returned on invalid input.
DMS to DD LongitudeFromDMS( dms_expression String ) : Double precision
LatitudeFromDMS( dms_expression String ) : Double precision		 will return the DD coordinates from within a DMS expression, which must contain both the Latitude and Longitude (in that order)
NULL will be returned on invalid input.

SQL utility functions for BLOB objects

Function Syntax Summary
IsZipBlob IsZipBlob( content BLOB ) : Integer The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL or non-BLOB argument.
TRUE if this BLOB object corresponds to a valid ZIP-compressed file
IsPdfBlob IsPdfBlob( content BLOB ) : Integer The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL or non-BLOB argument.
TRUE if this BLOB object corresponds to a valid PDF document
IsGifBlob IsGifBlob( image BLOB ) : Integer The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL or non-BLOB argument.
TRUE if this BLOB object corresponds to a valid GIF image
IsPngBlob IsPngBlob( image BLOB ) : Integer The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL or non-BLOB argument.
TRUE if this BLOB object corresponds to a valid PNG image
IsTiffBlob IsTiffBlob( image BLOB ) : Integer The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL or non-BLOB argument.
TRUE if this BLOB object corresponds to a valid TIFF image
IsJpegBlob IsJpegBlob( image BLOB ) : Integer The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL or non-BLOB argument.
TRUE if this BLOB object corresponds to a valid JPEG image
IsExifBlob IsExifBlob( image BLOB ) : Integer The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL or non-BLOB argument.
TRUE if this BLOB object corresponds to a valid EXIF image
Please note: any valid EXIF image is also a valid JPEG
IsExifGpsBlob IsExifGpsBlob( image BLOB ) : Integer The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL or non-BLOB argument.
TRUE if this BLOB object corresponds to a valid EXIF-GPS image
Please note: any valid EXIF-GPS image is a valid EXIF and JPEG as well
IsWebpBlob IsWebpBlob( image BLOB ) : Integer The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL or non-BLOB argument.
TRUE if this BLOB object corresponds to a valid WebP image
IsJP2Blob IsJP2Blob( image BLOB ) : Integer The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL or non-BLOB argument.
TRUE if this BLOB object corresponds to a valid JP2 [Jpeg2000] image
GetMimeType GetMimeType( payload BLOB ) : String The return type is Text, and could be one of: image/gif, image/png, image/jpeg, image/jp2, image/tiff, image/svg+xml, application/xml, application/zip, application/pdf.
NULL will be returned when called with a NULL or non-BLOB argument, or if no valid mime-type is detected.
IsGeometryBlob IsGeometryBlob( content BLOB ) : Integer The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL or non-BLOB argument.
TRUE if the BLOB argument is a valid BLOB-Geometry.
IsCompressedGeometryBlob IsCompressedGeometryBlob( content BLOB ) : Integer The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL or non-BLOB argument.
TRUE if the BLOB argument is a valid BLOB-Geometry.
IsTinyPointBlob IsTinyPointBlob( content BLOB ) : Integer The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL or non-BLOB argument.
TRUE if the BLOB argument is a valid BLOB-TinyPoint.
TinyPointEncode TinyPointEncode( content POINT BLOB-GEOMETRY ) : BLOB-TinyPoint If the input argument corresponds to a valid BLOB-GEOMETRY of the POINT, POINT Z, POINT M or POINT ZM type the corresponding BLOB-TinyPoint will be returned.
In any other case the input argument will be retuned.
GeometryPointEncode GeometryPointEncode( content BLOB-TinyPoint ) : BLOB-GEOMETRY If the input argument corresponds to a valid BLOB-TinyPoint the corresponding BLOB-GEOMETRY will be returned.
In any other case the input argument will be retuned.
BlobFromFile BlobFromFile( filepath String ) : BLOB If the filepath is valid, and the existing file can be successfully read, then the whole file content will be returned as a BLOB value.
Otherwise NULL will be returned.
Please note: SQLite doesn't support BLOB values bigger than SQLITE_MAX_LENGTH (usually, 1 GB).
Please note well: this SQL function opens the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is required in order to use this function.
BlobToFile BlobToFile( binary-data BLOB , filepath String ) : Integer If binary-data is of the BLOB-type, and the filepath is valid (i.e. accessible in write/create mode), then the corresponding file will be created/overwritten with the binary-data.
The return type is Integer, with a return value of 1 for success, 0 for failure.
Please note well: this SQL function opens the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is required in order to use this function.
CountUnsafeTriggers CountUnsafeTriggers( ) : Integer This SQL function checks if the currently connected DB contains any potentially malicious Triggers; carefully checking this conditions is a minimal precaution expected to be always verified before eventually activating the SPATIALITE_SECURITY=relaxed mode.
The return type is Integer (total count of suspected Triggers); 0 means that the currently connected DB should be considered absolutely safe.

SQL utility functions [non-standard] for geometric objects

Function Syntax OGC
defined		 required
module		 Summary
GeomFromExifGpsBlob GeomFromExifGpsBlob( image BLOB ) : Geometry base a POINT Geometry will be returned representing the GPS long/lat contained within EXIF-GPS metadata for the BLOB image
NULL will be returned if for any reason it's not possible to create such a POINT
ST_Point ST_Point( x Double precision , y Double precision ) : Geometry base simply an alias-name for MakePoint()
Please note: the SRID argument is never supported by ST_Point()
MakePoint MakePoint( x Double precision , y Double precision , [ , SRID Integer] ) : Geometry base a Geometry will be returned representing the POINT defined by [x y] coordinates
MakePointZ MakePointZ( x Double precision , y Double precision , z Double precision , [ , SRID Integer] ) : Geometry base a Geometry will be returned representing the POINT Z defined by [x y z] coordinates
MakePointM MakePointM( x Double precision , y Double precision , m Double precision , [ , SRID Integer] ) : Geometry base a Geometry will be returned representing the POINT M defined by [x y m] coordinates
MakePointZM MakePointZM( x Double precision , y Double precision , z Double precision , m Double precision [ , SRID Integer] ) : Geometry base a Geometry will be returned representing the POINT ZM defined by [x y z m] coordinates
MakeLine MakeLine( pt1 PointGeometry , pt2 PointGeometry ) : LinestringGeometry base a Linestring Geometry will be returned representing the segment connecting pt1 to pt2
NULL will be returned if any error is encountered
MakeLine MakeLine( geom PointGeometry ) : LinestringGeometry base a Linestring Geometry will be returned connecting all the input Points (accordingly to input sequence)
aggregate function
NULL will be returned if any error is encountered
MakeLine MakeLine( geom MultiPointGeometry , direction Boolean ) : LinestringGeometry base a Linestring Geometry will be returned connecting all the input Points (accordingly to input sequence); direction=FALSE implies reverse order.
Please note: similar to the previuous one, but this one is an ordinary (not aggregate) function; a MultiPoint input is always expected.
NULL will be returned if any error is encountered
MakeCircle MakeCircle( cx Double precision , cy Double precision , radius Double precision [ , SRID Integer [ , step Double precision ] ] ) : Geometry base will return a closed LINESTRING approximating the Circle defined by cx, cy and radius.
The optional argument step if specified defines how many points will be interpolated on the circumference; a point will be set every step degrees.
The implicit default setting corresponds to a point every 10 degrees.
MakeEllipse MakeEllipse( cx Double precision , cy Double precision , x_axis Double precision , y_axis Double precision [ , SRID Integer [ , step Double precision ] ] ) : Geometry base will return a closed LINESTRING approximating the Ellipse defined by cx, cy and x_axis, y_axis.
The optional argument step if specified defines how many points will be interpolated on the ellipse; a point will be set every step degrees.
The implicit default setting corresponds to a point every 10 degrees.
MakeArc MakeArc( cx Double precision , cy Double precision , radius Double precision , start Double precision , stop Double precision [ , SRID Integer [ , step Double precision ] ] ) : Geometry base will return a LINESTRING approximating the Circular Arc defined by cx, cy and radius; the arc's extremities will be defined by start, stop angles expressed in degrees.
The optional argument step if specified defines how many points will be interpolated on the circumference; a point will be set every step degrees.
The implicit default setting corresponds to a point every 10 degrees.
MakeEllipticArc MakeEllipticArc( cx Double precision , cy Double precision , x_axis Double precision , y_axis Double precision , start Double precision , stop Double precision [ , SRID Integer [ , step Double precision ] ] ) : Geometry base will return a LINESTRING approximating the Elliptic Arc defined by cx, cy and x_axis, y_axis; the arc's extremities will be defined by start, stop angles expressed in degrees.
The optional argument step if specified defines how many points will be interpolated on the ellipse; a point will be set every step degrees.
The implicit default setting corresponds to a point every 10 degrees.
MakeCircularSector MakeCircularSector( cx Double precision , cy Double precision , radius Double precision , start Double precision , stop Double precision [ , SRID Integer [ , step Double precision ] ] ) : Geometry base will return a POLYGON approximating the Circular Sector defined by cx, cy and radius; the arc's extremities will be defined by start, stop angles expressed in degrees.
The optional argument step if specified defines how many points will be interpolated on the circumference; a point will be set every step degrees.
The implicit default setting corresponds to a point every 10 degrees.
MakeEllipticSector MakeEllipticSector( cx Double precision , cy Double precision , x_axis Double precision , y_axis Double precision , start Double precision , stop Double precision [ , SRID Integer [ , step Double precision ] ] ) : Geometry base will return a POLYGON approximating the Elliptic Sector defined by cx, cy and x_axis, y_axis; the arc's extremities will be defined by start, stop angles expressed in degrees.
The optional argument step if specified defines how many points will be interpolated on the ellipse; a point will be set every step degrees.
The implicit default setting corresponds to a point every 10 degrees.
MakeCircularStripe MakeCircularStripe( cx Double precision , cy Double precision , radius_1 Double precision , radius_2 Double precision , start Double precision , stop Double precision [ , SRID Integer [ , step Double precision ] ] ) : Geometry base will return a POLYGON approximating the Circular Stripe delimited by two arcs sharing the same Centre [cx, cy] but having different radii [radius_1, radius_2]; the arc's extremities will be defined by start, stop angles expressed in degrees.
The optional argument step if specified defines how many points will be interpolated on the circumference; a point will be set every step degrees.
The implicit default setting corresponds to a point every 10 degrees.
SquareGrid SquareGrid( geom ArealGeometry , size Double precision [ , mode Integer , [ origin PointGeometry ] ] ) : Geometry
ST_SquareGrid( geom ArealGeometry , size Double precision [ , mode Integer , [ origin PointGeometry ] ] ) : Geometry		 GEOS return a grid of square cells (having the edge length of size) precisely covering the input Geometry.
The specific Type of returned Geometry is controlled by the mode attribute value:
  • any positive value will cause a MultiLinestring to be returned.
  • zero (default value) will cause a MultiPolygon to be returned.
  • any negative value will cause a MultiPoint to be returned.
If the optional origin argument (expected to be a Point) is not specified then the (0,0) grid origin will be assumed by default.
NULL will be returned if any error is encountered.
TriangularGrid TriangularGrid( geom ArealGeometry , size Double precision [ , mode Integer , [ origin PointGeometry ] ] ) : Geometry
ST_TriangularGrid( geom ArealGeometry , size Double precision [ , mode Integer , [ origin PointGeometry ] ] ) : Geometry		 GEOS return a grid of triangular cells (having the edge length of size) precisely covering the input Geometry.
The specific Type of returned Geometry is controlled by the mode attribute value:
  • any positive value will cause a MultiLinestring to be returned.
  • zero (default value) will cause a MultiPolygon to be returned.
  • any negative value will cause a MultiPoint to be returned.
If the optional origin argument (expected to be a Point) is not specified then the (0,0) grid origin will be assumed by default.
NULL will be returned if any error is encountered.
HexagonalGrid HexagonalGrid( geom ArealGeometry , size Double precision [ , mode Integer , [ origin PointGeometry ] ] ) : Geometry
ST_HexagonalGrid( geom ArealGeometry , size Double precision [ , mode Integer , [ origin PointGeometry ] ] ) : Geometry		 GEOS return a grid of hexagonal cells (having the edge length of size) precisely covering the input Geometry.
The specific Type of returned Geometry is controlled by the mode attribute value:
  • any positive value will cause a MultiLinestring to be returned.
  • zero (default value) will cause a MultiPolygon to be returned.
  • any negative value will cause a MultiPoint to be returned.
If the optional origin argument (expected to be a Point) is not specified then the (0,0) grid origin will be assumed by default.
NULL will be returned if any error is encountered.
BuildMbr BuildMbr( x1 Double precision , y1 Double precision , x2 Double precision , y2 Double precision [ , SRID Integer] ) : Geometry base [x1 y1] and [x2 y2] are assumed to be Points identifying a line segment; then a Geometry will be returned representing the MBR for this line segment
BuildCircleMbr BuildCircleMbr( x Double precision , y Double precision , radius Double precision [ , SRID Integer] ) : Geometry base [x y] is assumed to be the center of a circle of given radius; then a Geometry will be returned representing the MBR for this circle
Extent Extent( geom Geometry ) : Geometry base return a geometric object representing the bounding box that encloses a set of input values
aggregate function
ToGARS ToGARS( geom Geometry ) : String base geom is expected to represent a POINT (longitude and latitude coordinates); the corresponding GARS area designation code will be returned.
NULL will be returned if any error is encountered.
GARSMbr GARSMbr( code String ) : Geometry base code is assumed to represent a valid GARS area designation code; a Geometry will be returned representing the MBR for the corresponding GARS area.
NULL will be returned if any error is encountered.
MbrMinX MbrMinX( geom Geometry) : Double precision
ST_MinX( geom Geometry) : Double precision		 base return the x-coordinate for geom MBR's leftmost side as a double precision number.
NULL will be returned if geom isn't a valid Geometry.
MbrMinY MbrMinY( geom Geometry) : Double precision
ST_MinY( geom Geometry) : Double precision		 base return the y-coordinate for geom MBR's lowermost side as a double precision number.
NULL will be returned if geom isn't a valid Geometry.
MbrMaxX MbrMaxX( geom Geometry) : Double precision
ST_MaxX( geom Geometry) : Double precision		 base return the x-coordinate for geom MBR's rightmost side as a double precision number.
NULL will be returned if geom isn't a valid Geometry.
MbrMaxY MbrMaxY( geom Geometry) : Double precision
ST_MaxY( geom Geometry) : Double precision		 base return the y-coordinate for geom MBR's uppermost side as a double precision number.
NULL will be returned if geom isn't a valid Geometry.
MinZ ST_MinZ( geom Geometry) : Double precision
ST_MinZ( geom Geometry , nodata-value Double ) : Double precision		 base return the minimum Z-coordinate value for geom as a double precision number.
  • if the optional argument nodata-value is set, then any NODATA value eventually found will be ignored.
NULL will be returned if geom isn't a valid Geometry or if geom has no Z dimension.
MaxZ ST_MaxZ( geom Geometry) : Double precision
ST_MaxZ( geom Geometry , nodata-value Double ) : Double precision		 base return the maximum Z-coordinate value for geom as a double precision number.
  • if the optional argument nodata-value is set, then any NODATA value eventually found will be ignored.
NULL will be returned if geom isn't a valid Geometry or if geom has no Z dimension.
MinM ST_MinM( geom Geometry) : Double precision
ST_MinM( geom Geometry , nodata-value Double ): Double precision		 base return the minimum M-coordinate value for geom as a double precision number.
  • if the optional argument nodata-value is set, then any NODATA value eventually found will be ignored.
NULL will be returned if geom isn't a valid Geometry or if geom has no M dimension.
MaxM ST_MaxM( geom Geometry) : Double precision
ST_MaxM( geom Geometry , nodata-value Double ) : Double precision		 base return the maximum M-coordinate value for geom as a double precision number.
  • if the optional argument nodata-value is set, then any NODATA value eventually found will be ignored.
NULL will be returned if geom isn't a valid Geometry or if geom has no M dimension.

SQL functions for constructing a geometric object given its Well-known Text Representation

Function Syntax OGC
defined		 required
module		 Summary
GeomFromText GeomFromText( wkt String [ , SRID Integer] ) : Geometry
ST_GeomFromText( wkt String [ , SRID Integer] ) : Geometry		 X base construct a geometric object given its Well-known text Representation
ST_WKTToSQL ST_WKTToSQL( wkt String ) : Geometry base SQL/MM compliant: simply an alias name for ST_GeomFromText
Please note: SRID=0 is always assumed.
PointFromText PointFromText( wktPoint String [ , SRID Integer] ) : Point
ST_PointFromText( wktPoint String [ , SRID Integer] ) : Point		 X base construct a Point
LineFromText
LineStringFromText		 LineFromText( wktLineString String [ , SRID Integer] ) : Linestring
ST_LineFromText( wktLineString String [ , SRID Integer] ) : Linestring
LineStringFromText( wktLineString String [ , SRID Integer] ) : Linestring
ST_LineStringFromText( wktLineString String [ , SRID Integer] ) : Linestring		 X base construct a Linestring
PolyFromText
PolygonFromText		 PolyFromText( wktPolygon String [ , SRID Integer] ) : Polygon
ST_PolyFromText( wktPolygon String [ , SRID Integer] ) : Polygon
PolygonFromText( wktPolygon String [ , SRID Integer] ) : Polygon
ST_PolygonFromText( wktPolygon String [ , SRID Integer] ) : Polygon		 X base construct a Polygon
MPointFromText
MultiPointFromText		 MPointFromText( wktMultiPoint String [ , SRID Integer] ) : MultiPoint
ST_MPointFromText( wktMultiPoint String [ , SRID Integer] ) : MultiPoint
MultiPointFromText( wktMultiPoint String [ , SRID Integer] ) : MultiPoint
ST_MultiPointFromText( wktMultiPoint String [ , SRID Integer] ) : MultiPoint		 X base construct a MultiPoint
MLineFromText
MultiLineStringFromText		 MLineFromText( wktMultiLineString String [ , SRID Integer] ) : MultiLinestring
ST_MLineFromText( wktMultiLineString String [ , SRID Integer] ) : MultiLinestring
MultiLineStringFromText( wktMultiLineString String [ , SRID Integer] ) : MultiLinestring
ST_MultiLineStringFromText( wktMultiLineString String [ , SRID Integer] ) : MultiLinestring		 X base construct a MultiLinestring
MPolyFromText
MultiPolygonFromText		 MPolyFromText( wktMultiPolygon String [ , SRID Integer] ) : MultiPolygon
ST_MPolyFromText( wktMultiPolygon String [ , SRID Integer] ) : MultiPolygon
MultiPolygonFromText( wktMultiPolygon String [ , SRID Integer] ) : MultiPolygon
ST_MultiPolygonFromText( wktMultiPolygon String [ , SRID Integer] ) : MultiPolygon		 X base construct a MultiPolygon
GeomCollFromText
GeometryCollectionFromText		 GeomCollFromText( wktGeometryCollection String [ , SRID Integer] ) : GeometryCollection
ST_GeomCollFromText( wktGeometryCollection String [ , SRID Integer] ) : GeometryCollection
GeometryCollectionFromText( wktGeometryCollection String [ , SRID Integer] ) : GeometryCollection
ST_GeometryCollectionFromText( wktGeometryCollection String [ , SRID Integer] ) : GeometryCollection		 X base construct a GeometryCollection
BdPolyFromText BdPolyFromText( wktMultilinestring String [ , SRID Integer] ) : Polygon
ST_BdPolyFromText( wktMultilinestring String [ , SRID Integer] ) : Polygon		 X GEOS Construct a Polygon given an arbitrary collection of closed linestrings as a MultiLineString text representation.
see also: BuildArea(), Polygonize()
BdMPolyFromText BdMPolyFromText( wktMultilinestring String [ , SRID Integer] ) : MultiPolygon
ST_BdMPolyFromText( wktMultilinestring String [ , SRID Integer] ) : MultiPolygon		 X GEOS Construct a MultiPolygon given an arbitrary collection of closed linestrings as a MultiLineString text representation.
see also: BuildArea(), Polygonize()

SQL functions for constructing a geometric object given its Well-known Binary Representation

Function Syntax OGC
defined		 required
module		 Summary
GeomFromWKB GeomFromWKB( wkbGeometry Binary [ , SRID Integer] ) : Geometry
ST_GeomFromWKB( wkbGeometry Binary [ , SRID Integer] ) : Geometry		 X base construct a geometric object given its Well-known binary Representation
ST_WKBToSQL ST_WKBToSQL( wkbGeometry Binary ) : Geometry base SQL/MM compliant: simply an alias name for ST_GeomFromWKB
Please note: SRID=0 is always assumed.
PointFromWKB PointFromWKB( wkbPoint Binary [ , SRID Integer] ) : Point
ST_PointFromWKB( wkbPoint Binary [ , SRID Integer] ) : Point		 X base construct a Point
LineFromWKB
LineStringFromWKB		 LineFromWKB( wkbLineString Binary [ , SRID Integer] ) : Linestring
ST_LineFromWKB( wkbLineString Binary [ , SRID Integer] ) : Linestring
LineStringFromText( wkbLineString Binary [ , SRID Integer] ) : Linestring
ST_LineStringFromText( wkbLineString Binary [ , SRID Integer] ) : Linestring		 X base construct a Linestring
PolyFromWKB
PolygonFromWKB		 PolyFromWKB( wkbPolygon Binary [ , SRID Integer] ) : Polygon
ST_PolyFromWKB( wkbPolygon Binary [ , SRID Integer] ) : Polygon
PolygonFromWKB( wkbPolygon Binary [ , SRID Integer] ) : Polygon
ST_PolygonFromWKB( wkbPolygon Binary [ , SRID Integer] ) : Polygon		 X base construct a Polygon
MPointFromWKB
MultiPointFromWKB		 MPointFromWKB( wkbMultiPoint Binary [ , SRID Integer] ) : MultiPoint
ST_MPointFromWKB( wkbMultiPoint Binary [ , SRID Integer] ) : MultiPoint
MultiPointFromWKB( wkbMultiPoint Binary [ , SRID Integer] ) : MultiPoint ST_MultiPointFromWKB( wkbMultiPoint Binary [ , SRID Integer] ) : MultiPoint		 X base construct a MultiPoint
MLineFromWKB
MultiLineStringFromWKB		 MLineFromWKB( wkbMultiLineString Binary [ , SRID Integer] ) : MultiLinestring
ST_MLineFromWKB( wkbMultiLineString Binary [ , SRID Integer] ) : MultiLinestring
MultiLineStringFromWKB( wkbMultiLineString Binary [ , SRID Integer] ) : MultiLinestring
ST_MultiLineStringFromWKB( wkbMultiLineString Binary [ , SRID Integer] ) : MultiLinestring		 X base construct a MultiLinestring
MPolyFromWKB
MultiPolygonFromWKB		 MPolyFromWKB( wkbMultiPolygon Binary [ , SRID Integer] ) : MultiPolygon
ST_MPolyFromWKB( wkbMultiPolygon Binary [ , SRID Integer] ) : MultiPolygon
MultiPolygonFromWKB( wkbMultiPolygon Binary [ , SRID Integer] ) : MultiPolygon
ST_MultiPolygonFromWKB( wkbMultiPolygon Binary [ , SRID Integer] ) : MultiPolygon		 X base construct a MultiPolygon
GeomCollFromWKB
GeometryCollectionFromWKB		 GeomCollFromWKB( wkbGeometryCollection Binary [ , SRID Integer] ) : GeometryCollection
ST_GeomCollFromWKB( wkbGeometryCollection Binary [ , SRID Integer] ) : GeometryCollection
GeometryCollectionFromWKB( wkbGeometryCollection Binary [ , SRID Integer] ) : GeometryCollection
ST_GeometryCollectionFromWKB( wkbGeometryCollection Binary [ , SRID Integer] ) : GeometryCollection		 X base construct a GeometryCollection
BdPolyFromWKB BdPolyFromWKB( wkbMultilinestring Binary [ , SRID Integer] ) : Polygon
ST_BdPolyFromWKB( wkbMultilinestring Binary [ , SRID Integer] ) : Polygon		 X GEOS Construct a Polygon given an arbitrary collection of closed linestrings as a MultiLineString binary representation.
see also: BuildArea(), Polygonize()
BdMPolyFromWKB BdMPolyFromWKB( wkbMultilinestring Binary [ , SRID Integer] ) : MultiPolygon
ST_BdMPolyFromWKB( wkbMultilinestring Binary [ , SRID Integer] ) : MultiPolygon		 X GEOS Construct a MultiPolygon given an arbitrary collection of closed linestrings as a MultiLineString binary representation.
see also: BuildArea(), Polygonize()

SQL functions for obtaining the Well-known Text / Well-known Binary Representation of a geometric object

Function Syntax OGC
defined		 required
module		 Summary
AsText AsText( geom Geometry ) : String
ST_AsText( geom Geometry ) : String		 X base returns the Well-known Text representation
AsWKT AsWKT( geom Geometry [ , precision Integer ] ) : String base returns the Well-known Text representation
always return strictly conformant 2D WKT
AsBinary AsBinary( geom Geometry ) : Binary
ST_AsBinary( geom Geometry ) : Binary		 X base returns the Well-known Binary representation

SQL functions supporting exotic geometric formats

Function Syntax OGC
defined		 required
module		 Summary
AsSVG AsSVG( geom Geometry [ , relative Integer [ , precision Integer ] ] ) : String base returns the SVG [Scalable Vector Graphics] representation
AsKml AsKml( geom Geometry [ , precision Integer ] ) : String
AsKml( name String, description String, geom Geometry [ , precision Integer ] ) : String		 PROJ returns the KML [Keyhole Markup Language] representation
The first form will simply generate the geometry element: the second form will generate a complete KML entity
GeomFromKml GeomFromKml( KmlGeometry String ) : Geometry base construct a geometric object given its KML Representation
AsGml AsGml( geom Geometry [ , precision Integer ] ) : String
AsGml( version Integer, geom Geometry [ , precision Integer ] ) : String		 base returns the GML [Geography Markup Language] representation
If version = 3 than GML 3.x is generated, otherwise the output format will be GML 2.x
GeomFromGML GeomFromGML( gmlGeometry String ) : Geometry base construct a geometric object given its GML Representation
AsGeoJSON AsGeoJSON( geom Geometry [ , precision Integer [ , options Integer ] ] ) : String base returns the GeoJSON [Geographic JavaScript Object Notation] representation
if not explicitly specified precision is 15 decimal digits (default value).

options can assume the following values:
  • 0 no options [default]
  • 1 GeoJSON BoundingBox
  • 2 GeoJSON CRS [short version]
  • 3 BoundingBox + short CRS
  • 4 GeoJSON CRS [long version]
  • 5 BoundingBox + long CRS
GeomFromGeoJSON GeomFromGeoJSON( geoJSONGeometry String ) : Geometry base construct a geometric object given its GeoJSON Representation
AsEWKB AsEWKB( geom Geometry ) : String base returns the EWKB [Extended Well Known Binary] representation (PostGIS compatibility)
GeomFromEWKB GeomFromEWKB( ewkbGeometry String ) : Geometry base construct a geometric object given its EWKB Representation
AsEWKT AsEWKT( geom Geometry ) : String base returns the EWKT [Extended Well Known Text] representation (PostGIS compatibility)
GeomFromEWKT GeomFromEWKT( ewktGeometry String ) : Geometry base construct a geometric object given its EWKT Representation
AsFGF AsFGF( geom Geometry , dims Integer ) : Binary base returns the FGF [FDO Geometry Binary Format] representation
dims can assume one of the following values:
  • 0 XY dimension
  • 1 XYZ dimension
  • 2 XYM dimension
  • 3 XYZM dimension
GeomFromFGF GeomFromFGF( fgfGeometry Binary [ , SRID Integer] ) : Geometry base construct a geometric object given its FGF binary Representation
AsTWKB AsTWKB( geom Geometry ) : TWKB-blob
AsTWKB( geom Geometry , precision_xy Integer) : TWKB-blob
AsTWKB( geom Geometry , precision_xy Integer , precision_z Integer ) : TWKB-blob
AsTWKB( geom Geometry , precision_xy Integer , precision_z Integer , precision_m Integer ) : TWKB-blob
AsTWKB( geom Geometry , precision_xy Integer , precision_z Integer , precision_m Integer , with_size Integer ) : TWKB-blob
AsTWKB( geom Geometry , precision_xy Integer , precision_z Integer , precision_m Integer , with_size Integer , with_bbox Integer ) : TWKB-blob		 RTTOPO returns the TWKB [Tiny Well Known Binary] representation (PostGIS/Mapnik compatibility)
  • the optional arguments precision_xy, precision_z and precision_m are intended to specify how many decimal digits should be preserved for the corresponding coordinate (less decimal digits = stronger compression). If not specified the default value is alwasy ZERO (no decimal digits at all).
  • the optional arguments with_size and with_bbox are of the Boolean type (TRUE or FALSE) and respectively control if the TWKB geometry should include or not a size and/or a BBOX. If not specified the default value is always FALSE.
GeomFromTWKB GeomFromTWKB( twkbGeometry BLOB [ , SRID Integer] ) : Geometry RTTOPO construct a geometric object given its TWKB Representation
AsEncodedPolyline ST_AsEncodedPolyline( geom Geometry ) : TEXT
ST_AsEncodedPolyline( geom Geometry , precision Integer ) : TEXT		 RTTOPO returns a GoogleMaps encoded Polyline from a Geometry.
The optional arguments precision is intended to specify how many decimal digits should be preserved (default value: 5).
Note: the input Geometry must be a LINESTRING in some geographic SRID (coordinates expressed as longitude and latitude angles).
NULL will be returned on invalid arguments.
LineFromEncodedPolyline ST_LineFromEncodedPolyline( polyline TEXT ) : Geometry
ST_LineFromEncodedPolyline( polyline TEXT , precision Integer ) : Geometry		 RTTOPO returns a Geometry from a GoogleMaps encoded Polyline.
The optional arguments precision is intended to specify how many decimal digist should be preserved (default value: 5).
Note: the returned Geometry will always be a LINESTRING in the SRID=4326.
NULL will be returned on invalid arguments.

SQL functions on type Geometry

Function Syntax OGC
defined		 required
module		 Summary
Dimension Dimension( geom Geometry ) : Integer
ST_Dimension( geom Geometry ) : Integer		 X base returns the dimension of the geometric object, which is less than or equal to the dimension of the coordinate space
CoordDimension CoordDimension( geom Geometry ) : String base returns the dimension model used by the geometric object as:
'XY', 'XYZ', 'XYM' or 'XYZM'
NDims ST_NDims( geom Geometry ) : Integer base returns the dimension number used by the geometric object as:
2, 3 or 4 respectively for XY, XYZ and XYZM (3 for XYM)
Is3D ST_Is3D( geom Geometry ) : Integer base Checks if geom has the Z dimension.
The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and -1 for UNKNOWN when called with invalid arguments.
IsMeasured ST_IsMeasured( geom Geometry ) : Integer base Check if geom has the M dimension.
The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and -1 for UNKNOWN when called with invalid arguments.
GeometryType GeometryType( geom Geometry ) : String
ST_GeometryType( geom Geometry ) : String		 X base returns the name of the instantiable subtype of Geometry of which this geometric object is a member, as a string
SRID SRID( geom Geometry ) : Integer
ST_SRID( geom Geometry ) : Integer		 X base returns the Spatial Reference System ID for this geometric object
SetSRID SetSRID( geom Geometry , SRID Integer ) : Geometry base directly sets the Spatial Reference System ID for this geometric object [no reprojection is applied]
Will return a new Geometry BLOB object, or NULL on invalid arguments or errors.
IsEmpty IsEmpty( geom Geometry ) : Integer
ST_IsEmpty( geom Geometry ) : Integer		 X base The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL argument.
TRUE if this geometric object corresponds to the empty set
IsSimple IsSimple( geom Geometry ) : Integer
ST_IsSimple( geom Geometry ) : Integer		 X GEOS The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL argument.
TRUE if this geometric object is simple, as defined in the Geometry Model
IsValid IsValid( geom Geometry [ , esri_flag Boolean ] ) : Integer
ST_IsValid( geom Geometry [ , esri_flag Boolean ] ) : Integer		 GEOS The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL argument.
If the ESRI_flag argument is set to 1 (TRUE), then all ESRI-like internal holes (violating the standard OGC model) will be considered valid.
TRUE if this geometric object does not contains any topological error
IsValidReason IsValidReason( geom Geometry [ , esri_flag Boolean ] ) : String
ST_IsValidReason( geom Geometry [ , esri_flag Boolean ] ) : String		 GEOS Will return a TEXT string stating if a Geometry is valid and if not valid, a reason why.
If the ESRI_flag argument is set to 1 (TRUE), then all ESRI-like internal holes (violating the standard OGC model) will be considered valid.
NULL will be returned on invalid arguments.
IsValidDetail IsValidDetail( geom Geometry [ , esri_flag Boolean ]) : Geometry
ST_IsValidDetail( geom Geometry [ , esri_flag Boolean ]) : Geometry		 GEOS Will return a Geometry detail (usually a POINT) causing invalidity.
If the ESRI_flag argument is set to 1 (TRUE), then all ESRI-like internal holes (violating the standard OGC model) will be considered valid.
NULL will be returned on invalid arguments, or in the case of a valid Geometry.
Boundary Boundary( geom Geometry ) : Geometry
ST_Boundary( geom Geometry ) : Geometry		 X GEOS returns a geometric object that is the combinatorial boundary of g as defined in the Geometry Model
Envelope Envelope( geom Geometry ) : Geometry
ST_Envelope( geom Geometry ) : Geometry		 X base returns the rectangle bounding g as a Polygon. The Polygon is defined by the corner points of the bounding box [(MINX, MINY),(MAXX, MINY), (MAXX, MAXY), (MINX, MAXY), (MINX, MINY)].
Expand ST_Expand( geom Geometry , amount Double precision ) : Geometry base returns the rectangle bounding g as a Polygon. The bounding rectangle is expanded in all directions by an amount specified by the second argument.
NPoints ST_NPoints( geom Geometry ) : Integer base return the total number of Points (this including any Linestring/Polygon vertex).
NRings ST_NRings( geom Geometry ) : Integer base return the total number of Rings (this including both Exterior and Interior Rings).
Reverse ST_Reverse( geom Geometry ) : Geometry base returns a new Geometry [if a valid Geometry was supplied], or NULL in any other case.
Any Linestring or Ring will be in reverse order (first vertex will be the last one, and last vertex will be the first one).
ForceLHR ST_ForceLHR( geom Geometry ) : Geometry base Just an alias-name for ST_ForcePolygonCW().
Note: this function in Spatialite has a different interpretation then in PostGIS.
ForcePolygonCW ST_ForcePolygonCW( geom Geometry ) : Geometry base returns a new Geometry [if a valid Geometry was supplied], or NULL in any other case.
All Polygons will be oriented accordingly to Clockwise Rule (all Exterior Ring will be clockwise oriented, and all Interior Rings will be counter-clockwise oriented).
ForcePolygonCCW ST_ForcePolygonCCW( geom Geometry ) : Geometry base returns a new Geometry [if a valid Geometry was supplied], or NULL in any other case.
All Polygons will be oriented accordingly to Counter-Clockwise Rule (all Exterior Ring will be counter-clockwise oriented, and all Interior Rings will be clockwise oriented).
IsPolygonCW ST_IsPolygonCW( geom Geometry ) : Boolean base returns TRUE (1) if all Polygons into the Geometry are oriented accordingly to Clockwise Rule (all Exterior Ring must be clockwise oriented, and all Interior Rings must be counter-clockwise oriented).
returns FALSE (0) if not; returns -1 on invalid Geometries.
Note: a Geometry containing no Polygons will always return TRUE.
IsPolygonCCW ST_IsPolygonCCW( geom Geometry ) : Boolean base returns TRUE (1) if all Polygons into the Geometry are oriented accordingly to Counter-Clockwise Rule (all Exterior Ring must be counter-clockwise oriented, and all Interior Rings must be clockwise oriented).
returns FALSE (0) if not: returns -1 on invalid Geometries.
Note: a Geometry containing no Polygons will always return TRUE.

SQL functions attempting to repair malformed Geometries

Function Syntax OGC
defined		 required
module		 Summary
SanitizeGeometry SanitizeGeometry( geom Geometry ) : geom Geometry base returns a (possibly) sanitized Geometry [if a valid Geometry was supplied], or NULL in any other case
Please note: current implementations only affects:
  • repeated vertices suppression
  • Ring's closure enforcement
EnsureClosedRings EnsureClosedRings( geom Geometry ) : geom Geometry base returns a new Geometry derived from the input Geometry; all Rings within the output Geometry are ensured to be correctly closed, i.e. will have exactly coincident start and end vertices.
This function accepts input Geometries of any class: Point, MultiPoint, Linestring, MultiLinestring, Polygon, MultiPolygon, GeometryCollection and Geometry.
Will return NULL on invalid argument.
RemoveRepeatedPoints RemoveRepeatedPoints( geom Geometry ) : geom Geometry
RemoveRepeatedPoints( geom Geometry , tolerance Double ) : geom Geometry		 base returns a new Geometry derived from the input Geometry; all repeated vertices found in Linestrings or Rings will be removed and the same applies to repeated points found in Multipoints.
This function accepts input Geometries of any class: Point, MultiPoint, Linestring, MultiLinestring, Polygon, MultiPolygon, GeometryCollection and Geometry.
  • if the optional argument tolerance is defined all vertices/points presenting a distance less or equal to this value will be considered as repeated points.
    The default tolerance value is 0.0, this intending an exact coincidence; the same applies to any negative tolerance.
  • Linestring or Ring vertices are considered to be repeated only if they are consecutive.
  • Multipoint points are considered to be repeated independentely from their relative order.
Will return NULL on invalid arguments.

SQL Geometry-compression functions

Function Syntax OGC
defined		 required
module		 Summary
CompressGeometry CompressGeometry( geom Geometry ) : geom Geometry base returns a compressed Geometry [if a valid Geometry was supplied], or NULL in any other case
Please note: geometry compression only affects LINESTRINGs and POLYGONs, not POINTs
UncompressGeometry UncompressGeometry( geom Geometry ) : geom Geometry base returns an uncompressed Geometry [if a valid Geometry was supplied], or NULL in any other case

SQL Geometry-type casting functions

Function Syntax OGC
defined		 required
module		 Summary
CastToPoint CastToPoint( geom Geometry ) : geom Geometry base returns a POINT-type Geometry [if type-conversion is possible], or NULL in any other case
can be applied to any Geometry containing only a single POINT and no other elementary sub-geometry
CastToLinestring CastToLinestring( geom Geometry ) : geom Geometry base returns a LINESTRING-type Geometry [if type-conversion is possible], or NULL in any other case
can be applied to any Geometry containing only a single LINESTRING and no other elementary sub-geometry
CastToPolygon CastToPolygon( geom Geometry ) : geom Geometry base returns a POLYGON-type Geometry [if type-conversion is possible], or NULL in any other case
can be applied to any Geometry containing only a single POLYGON and no other elementary sub-geometry
CastToMultiPoint CastToMultiPoint( geom Geometry ) : geom Geometry base returns a MULTIPOINT-type Geometry [if type-conversion is possible], or NULL in any other case
can be applied to any Geometry containing one or more POINT(s) and no other elementary sub-geometry
CastToMultiLinestring CastToMultiLinestring( geom Geometry ) : geom Geometry base returns a MULTILINESTRING-type Geometry [if type-conversion is possible], or NULL in any other case
can be applied to any Geometry containing one or more LINESTRING(s) and no other elementary sub-geometry
CastToMultiPolygon CastToMultiPolygon( geom Geometry ) : geom Geometry base returns a MULTIPOLYGON-type Geometry [if type-conversion is possible], or NULL in any other case
can be applied to any Geometry containing one or more POLYGON(s) and no other elementary sub-geometry
CastToGeometyCollection CastToGeometryCollection( geom Geometry ) : geom Geometry base returns a GEOMETRYCOLLECTION-type Geometry [if type-conversion is possible], or NULL in any other case
can be applied to any valid Geometry
CastToMulti CastToMulti( geom Geometry ) : geom Geometry
ST_Multi( geom Geometry ) : geom Geometry		 base returns a MULTIPOINT-, MULTILINESTRING- or MULTIPOLYGON-type Geometry [if type-conversion is possible], or NULL in any other case
  • a MULTIPOINT will be returned for a Geometry containing one or more POINT(s) and no other elementary sub-geometry
  • a MULTILINESTRING will be returned for a Geometry containing one or more LINESTRING(s) and no other elementary sub-geometry
  • a MULTIPOLYGON will be returned for a Geometry containing one or more POLYGON(s) and no other elementary sub-geometry
  • a GEOMETRYCOLLECTION will be returned for any other valid Geometry
CastToSingle CastToSingle( geom Geometry ) : geom Geometry base returns a POINT-, LINESTRING- or POLYGON-type Geometry [if type-conversion is possible], or NULL in any other case
  • a POINT will be returned for a Geometry containing only a single POINT and no other elementary sub-geometry
  • a LINESTRING will be returned for a Geometry containing only a single LINESTRING and no other elementary sub-geometry
  • a POLYGON will be returned for a Geometry containing only a single POLYGON and no other elementary sub-geometry

SQL Space-dimensions casting functions

Function Syntax OGC
defined		 required
module		 Summary
CastToXY CastToXY( geom Geometry ) : geom Geometry base returns a Geometry using the [XY] space dimension
CastToXYZ CastToXYZ( geom Geometry ) : geom Geometry
CastToXYZ( geom Geometry , no_data Double ) : geom Geometry		 base returns a Geometry using the [XYZ] space dimension.
If the input Geometry already supports Z coordinates they'll be preserved as they are.
If the input Geometry does not support Z coordinates they'll be set to no_data (defaulting to 0.0 if no_data is not specified).
CastToXYM CastToXYM( geom Geometry ) : geom Geometry
CastToXYM( geom Geometry , no_data Double ) : geom Geometry		 base returns a Geometry using the [XYM] space dimension.
If the input Geometry already supports M coordinates they'll be preserved as they are.
If the input Geometry does not support M coordinates they'll be set to no_data (defaulting to 0.0 if no_data is not specified).
CastToXYZM CastToXYZM( geom Geometry ) : geom Geometry
CastToXYZM( geom Geometry , z_no_data Double , m_no_data Double ) : geom Geometry		 base returns a Geometry using the [XYZM] space dimension.
If the input Geometry already supports Z coordinates they'll be preserved as they are.
If the input Geometry does not support Z coordinates they'll be set to z_no_data (defaulting to 0.0 if z_no_data is not specified).
The same rule applies to M coordinates and m_no_data.

SQL functions on type Point

Function Syntax OGC
defined		 required
module		 Summary
X X( pt Point ) : Double precision
ST_X( pt Point ) : Double precision		 X base return the x-coordinate of Point p as a double precision number
Y Y( pt Point ) : Double precision
ST_Y( pt Point ) : Double precision		 X base return the y-coordinate of Point p as a double precision number
Z Z( pt Point ) : Double precision
ST_Z( pt Point ) : Double precision		 X base return the z-coordinate of Point p as a double precision number
or NULL is no z-coordinate is available
M M( pt Point ) : Double precision
ST_M( pt Point ) : Double precision		 X base return the m-coordinate of Point p as a double precision number
or NULL is no m-coordinate is available

SQL functions on type Curve [Linestring or Ring]

Function Syntax OGC
defined		 required
module		 Summary
StartPoint StartPoint( c Curve ) : Point
ST_StartPoint( c Curve ) : Point		 X base return a Point containing the first Point of c
EndPoint EndPoint( c Curve ) : Point
ST_EndPoint( c Curve ) : Point		 X base return a Point containing the last Point of c
Length GLength( c Curve ) : Double precision
OpenGis name for this function is Length(), but it conflicts with an SQLite reserved keyword
ST_Length( c Curve ) : Double precision 		X GEOS return the length of c
Starting since v.4.0.0 this function will simply consider Linestrings and MultiLinestrings, ignoring any Polygon or MultiPolygon
GLength( c Curve , use_ellipsoid Boolean ) : Double precision
ST_Length( c Curve , use_ellipsoid Boolean ) : Double precision 		X GEOS return the length of c (measured in meters).
If the use_ellipsoid argument is set to TRUE the precise (but slower) length will be computed on the Ellipsoid, otherwise will be computed on the Great Circle (approximative, but faster).
This function only supports Long/Lat coordinates, and will return NULL for any planar CRS
Starting since v.4.0.0 this function will simply consider Linestrings and MultiLinestrings, ignoring any Polygon or MultiPolygon
Perimeter Perimeter( s Surface ) : Double precision ST_Perimeter( s Surface ) : Double precision X GEOS return the perimeter of s
Starting since v.4.0.0 this function will simply consider Polygons and MultiPolygons, ignoring any Linestring or MultiLinestring
Perimeter( s Surface , use_ellipsoid Boolean ) : Double precision
ST_Perimeter( s Surface , use_ellipsoid Boolean ) : Double precision 		X GEOS return the perimeter of s (measured in meters).
If the use_ellipsoid argument is set to TRUE the precise (but slower) perimeter will be computed on the Ellipsoid, otherwise will be computed on the Great Circle (approximative, but faster).
This function only supports Long/Lat coordinates, and will return NULL for any planar CRS
Starting since v.4.0.0 this function will simply consider Polygons and MultiPolygons, ignoring any Linestring or MultiLinestring
Geodesic Length GeodesicLength( c Curve ) : Double precision base If [and only if] the SRID associated with c is a geographic one [i.e. one using longitude and latitude angles], then returns the length of c measured on the Ellipsoid [such length is always expressed in meters]
Otherwise NULL will be returned
Please note: measuring lengths on the Ellipsoid requires complex geodesic calculations, and thus is an intrinsically slow and time consuming task
Great Circle Length GreatCircleLength( c Curve ) : Double precision base If [and only if] the SRID associated with c is a geographic one [i.e. one using longitude and latitude angles], then returns the length of c measured on the Great Circle [such length is always expressed in meters]
Otherwise NULL will be returned
Please note: lengths measured on the Great Circle are less precise than lengths measured on the Ellipsoid using complex geodesic calculations; but they are by far quickest to compute
IsClosed IsClosed( c Curve ) : Integer
ST_IsClosed( c Curve ) : Integer		 X GEOS The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL argument;
return TRUE if c is closed, i.e., if StartPoint(c) = EndPoint(c)
IsRing IsRing( c Curve ) : Integer
ST_IsRing( c Curve ) : Integer		 X GEOS The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL argument.
return TRUE if c is a ring, i.e., if c is closed and simple. A simple Curve does not pass through the same Point more than once.
PointOnSurface PointOnSurface( s Surface/Curve ) : Point
ST_PointOnSurface( s Surface/Curve ) : Point		 X GEOS return a Point guaranteed to lie on the Surface (or Curve)
Simplify Simplify( c Curve , tolerance Double precision ) : Curve
ST_Simplify( c Curve , tolerance Double precision ) : Curve
ST_Generalize( c Curve , tolerance Double precision ) : Curve		 GEOS return a geometric object representing a simplified version of c applying the Douglas-Peuker algorithm with given tolerance
SimplifyPreserveTopology SimplifyPreserveTopology( c Curve , tolerance Double precision ) : Curve
ST_SimplifyPreserveTopology( c Curve , tolerance Double precision ) : Curve		 GEOS return a geometric object representing a simplified version of c applying the Douglas-Peuker algorithm with given tolerance and respecting topology

SQL functions on type LineString

Function Syntax OGC
defined		 required
module		 Summary
NumPoints NumPoints( line LineString ) : Integer
ST_NumPoints( line LineString ) : Integer		 X base return the number of Points in the LineString
PointN PointN( line LineString , n Integer ) : Point
ST_PointN( line LineString , n Integer ) : Point		 X base return a Point containing Point n of line (first Point corresponds to n=1)
AddPoint AddPoint( line LineString , point Point [ , position Integer ] ) : Linestring
ST_AddPoint( line LineString , point Point [ , position Integer ] ) : Linestring		 base returns a new Linestring by adding a new Point into the input Linestring immediately before position (zero-based index).
A negative position (default) means appending the new Point to the end of the input Linestring.
NULL will be returned if any error is encountered.
SetPoint SetPoint( line LineString , position Integer , point Point ) : Linestring
ST_SetPoint( line LineString , position Integer , point Point ) : Linestring		 base returns a new Linestring by replacing the Point at position (zero-based index).
NULL will be returned if any error is encountered.
SetStartPoint SetStartPoint( line LineString , point Point ) : Linestring
ST_SetStartPoint( line LineString , point Point ) : Linestring		 base returns a new Linestring by replacing its StartPoint.
NULL will be returned if any error is encountered.
SetEndPoint SetEndPoint( line LineString , point Point ) : Linestring
ST_SetEndPoint( line LineString , point Point ) : Linestring		 base returns a new Linestring by replacing its EndPoint.
NULL will be returned if any error is encountered.
RemovePoint RemovePoint( line LineString , position Integer ) : Linestring
ST_RemovePoint( line LineString , position Integer ) : Linestring		 base returns a new Linestring by removing the Point at position (zero-based index).
NULL will be returned if any error is encountered.
LinestringMinSegmentLength LinestringMinSegmentLength( line LineString ) : Double precision
LinestringMinSegmentLength( line LineString , boolean ignore_repeated_vertices ) : Double precision
ST_LinestringMinSegmentLength( line LineString ) : Double precision
ST_LinestringMinSegmentLength( line LineString , boolean ignore_repeated_vertices ) : Double precision		 base
  • any eventual repeated vertex will be ignored or considered accordingly to the seeting of the optional argument ignore_repeated_vertices
    The default setting is TRUE (that is, ignore)
returns the length of the shortest segment in the Linestring.
NULL will be returned if any error is encountered.
Note: this function only accepts simple Linestrings; Geometries containing any Point or Polygon, or containing more than a single Linesting will be considered invalid.
LinestringMaxSegmentLength LinestringMaxSegmentLength( line LineString ) : Double precision
ST_LinestringMaxSegmentLength( line LineString ) : Double precision		 base returns the length of the longest segment in the Linestring.
NULL will be returned if any error is encountered.
Note: this function only accepts simple Linestrings; Geometries containing any Point or Polygon, or containing more than a single Linesting will be considered invalid.
LinestringAvgSegmentLength LinestringAvgSegmentLength( line LineString ) : Double precision
ST_LinestringAvgSegmentLength( line LineString ) : Double precision		 base returns the average length of segments in the Linestring.
NULL will be returned if any error is encountered.
Note: this function only accepts simple Linestrings; Geometries containing any Point or Polygon, or containing more than a single Linesting will be considered invalid.
CurvosityIndex CurvosityIndex( line LineString ) : Double precision
CurvosityIndex( line LineString , extra-points Integer ) : Double precision
ST_CurvosityIndex( line LineString ) : Double precision
ST_CurvosityIndex( line LineString , extra-points Integer ) : Double precision		 base returns the Curvosity Index of a generic simple Linestring:
  • the Index will range between 1.0 (in the case of a perfectly straight line) and 0.0 (in the case of a closed line with coincident start and end points).
  • if the optional argument extra-points is explicitly set, then the given number of equidistant points will be interpolated into the reference line used to calculate the Index.
NULL will be returned if any error is encountered.
Note: this function only accepts simple Linestrings; Geometries containing any Point or Polygon, or containing more than a single Linesting will be considered invalid.
UphillHeight UphillHeight( line LineString ) : Double precision
ST_UphillHeight( line LineString ) : Double precision		 base returns the total Uphill Height of a generic simple Linestring:
  • 0.0 will be always returned for any 2D Linestring not containing Z coordinates.
NULL will be returned if any error is encountered.
Note: this function only accepts simple Linestrings; Geometries containing any Point or Polygon, or containing more than a single Linesting will be considered invalid.
DownhillHeight DownhillHeight( line LineString ) : Double precision
ST_DownhillHeight( line LineString ) : Double precision		 base returns the total Downhill Height of a generic simple Linestring:
  • 0.0 will be always returned for any 2D Linestring not containing Z coordinates.
NULL will be returned if any error is encountered.
Note: this function only accepts simple Linestrings; Geometries containing any Point or Polygon, or containing more than a single Linesting will be considered invalid.
UpDownHeight UpDownHeight( line LineString ) : Double precision
ST_UpDownHeight( line LineString ) : Double precision		 base returns the sum of total UpHill and DownHill Heights of a generic simple Linestring:
  • this is just a convenience method; calling ST_UpDownHeight(line) is exactly the same than calling ST_UphillHeight(geom) + ST_DownhillHeight(geom)
  • 0.0 will be always returned for any 2D Linestring not containing Z coordinates.
NULL will be returned if any error is encountered.
Note: this function only accepts simple Linestrings; Geometries containing any Point or Polygon, or containing more than a single Linesting will be considered invalid.

SQL functions on type Surface [Polygon or Ring]

Function Syntax OGC
defined		 required
module		 Summary
Centroid Centroid( s Surface ) : Point
ST_Centroid( s Surface ) : Point		 X GEOS return the centroid of s, which may lie outside s
Area Area( s Surface ) : Double precision
ST_Area( s Surface ) : Double precision		 X GEOS return the area of s
Area( s Surface , use_ellipsoid Boolean ) : Double precision
ST_Area( s Surface , use_ellipsoid Boolean ) : Double precision 		X RTTOPO return the area of s (measured in meters).
If the use_ellipsoid argument is set to TRUE the precise (but slower) area will be computed on the Ellipsoid, otherwise will be computed on the Sphere (approximative, but faster).
This function only supports Long/Lat coordinates, and will return NULL for any planar CRS
Circularity Circularity( s Surface ) : Double precision X GEOS computes the Circularity Index from the given Geometry by applying the following formula:
  • index = ( 4 * PI * Sum(area) ) / ( Sum(perimeter) * Sum(perimeter) )
  • it only applies to Polygons or MultiPolygons with the following interpretation:
    • 1.0 corresponds to a perfectly circular shape.
    • very low values (near zero) correspond to a threadlike shape.
    • intermediate values correspond to a more or less flattened shape; lower index values means a stronger flattening effect.
  • if the given Geometry does not contains any Polygon but contains at least a Linestring the index will always assume a 0.0 value.
  • if the given Geometry only contains one or more Points the index will always assume a NULL value.
Circularity( s Surface , use_ellipsoid Boolean ) : Double precision X RTTOPO same as the above Function, but in this case areas and perimeters will be measured in meters.
If the use_ellipsoid argument is set to TRUE the precise (but slower) values will be computed on the Ellipsoid, otherwise they'll be computed on the Sphere (approximative, but faster).
This function only supports Long/Lat coordinates, and will return NULL for any planar CRS

SQL functions on type Polygon

Function Syntax OGC
defined		 required module Summary
ExteriorRing ExteriorRing( polyg Polygon ) : LineString
ST_ExteriorRing( polyg Polygon ) : LineString		 X base return the exteriorRing of p
NumInteriorRing
NumInteriorRings		 NumInteriorRing( polyg Polygon ) : Integer
NumInteriorRings( polyg Polygon ) : Integer
ST_NumInteriorRing( polyg Polygon ) : Integer		 X base return the number of interiorRings
InteriorRingN InteriorRingN( polyg Polygon , n Integer ) : LineString
ST_InteriorRingN( polyg Polygon , n Integer ) : LineString		 X base return the nth (1-based) interiorRing. The order of Rings is not geometrically significant.

SQL functions on type GeomCollection

Function Syntax OGC
defined		 required
module		 Summary
NumGeometries NumGeometries( geom GeomCollection ) : Integer
ST_NumGeometries( geom GeomCollection ) : Integer		 X base return the number of individual Geometries
GeometryN GeometryN( geom GeomCollection , n Integer ) : Geometry
ST_GeometryN( geom GeomCollection , n Integer ) : Geometry		 X base return the nth (1-based) geometric object in the collection. The order of the elements in the collection is not geometrically significant.

SQL functions that test approximate spatial relationships via MBRs

Function Syntax OGC
defined		 required
module		 Summary
MbrEqual MbrEqual( geom1 Geometry , geom2 Geometry ) : Integer base The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with NULL or invalid arguments.
TRUE if g1 and g2 have equal MBRs
MbrDisjoint MbrDisjoint( geom1 Geometry , geom2 Geometry ) : Integer base The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with NULL or invalid arguments.
TRUE if the intersection of g1 and g2 MBRs is the empty set
MbrTouches MbrTouches( geom1 Geometry , geom2 Geometry ) : Integer base The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with NULL or invalid arguments.
TRUE if the only Points in common between g1 and g2 MBRs lie in the union of the boundaries of g1 and g2
MbrWithin MbrWithin( geom1 Geometry , geom2 Geometry ) : Integer base The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with NULL or invalid arguments.
TRUE if g1 MBR is completely contained in g2 MBR
MbrOverlaps MbrOverlaps( geom1 Geometry , geom2 Geometry ) : Integer base The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with NULL or invalid arguments.
TRUE if the intersection of g1 and g2 MBRs results in a value of the same dimension as g1 and g2 that is different from both g1 and g2
MbrIntersects MbrIntersects( geom1 Geometry , geom2 Geometry ) : Integer base The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with NULL or invalid arguments;
convenience predicate: TRUE if the intersection of g1 and g2 MBRs is not empty
EnvelopesIntersects ST_EnvIntersects( geom1 Geometry , geom2 Geometry ) : Integer
ST_EnvelopesIntersects( geom1 Geometry , geom2 Geometry ) : Integer
ST_EnvIntersects( geom1 Geometry , x1 Double precision , y1 Double precision , x2 Double precision , y2 Double precision ) : Integer
ST_EnvelopesIntersects( geom1 Geometry , x1 Double precision , y1 Double precision , x2 Double precision , y2 Double precision ) : Integer		 base The first form simply is an alias name for MbrIntersects; the other form allows to define the second MBR by two extreme points [x1, y1] and [x2, y2].
The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with NULL or invalid arguments;
convenience predicate: TRUE if the intersection of both MBRs is not empty
MbrContains MbrContains( geom1 Geometry , geom2 Geometry ) : Integer base The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with NULL arguments;
convenience predicate: TRUE if g2 MBR is completely contained in g1 MBR

SQL functions that test spatial relationships

Function Syntax OGC
defined		 required
module		 Summary
Equals Equals( geom1 Geometry , geom2 Geometry ) : Integer
ST_Equals( geom1 Geometry , geom2 Geometry ) : Integer		 X GEOS The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with NULL arguments.
TRUE if g1 and g2 are equal
Disjoint Disjoint( geom1 Geometry , geom2 Geometry ) : Integer
ST_Disjoint( geom1 Geometry , geom2 Geometry ) : Integer		 X GEOS The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with NULL arguments.
TRUE if the intersection of g1 and g2 is the empty set
Touches Touches( geom1 Geometry , geom2 Geometry ) : Integer
ST_Touches( geom1 Geometry , geom2 Geometry ) : Integer		 X GEOS The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with NULL arguments.
TRUE if the only Points in common between g1 and g2 lie in the union of the boundaries of g1 and g2
Within Within( geom1 Geometry , geom2 Geometry ) : Integer
ST_Within( geom1 Geometry , geom2 Geometry ) : Integer		 X GEOS The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with NULL arguments.
TRUE if g1 is completely contained in g2
Overlaps Overlaps( geom1 Geometry , geom2 Geometry ) : Integer
ST_Overlaps( geom1 Geometry , geom2 Geometry ) : Integer		 X GEOS The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with NULL arguments.
TRUE if the intersection of g1 and g2 results in a value of the same dimension as g1 and g2 that is different from both g1 and g2
Crosses Crosses( geom1 Geometry , geom2 Geometry ) : Integer
ST_Crosses( geom1 Geometry , geom2 Geometry ) : Integer		 X GEOS The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with NULL arguments.
TRUE if the intersection of g1 and g2 results in a value whose dimension is less than the maximum dimension of g1 and g2 and the intersection value includes Points interior to both g1 and g2, and the intersection value is not equal to either g1 or g2
Intersects Intersects( geom1 Geometry , geom2 Geometry ) : Integer
ST_Intersects( geom1 Geometry , geom2 Geometry ) : Integer		 X GEOS The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with NULL arguments;
convenience predicate: TRUE if the intersection of g1 and g2 is not empty
Contains Contains( geom1 Geometry , geom2 Geometry ) : Integer
ST_Contains( geom1 Geometry , geom2 Geometry ) : Integer		 X GEOS The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with NULL arguments;
convenience predicate: TRUE if g2 is completely contained in g1
Covers Covers( geom1 Geometry , geom2 Geometry ) : Integer
ST_Covers( geom1 Geometry , geom2 Geometry ) : Integer		 GEOS The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with NULL arguments;
convenience predicate: TRUE if g1 completely covers g2
CoveredBy CoveredBy( geom1 Geometry , geom2 Geometry ) : Integer
ST_CoveredBy( geom1 Geometry , geom2 Geometry ) : Integer		 GEOS The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with NULL arguments;
convenience predicate: TRUE if g1 is completely covered by g2
Relate

OGC canonical signature

Relate( geom1 Geometry , geom2 Geometry , patternMatrix String ) : Integer
ST_Relate( geom1 Geometry , geom2 Geometry , patternMatrix Text ) : Integer		 X GEOS For more informations about patternMatrix interpretation please read: DE-9IM

The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with NULL arguments;
returns TRUE if the spatial relationship specified by the patternMatrix holds.

non-canonical signature (PostGIS-like)

Relate( geom1 Geometry , geom2 Geometry ) : Text
ST_Relate( geom1 Geometry , geom2 Geometry ) : Text
Relate( geom1 Geometry , geom2 Geometry , bnr Integer ) : Text
ST_Relate( geom1 Geometry , geom2 Geometry , bnr Integer ) : Text		 GEOS The return type is Text, this corresponding to the serialized representation of some [DE-9IM] intersection matrix.
The optional argument bnr determines the Boundary Node Rule to be appiled, and has the following interpretation:
  • 1: OGC/MOD2 (this is the default case, and will be always assumed for any unknown value)
  • 2: Endpoint
  • 3: Multivalent Endpoint
  • 4: Monovalent Endpoint
A patternMatrix returned by this function is then expected to be evaluated later by ST_RelateMatch().
The main advantage in using this approach is that this way a comprehensive result (the patternMatrix itself) representing all possible intersection modes between two geometries will be be computed just once by ST_Relate() and could then be more precisely qualified by repeatedly calling ST_RelateMatch(); the first operation is computationally heavy but the second is really fast, so an overall performance boot is usually expected when more than a single intersection mode have to be checked at the same time, as e.g. OVERLAPS OR TOUCHES OR INTERSECTS.
Will return NULL on NULL or invalid geometries.
RelateMatch ST_RelatedMatch( matrix Text , pattern Text ) : Integer GEOS Evaluates if an intersection matrix [DE-9IM] satisfies an intersection pattern.
The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with NULL or invalid arguments.

SQL functions for distance relationships

Function Syntax OGC
defined		 required
module		 Summary
Distance Distance( geom1 Geometry , geom2 Geometry ) : Double precision
ST_Distance( geom1 Geometry , geom2 Geometry ) : Double precision		 X GEOS return the distance between geom1 and geom2 (always measured in CRS units).
Distance( geom1 Geometry , geom2 Geometry , use_ellipsoid Boolean ) : Double precision
ST_Distance( geom1 Geometry , geom2 Geometry , use_ellipsoid Boolean ) : Double precision		 X GEOS return the distance between geom1 and geom2 (measured in meters).
If the use_ellipsoid argument is set to TRUE the precise (but slower) distance will be computed on the Ellipsoid, otherwise will be computed on the Great Circle (approximative, but faster).
This function only supports Long/Lat coordinates, and will return NULL for any planar CRS
PtDistWithin PtDistWithin( geom1 Geometry , geom2 Geometry, range Double precision [, use_spheroid Integer ] ) : Integer PROJ return TRUE (1) if the distance between geom1 and geom2 is within the given range.
Usually distances are expressed in the length unit corresponding to the geoms own SRID: but if both geoms are simple POINTs and their SRID is 4326 (i.e. WGS84), then distances are expressed in meters.
In this later case the optional arg use_spheroid can be used to select the distance algorithm to be used: is use_spheroid = 1 the slowest but most accurate geodesic distance will be evaluated: in any other case the simplest great circle distance will be used instead

SQL functions supporting Linear Referencing

Function Syntax OGC
defined		 required
module		 Summary
AddMeasure ST_AddMeasure( geom Geometry , m_start Double precision , m_end Double precision ) : Geometry base Return a derived Geometry with M-values linearly interpolated between the start and end points.
NULL will be returned if any error is encountered.
Please note: NULL will be returned if geom is not of the Linestring or MultiLinestring type.
InterpolatePoint ST_InterpolatePoint( line Geometry , point Geometry ) : Double precision GEOS Interpolates the M-value of a linear Geometry at the point closest to the given point.
NULL will be returned if any error is encountered.
Please note: NULL will be returned if the first geometry is not a Linestring supporting M-values, or if the second geometry is not a Point.
LocateAlongMeasure ST_Locate_Along_Measure( geom Geometry , m_value Double precision ) : Geometry
ST_LocateAlong( geom Geometry , m_value Double precision ) : Geometry		 base Return a derived geometry collection value with elements that match the specified measure.
NULL will be returned if any error is encountered (or when no element corresponding to the given measure is found).
Please note: NULL will be returned if geom doesn't contain M-dimension, or if geom contains a Polygon, or if geom is a GeometryCollection.
LocateBetweenMeasures ST_Locate_Between_Measures( geom Geometry , m_start Double precision , m_end Double precision ) : Geometry
ST_LocateBetween( geom Geometry , m_start Double precision , m_end Double precision ) : Geometry		 base Return a derived geometry collection value with elements that match the specified range of measures.
NULL will be returned if any error is encountered (or when no element corresponding to the given range of measures is found).
Please note: NULL will be returned if geom doesn't contain M-dimension, or if geom contains a Polygon, or if geom is a GeometryCollection.
IsValidTrajectory ST_IsValidTrajectory( geom Geometry ) : Integer base Check if a Geometry corresponds to a valid Trajectory.
a Trajectory is assumed to be a LINESTRING supporting M-values growing from each vertex to the next.
NULL will be returned on invalid arguments.
TrajectoryInterpolatePoint ST_TrajectoryInterpolarePoint( geom Geometry , m_value Double ) : Geometry base Check if a Geometry corresponds to a valid Trajectory.
Return a POINT Geometry being interpolated along the Geometry (that is expected to be a valid Trajectory) accordingly to the given M-value.
The interpolated Point will have the same dimensions and SRID of the Trajectory.
NULL will be returned on invalid arguments.

SQL functions that implement spatial operators

Function Syntax OGC
defined		 required
module		 Summary
Intersection Intersection( geom1 Geometry , geom2 Geometry ) : Geometry
ST_Intersection( geom1 Geometry , geom2 Geometry ) : Geometry		 X GEOS return a geometric object that is the intersection of geometric objects geom1 and geom2
Difference Difference( geom1 Geometry , geom2 Geometry ) : Geometry
ST_Difference( geom1 Geometry , geom2 Geometry ) : Geometry		 X GEOS return a geometric object that is the closure of the set difference of geom1 and geom2
GUnion GUnion( geom1 Geometry , geom2 Geometry ) : Geometry
OpenGis name for this function is Union(), but it conflicts with an SQLite reserved keyword
ST_Union( geom1 Geometry , geom2 Geometry ) : Geometry 		X GEOS return a geometric object that is the set union of geom1 and geom2
GUnion GUnion( geom Geometry ) : Geometry
ST_Union( geom Geometry ) : Geometry		 X GEOS return a geometric object that is the set union of input values aggregate function
SymDifference SymDifference( geom1 Geometry , geom2 Geometry ) : Geometry
ST_SymDifference( geom1 Geometry , geom2 Geometry ) : Geometry		 X GEOS return a geometric object that is the closure of the set symmetric difference of geom1 and geom2 (logical XOR of space)
Buffer Buffer( geom Geometry , dist Double precision [ , quadrantsegments Integer ] ) : Geometry
ST_Buffer( geom Geometry , dist Double precision [ , quadrantsegments Integer ] ) : Geometry		 X GEOS return a geometric object defined by buffering a distance around the geom, where dist is in the distance units for the Spatial Reference of geom.
the optional quadrantsegments argument specifies the number of segments used to approximate a quarter circle (default is 30).
ConvexHull ConvexHull( geom Geometry ) : Geometry
ST_ConvexHull( geom Geometry ) : Geometry		 X GEOS return a geometric object that is the convex hull of geom

SQL functions that implement spatial operators
[GEOS specific features]

HausdorffDistance HausdorffDistance( geom1 Geometry , geom2 Geometry ) : Double precision
HausdorffDistance( geom1 Geometry , geom2 Geometry , densify_fract Double precision ) : Double precision
ST_HausdorffDistance( geom1 Geometry , geom2 Geometry ) : Double precision
ST_HausdorffDistance( geom1 Geometry , geom2 Geometry , densify_fract Double precision ) : Double precision		 GEOS return the Hausdorff distance between geom1 and geom2
learn more
the optional argument densify_fract is the fraction (in the range 0.0 / 1.0) by which to densify each segment. Each segment will be split into a number of equal-lenght subsegments, whose fraction of the total length is closest to the given fraction.
The smaller densify_fract we specify, the more acurate Hausdorff distance we get. But, the computation time and the memory usage increase with the square of the number of subsegments.
The second form of this SQL function (supporting densify_fract) is only available when using GEOS 3.7.0 (or any subsequent version).
FrechetDistance FrechetDistance( geom1 Geometry , geom2 Geometry ) : Double precision
FrechetDistance( geom1 Geometry , geom2 Geometry , densify_fract Double precision ) : Double precision
ST_FrechetDistance( geom1 Geometry , geom2 Geometry ) : Double precision
ST_FrechetDistance( geom1 Geometry , geom2 Geometry , densify_fract Double precision ) : Double precision		 GEOS return the Fréchet distance between geom1 and geom2
learn more
the optional argument densify_fract is the fraction (in the range 0.0 / 1.0) by which to densify each segment. Each segment will be split into a number of equal-lenght subsegments, whose fraction of the total length is closest to the given fraction.
The smaller densify_fract we specify, the more acurate Fréchet distance we get. But, the computation time and the memory usage increase with the square of the number of subsegments.
This SQL function is only available when using GEOS 3.7.0 (or any subsequent version).
OffestCurve OffsetCurve( geom Curve , radius Double precision ) : Curve
ST_OffsetCurve( geom Curve , radius Double precision ) : Curve		 GEOS return a geometric object representing the corresponding left-sided (positive radius) or right-sided (negative radius) offset curve
NULL is returned whenever is not possible deriving an offset curve from the original geometry
[a single not-closed LINESTRING is expected as input]
SingleSidedBuffer SingleSidedBuffer( geom Curve , radius Double precision , left_or_right Integer ) : Curve
ST_SingleSidedBuffer( geom Curve , radius Double precision , left_or_right Integer ) : Curve		 GEOS return a geometric object representing the corresponding left- (or right-sided) single-sided buffer
NULL is returned whenever is not possible deriving a single-sided buffer from the original geometry
[a single not-closed LINESTRING is expected as input]
SharedPaths SharedPaths( geom1 Geometry , geom2 Geomety ) : Geometry
ST_SharedPaths( geom1 Geometry , geom2 Geomety ) : Geometry		 GEOS return a geometric object (of the MULTILINESTRING type) representing any common lines shared by both geometries
NULL is returned is no common line exists
Line_Interpolate_Point Line_Interpolate_Point( line Curve , fraction Double precision ) : Point
ST_Line_Interpolate_Point( line Curve , fraction Double precision ) : Point		 GEOS return a point interpolated along a line.
Second argument (between 0.0 and 1.0) representing fraction of total length of linestring the point has to be located.
NULL is returned for invalid arguments
Line_Interpolate_Equidistant_Points Line_Interpolate_Equidistant_Points( line Curve , distance Double precision) : MultiPoint
ST_Line_Interpolate_Equidistant_Points( line Curve , distance Double precision ) : MultiPoint		 GEOS return a set of equidistant points interpolated along a line; the returned geometry always corresponds to a MULTIPOINT supporting the M coordinate (representing the progressive distance for each interpolated Point).
Second argument represents the regular distance between interpolated points.
NULL is returned for invalid arguments
Line_Locate_Point Line_Locate_Point( line Curve , point Point ) : Double precision
ST_Line_Locate_Point( line Curve , point Point ) : Double precision		 GEOS return a number (between 0.0 and 1.0) representing the location of the closest point on LineString to the given Point, as a fraction of total 2d line length.
NULL is returned for invalid arguments
Line_Substring Line_Substring( line Curve , start_fraction Double precision , end_fraction Double precision ) : Curve
ST_Line_Substring( line Curve , start_fraction Double precision , end_fraction Double precision ) : Curve		 GEOS Return a Linestring being a substring of the input one starting and ending at the given fractions of total 2d length.
Second and third arguments are expected to be in the range between 0.0 and 1.0.
NULL is returned for invalid arguments
ClosestPoint ClosestPoint( geom1 Geometry , geom2 Geometry ) : Point
ST_ClosestPoint( geom1 Geometry , geom2 Geometry ) : Point		 GEOS Returns the Point on geom1 that is closest to geom2.
NULL is returned for invalid arguments (or if distance is ZERO)
ShortestLine ShortestLine( geom1 Geometry , geom2 Geometry ) : Curve
ST_ShortestLine( geom1 Geometry , geom2 Geometry ) : Curve		 GEOS Returns the shortest line between two geometries.
NULL is returned for invalid arguments (or if distance is ZERO)
Snap Snap( geom1 Geometry , geom2 Geometry , tolerance Double precision ) : Geometry
ST_Snap( geom1 Geometry , geom2 Geometry , tolerance Double precision ) : Geometry		 GEOS Returns a new Geometry representing a modified geom1, so to "snap" vertices and segments to geom2 vertices; a snap distance tolerance is used to control where snapping is performed.
NULL is returned for invalid arguments
Collect Collect( geom1 Geometry , geom2 Geometry ) : Geometry
ST_Collect( geom1 Geometry , geom2 Geometry ) : Geometry		 GEOS a generic Geometry (possibly a GEOMETRYCOLLECTION) will be returned merging geom1 and geom2
NULL will be returned if any error is encountered
Collect Collect( geom Geometry ) : Geometry
ST_Collect( geom Geometry ) : Geometry		 GEOS a generic Geometry (possibly a GEOMETRYCOLLECTION) will be returned merging input Geometries all together
aggregate function
NULL will be returned if any error is encountered
LineMerge LineMerge( geom Geometry ) : Geometry
ST_LineMerge( geom Geometry ) : Geometry		 GEOS a Geometry (actually corresponding to a LINESTRING or MULTILINESTRING) will be returned.
The input Geometry is expected to represent a LINESTRING or a MULTILINESTRING.
The input Geometry can be an arbitrary collection of sparse line fragments: this function will then try to (possibly) reassemble them into one (or more) Linestring(s).
NULL will be returned if any error is encountered
BuildArea BuildArea( geom Geometry ) : Geometry
ST_BuildArea( geom Geometry ) : Geometry		 GEOS a Geometry (actually corresponding to a POLYGON or MULTIPOLYGON) will be returned.
The input Geometry is expected to represent a LINESTRING or a MULTILINESTRING.
The input Geometry can be an arbitrary collection of sparse Linestrings: this function will then try to (possibly) reassemble them into one (or more) polygon(s).
NULL will be returned if any error is encountered
Polygonize Polygonize( geom Geometry ) : Geometry
ST_Polygonize( geom Geometry ) : Geometry		 GEOS Exactly the same as ST_BuildArea, but implemented as an aggregate function.
NULL will be returned if any error is encountered
MakePolygon MakePolygon( geom1 Geometry [ , geom2 Geometry ] ) : Geometry
ST_MakePolygon( geom1 Geometry [ , geom2 Geometry ] ) : Geometry		 base Kind of lightweight/simplified ST_BuildArea: the first input Geometry is always expected to represent a closed LINESTRING assumed to identify the output polygon's Exterior Ring.
The second input Geometry (if any) is expected to be a LINESTRING or MULTILINESTRING identifying any polygon's Interior Ring (and all them are expected to be correctly closed).
Please note well: this function doesn't perform any internal topology check, so it could possibly return an invalid polygon on invalid input.
NULL will be returned if any error is encountered
UnaryUnion UnaryUnion( geom Geometry ) : Geometry
ST_UnaryUnion( geom Geometry ) : Geometry		 GEOS Exactely the same as ST_Union, but applied to a single Geometry.
(set union of elementary Geometries within a MULTI- or GEOMETRYCOLLECTION complex Geometry)
NULL will be returned if any error is encountered
DrapeLine ST_DrapeLine( geom1 Geometry , geom2 Geometry ) : Geometry
ST_DrapeLine( geom1 Geometry , geom2 Geometry , tolerance Double ) : Geometry		 GEOS Will return a 3D Linestring by draping geom1 over geom2:
  • geom1 is expected to be a 2D Linestring (XY or XYM dimensions).
  • geom2 is expected to be a 3D Linestring (XYZ or XYZM dimensions).
  • both geom1 and geom2 must share the same SRID.
  • the option tolerance is the distance radius between corresponding 2D and 3d points.
    By default a 0.0 tolerance is intended, thus implying an exact coincidence of X and Y coordinates.
Note: The returned Linestring will contain all Vertices defined by geom1 faithfully preserving their X and Y values; Z and M values will be recovered from the corresponding 3D Vertex from geom2, or will be possibily interpolated if no direct match exists.
If neither a direct match nor interpolation can be applied then Z and M will assume a conventional 0.0 value.
NULL will be returned on invalid arguments or if any error is encountered.
DrapeLineExceptions ST_DrapeLineExceptions( geom1 Geometry , geom2 Geometry ) : Geometry
ST_DrapeLineExceptions( geom1 Geometry , geom2 Geometry , tolerance Double ) : Geometry
ST_DrapeLineExceptions( geom1 Geometry , geom2 Geometry , tolerance Double , interpolated Bool ) : Geometry		 GEOS Will return a 3D MultiPoint containing all undraped Vertices encountered when draping geom1 over geom2:
  • geom1, geom2 and tolerance exactly have the same interpretation as in ST_DrapeLine().
  • the option interpolated argument has the following interpreation:
    • if FALSE all 2D Vertices lacking a matching 3D Vertex will be inserted into the returned MultiPoint, even when Z and/or M values have been succesfully interpolated.
    • if TRUE only 2D Vertices completely lacking Z and/or M values (defaulting to 0.0) will be inserted into the returned MultiPoint.
    • if not specified then TRUE is assumed by default.
NULL will be returned on invalid arguments, or if any error is encountered or if there aren't undraped Vertices.
DissolveSegments DissolveSegments( geom Geometry ) : Geometry
ST_DissolveSegments( geom Geometry ) : Geometry		 base a Geometry (actually corresponding to a LINESTRING, MULTILINESTRING or GEOMETRYCOLLECTION) will be returned.
The input Geometry is arbitrary: any POINT will remain unaffected, but any LINESTRING or RING will be dissolved into elementary segments.
NULL will be returned if any error is encountered
DissolvePoints DissolvePoints( geom Geometry ) : Geometry
ST_DissolvePoints( geom Geometry ) : Geometry		 base a Geometry (actually corresponding to a POINT or MULTIPOINT) will be returned.
The input Geometry is arbitrary: any POINT will remain unaffected, but any LINESTRING or RING will be dissolved into elementary Points corresponding to each Vertex.
NULL will be returned if any error is encountered
LinesFromRings LinesFromRings( geom Geometry ) : Geometry
ST_LinesFromRings( geom Geometry ) : Geometry		 base a Geometry (actually corresponding to a LINESTRING or MULTILINESTRING) will be returned.
The input Geometry is expected to be a POLYGON or MULTIPOLYGON; any RING will then be transformed into the corresponding LINESTRING.
NULL will be returned if any error is encountered
LinesCutAtNodes LinesCutAtNodes( geom1 Geometry , geom2 Geometry ) : Geometry
ST_LinesCutAtNodes( geom1 Geometry , geom2 Geometry ) : Geometry		 base a Geometry (actually corresponding to a LINESTRING or MULTILINESTRING) will be returned.
The first input Geometry is expected to be a LINESTRING or MULTILINESTRING (Lines); the second input Geometry is expected to be a POINT or MULTIPOINT (Nodes).
any Line will then be possibly split in two halves where some vertex exactly intercepts a Node.
NULL will be returned if any error is encountered
RingsCutAtNodes RingsCutAtNodes( geom Geometry ) : Geometry
ST_RingsCutAtNodes( geom Geometry ) : Geometry		 base a Geometry (actually corresponding to a LINESTRING or MULTILINESTRING) will be returned.
The input Geometry is expected to be a POLYGON or MULTIPOLYGON (Rings); any self-intersection or intersection between Rings is assumed to represent a Node.
any Ring will then be possibly split in two halves where some vertex exactly intercepts a Node.
NULL will be returned if any error is encountered
CollectionExtract CollectionExtract( geom Geometry , type Integer ) : Geometry
ST_CollectionExtract( geom Geometry , type Integer ) : Geometry		 base Given any arbitrary GEOMETRY will return a derived geometry consisting only of the specified type. Sub-geometries that are not the specified type are ignored.
  • 1 = POINT-type
  • 2 = LINESTRING-type
  • 3 = POLYGON-type
NULL will be returned if any error is encountered (or when no item of required type is found)
The Type of the returned Geometry could be e.g. POINT or MULTIPOINT depending on actual items count.
ExtractMultiPoint ExtractMultiPoint( geom Geometry ) : Geometry base Given any arbitrary GEOMETRY will return a derived MULTIPOINT geometry. Sub-geometries not being of the POINT type will be ignored.
NULL will be returned if any error is encountered (or when no POINT is found).
ExtractMultiLinestring ExtractMultiLinestring( geom Geometry ) : Geometry base Given any arbitrary GEOMETRY will return a derived MULTILINESTRING geometry. Sub-geometries not being of the LINESTRING type will be ignored.
NULL will be returned if any error is encountered (or when no LINESTRING is found).
ExtractMultiPolygon ExtractMultiPolygon( geom Geometry ) : Geometry base Given any arbitrary GEOMETRY will return a derived MULTIPOLYGON geometry. Sub-geometries not being of the POLYGON type will be ignored.
NULL will be returned if any error is encountered (or when no POLYGON is found).

SQL functions that implement spatial operators
[GEOS advanced features]

Function Syntax OGC
defined		 required
module		 Summary
DelaunayTriangulation DelaunayTriangulation( geom Geometry [ , edges_only Boolean [ , tolerance Double precision ] ] ) : Geometry
ST_DelaunayTriangulation( geom Geometry [ , edges_only Boolean [ , tolerance Double precision ] ] ) : Geometry		 GEOS-advanced return a geometric object representing the Delaunay Triangulation corresponding to the input Geometry
The input Geometry could have any arbitrary type; eventually all Linestring's / Polygon's vertices will be dissolved into Points, so to implicitly always get a MultiPoint.
The returned Geometry will usually be of the MultiPolygon type (a collection of Triangles), but will be a MultiLinestring if the optional edges_only argument is set to TRUE
The optional argument tolerance is intended to normalize the input Geometry, suppressing repeated (or too close) Points.
NULL is returned on failure.
VoronojDiagram VoronojDiagram( geom Geometry [ , edges_only Boolean [ , frame_extra_size Double precision [ , tolerance Double precision ] ] ] ) : Geometry
ST_VoronojDiagram( geom Geometry [ , edges_only Boolean [ , frame_extra_size Double precision [ , tolerance Double precision ] ] ] ) : Geometry		 GEOS-advanced return a geometric object representing the Voronoj Diagram corresponding to the input Geometry
The input Geometry could have any arbitrary type; eventually all Linestring's / Polygon's vertices will be dissolved into Points, so to implicitly always get a MultiPoint.
The returned Geometry will usually be of the MultiPolygon type, but will be a MultiLinestring if the optional edges_only argument is set to TRUE
The optional argument extra_frame_size allows to arbitrarily set the percent extension of the bounding frame: the default value is 5%.
The optional argument tolerance is intended to normalize the input Geometry, suppressing repeated (or too close) Points.
NULL is returned on failure.
ConcaveHull ConcaveHull( geom Geometry [ , factor Double precision [ , allow_holes Boolean [ , tolerance Double precision ] ] ] ) : Geometry
ST_ConcaveHull( geom Geometry [ , factor Double precision [ , allow_holes Boolean [ , tolerance Double precision ] ] ] ) : Geometry		 GEOS-advanced return a geometric object representing the ConcaveHull corresponding to the input Geometry
The input Geometry could have any arbitrary type; eventually all Linestring's / Polygon's vertices will be dissolved into Points, so to implicitly always get a MultiPoint.
The returned Geometry will always be of the Polygon or MultiPolygon type.
The default factor applies a 3σ filtering; by declaring some lower factor you can get a more aggressive filtering effect.
By setting the optional allow_holes argument to TRUE all interior holes will be preserved.
The optional argument tolerance is intended to normalize the input Geometry, suppressing repeated (or too close) Points.
NULL is returned on failure.

SQL functions that implement spatial operators
[RTTOPO features]

Function Syntax OGC
defined		 required
module		 Summary
MakeValid MakeValid( geom Geometry ) : Geometry
ST_MakeValid( geom Geometry ) : Geometry		 RTTOPO return a geometric object representing the repaired version of the input Geometry.
If the input Geometry was already valid, then it will be returned exactly as it was.
NULL is returned on failure.
MakeValidDiscarded MakeValidDiscarded( geom Geometry ) : Geometry
ST_MakeValidDiscarded( geom Geometry ) : Geometry		 RTTOPO return a geometric object containing all elements that would be eventually discarded by ST_MakeValid() while validating the same input Geometry.
NULL is returned on failure, or if no discarded item exists.
Segmentize Segmentize( geom Geometry, dist Double precision ) : Geometry
ST_Segmentize( geom Geometry , dist Double precision ) : Geometry		 RTTOPO return a new Geometry corresponding to the input Geometry; as much Linestring / Ring vertices as required will be eventually interpolated so to ensure that no segment will be longer than dist.
NULL is returned on failure.
Split Split( geom Geometry, blade Geometry ) : Geometry
ST_Split( geom Geometry , blade Geometry ) : Geometry		 RTTOPO return a new Geometry collecting all items resulting by splitting the input Geometry by the blade.
NULL is returned on failure.
SplitLeft SplitLeft( geom Geometry, blade Geometry ) : Geometry
ST_SplitLeft( geom Geometry , blade Geometry ) : Geometry		 RTTOPO return a new Geometry collecting all items resulting by splitting the input Geometry by the blade and falling on the left side.
All items not affected by the split operation (i.e. not intersecting the blade) will be returned into the left collection.
NULL is returned on failure.
SplitRight SplitRight( geom Geometry, blade Geometry ) : Geometry
ST_SplitRight( geom Geometry , blade Geometry ) : Geometry		 RTTOPO return a new Geometry collecting all items resulting by splitting the input Geometry by the blade and falling on the right side.
NULL is returned on failure (or if the right side is empty).
SnapAndSplit SnapAndSplit( geom1 Geometry , geom2 Geometry , tolerance Double precision ) : Geometry
ST_SnapAndSplit( geom1 Geometry , geom2 Geometry , tolerance Double precision ) : Geometry		 RTTOPO This one simply is a convenience function accepting the same arguments of ST_Snap() (with identical meaning).
  • geom1 is expected to be a LINESTRING or a MULTILINESTRING
  • geom2 is expected to be a POINT or a MULTIPOINT
  • first pass: geom1 will be snapped against geom2
  • second pass: the resulting geometry returned by the above snap will then be split using geom2 as the cutting blade.
  • the final result returned by SnapAndSplit will always be a MULTILINESTRING
NULL is returned for invalid arguments
Azimuth Azimuth( pt1 Geometry, pt2 Geometry ) : Double precision
ST_Azimuth( pt1 Geometry , pt2 Geometry ) : Double precision		 RTTOPO return the angle (in radians) from the horizontal of the vector defined by pt1 and pt2.
Both pt1 and pt2 are expected to be simple Points.
Starting since 4.1.0 if both points supports long/lat coords the returned Azimuth will be precisely computed on the ellipsoid.
NULL is returned on failure.
On the clock: 12=0; 3=PI/2; 6=PI; 9=3PI/2
Project Project( start_point Geometry, distance Double precision, azimuth Double precision ) : Geometry
ST_Project( start_point Geometry, distance Double precision, azimuth Double precision ) : Geometry		 RTTOPO return a new Point projected from a start point using a bearing and distance.
start_point is expected to be simple long/lat Point.
distance is expected to be measured in meters; azimuth (aka bearing or heading) has the same identical meaning as in ST_Azimuth().
NULL is returned on failure or on invalid arguments.
SnapToGrid SnapToGrid( geom Geometry , size Double precision ) : Geometry
SnapToGrid( geom Geometry , size_x Double precision , size_y Double precision ) : Geometry
SnapToGrid( geom Geometry , origin_x Double precision , origin_y Double precision , size_x Double precision , size_y Double precision ) : Geometry
SnapToGrid( geom Geometry , origin Geometry , size_x Double precision , size_y Double precision , size_z Double precision , size_m Double precision ) : Geometry
ST_SnapToGrid( geom Geometry , size Double precision ) : Geometry
ST_SnapToGrid( geom Geometry , size_x Double precision , size_y Double precision ) ) : Geometry
ST_SnapToGrid( geom Geometry , origin_x Double precision , origin_y Double precision , size_x Double precision , size_y Double precision ) ) : Geometry
ST_SnapToGrid( geom Geometry , origin Geometry , size_x Double precision , size_y Double precision , size_z Double precision , size_m Double precision ) : Geometry		 base return a new Geometry corresponding to the input Geometry; all points and vertices will be snapped to the grid defined by its origin and size(s).
Removes all consecutive points falling on the same cell.
All collapsed geometries will be stripped from the returned Geometry.
NULL is returned on failure.
GeoHash GeoHash( geom Geometry [ , precision Integer ] ) : String
ST_GeoHash( geom Geometry [ , precision Integer ] ) : String		 RTTOPO Return a GeoHash representation (geohash.org) of the geometry.
A GeoHash encodes a point into a text form that is sortable and searchable based on prefixing.
  • If no precision is specficified ST_GeoHash returns a GeoHash based on full precision of the input geometry type.
    Points return a GeoHash with 20 characters of precision (about enough to hold the full double precision of the input).
  • Other types return a GeoHash with a variable amount of precision, based on the size of the feature. Larger features are represented with less precision, smaller features with more precision. The idea is that the box implied by the GeoHash will always contain the input feature.
  • When explicitly set the precision argument will determine how many characters should by used by the returned GeoHash.
ST_GeoHash will not work with geometries that are not in geographic (lon/lat) coordinates
AsX3D AsX3D( geom Geometry ) : String
AsX3D( geom Geometry , precision Integer ) : String
AsX3D( geom Geometry , precision Integer , options Integer ) : String
AsX3D( geom Geometry , precision Integer , options Integer , refid String ) : String
ST_AsX3D( geom Geometry ) : String
ST_AsX3D( geom Geometry , precision Integer ) : String
ST_AsX3D( geom Geometry , precision Integer , options Integer ) : String
ST_AsX3D( geom Geometry , precision Integer , options Integer , refid String ) : String 		RTTOPO Returns a geometry as an X3D XML formatted node element.
MaxDistance MaxDistance( geom1 Geometry , geom2 Geometry ) : Double precision
ST_MaxDistance( geom1 Geometry , geom2 Geometry ) : Double precision		 RTTOPO return the max distance between geom1 and geom2
3DDistance ST_3DDistance( geom1 Geometry , geom2 Geometry ) : Double precision RTTOPO return the 3D-distance between geom1 and geom2 (Z coordinates will be considered)
3DMaxDistance ST_3DMaxDistance( geom1 Geometry , geom2 Geometry ) : Double precision RTTOPO return the max 3D-distance between geom1 and geom2 (Z coordinates will be considered)
3dLength ST_3dLength( geom Geometry ) : Double precision RTTOPO return the total 2D or 3D-length of Linestring or MultiLinestring geometry.
Z coordinates if eventually present will be considered leading to a 3D measured length; otherwise a 2D length will be computed.
ST_Node ST_Node( geom Geometry ) : Geometry RTTOPO Fully nodes a set of linestrings using the least possible number of nodes while preserving all of the input ones.
NULL will be returned if the input Geometry isn't a set of linestrings or if any other error occurs.
SelfIntersections SelfIntersections( geom Geometry ) : Geometry
ST_SelfIntersections( geom Geometry ) : Geometry		 RTTOPO Returns a MultiPoint Geometry representing any self-intersection found within the input geometry [expected to be of the Linestring or MultiLinestring type].
NULL will be returned for invalid arguments, or when no self-intersections were found.
ST_Subdivide ST_Subdivide( geom Geometry ) : Geometry
ST_Subdivide( geom Geometry , max_vertices Integer ) : Geometry		 RTTOPO Divides geom into many parts until each part can be represented using no more than max_vertices.
If the optional argument max_vertices is not explicitly specified a limit of 128 vertices is implicitly assumed.
NULL will be returned on invalid arguments.

SQL functions for coordinate transformations

Function Syntax OGC
defined		 required
module		 Summary
Transform Transform( geom Geometry , newSRID Integer ) : Geometry
ST_Transform( geom Geometry , newSRID Integer ) : Geometry
Transform( geom Geometry , newSRID Integer , area_of_use Geometry ) : Geometry
ST_Transform( geom Geometry , newSRID Integer , area_of_use Geometry ) : Geometry
Transform( geom Geometry , newSRID Integer , area_of_use Geometry , proj_string_from Text ) : Geometry
ST_Transform( geom Geometry , newSRID Integer , area_of_use Geometry , proj_string_from Text ) : Geometry
Transform( geom Geometry , newSRID Integer , area_of_use Geometry , proj_string_from Text , proj_string_to Text ) : Geometry
ST_Transform( geom Geometry , newSRID Integer , area_of_use Geometry , proj_string_from Text , proj_string_to Text ) : Geometry		 PROJ return a geometric object obtained by reprojecting coordinates into the Reference System identified by newSRID
All the following optional arguments are available only when SpatiaLite is built on the top of PROJ.6 (or any later version):
  • area_of_use: the BBOX of this Geometry (always required to be in SRID=4326) will be possibly used by PROJ.6 in order to optimize the transformation as much as possible.
    may be eventually NULL (default setting).
  • proj_string_from: an user defined text string accepted by PROJ.6 identifying the origin CRS of the transformation.
  • proj_string_to: an user defined text string accepted by PROJ.6 identifying the destination CRS of the transformation.
  • Interpretation:
    • if both proj_string_from and proj_string_to are NULL (default setting) then PROJ.6 will be left free to choose the best possible transformation between the origin and the destination CRSes.
    • if both proj_string_from and proj_string_to are instead explicitly set then PROJ.6 will be constrained to identify the origin and destination CRSes accordingly to the received string values. Valid CRS strings accepted by PROJ.6 can be:
      • a proj-string as e.g. +proj=longlat +datum=WGS84 +no_defs
      • a WKT definition of the intended CRS.
      • a CRS name as e.g. WGS 84 or Monte Mario / Italy zone 1
      • a canonical CRS reference as e.g. EPSG:4326 or EPSG:3003
      • or in the alternative notation as e.g. urn:ogc:def:crs:EPSG::3003
    • Special case: if only proj_string_from is explicitly set whilst proj_string_to is NULL, then proj_string_from will be expected to contain an user defined transformation pipeline
    • Any other different combination of proj_string_from and proj_string_to will be consided invalid and will raise an exception.
TransformXY TransformXY( geom Geometry , newSRID Integer ) : Geometry
ST_TransformXY( geom Geometry , newSRID Integer ) : Geometry		 PROJ this is a special flavor of ST_Transform(); just X and Y coordinates will be transformed, Z and M values (if eventually present) will be left untouched.
Mainly intended as a workaround for (possibly) imprecise +towsg84 PROJ definitions; should never be used as a ST_Transform() replacement without a very careful evaluation.
TransformXYZ TransformXYZ( geom Geometry , newSRID Integer ) : Geometry
ST_TransformXYZ( geom Geometry , newSRID Integer ) : Geometry		 PROJ this is a special flavor of ST_Transform(); just X, Y and Z coordinates will be transformed, M values (if eventually present) will be left untouched.
Mainly intended as a workaround possibily useful when handling 4D geometries having M-values not corresponding to Time.
SridFromAuthCRS SridFromAuthCRS( auth_name String , auth_SRID Integer ) : Integer base return the internal SRID corresponding to auth_name and auth_SRID
-1 will be returned if no such CRS is defined
ShiftCoords
ShiftCoordinates		 ShiftCoords( geom Geometry , shiftX Double precision , shiftY Double precision ) : Geometry
ShiftCoordinates( geom Geometry , shiftX Double precision , shiftY Double precision ) : Geometry		 base return a geometric object obtained by translating coordinates according to shiftX and shiftY values
ST_Translate ST_Translate( geom Geometry , shiftX Double precision , shiftY Double precision , shiftZ Double precision ) : Geometry base return a geometric object obtained by translating coordinates according to shiftX, shiftY and shiftZ values
ST_Shift_Longitude ST_Shift_Longitude( geom Geometry ) : Geometry base return a geometric object obtained by translating any negative longitude by 360.
Only meaningful for geographic (longitude/latitude) coordinates.
Negative longitudes (-180/0) will be shifted by 360, thus allowing to represent longitudes in the 0/360 range and effectively crossing the International Date Line.
NormalizeLonLat NormalizeLonLat( geom Geometry ) : Geometry base return a geometric object obtained by normalizing any longitude in the range [-180 / +180] and any latitude in the range [-90 / + 90].
Only meaningful for geographic (longitude/latitude) coordinates.
ScaleCoords
ScaleCoordinates		 ScaleCoords( geom Geometry , scaleX Double precision [ , scaleY Double precision ] ) : Geometry
ScaleCoordinates( geom Geometry , scaleX Double precision [ , scaleY Double precision ] ) : Geometry		 base return a geometric object obtained by scaling coordinates according to scaleX and scaleY values
if only one scale factor is specified, then an isotropic scaling occurs [i.e. the same scale factor is applied to both axis]
otherwise an anisotropic scaling occurs [i.e. each axis is scaled according to its own scale factor]
RotateCoords
RotateCoordinates		 RotateCoords( geom Geometry , angleInDegrees Double precision ) : Geometry
RotateCoordinates( geom Geometry , angleInDegrees Double precision ) : Geometry		 base return a geometric object obtained by rotating coordinates according to angleInDegrees value.
Positive angle = clockwise rotation.
Negative angle = counterclockwise rotation.
ReflectCoords
ReflectCoordinates		 ReflectCoords( geom Geometry , xAxis Integer , yAxis Integer ) : Geometry
ReflectCoordinates( geom Geometry , xAxis Integer , yAxis Integer ) : Geometry		 base return a geometric object obtained by reflecting coordinates according to xAxis and yAxis switches
i.e. if xAxis is 0 (FALSE), then x-coordinates remains untouched; otherwise x-coordinates will be reflected
SwapCoords
SwapCoordinates		 SwapCoords( geom Geometry ) : Geometry
SwapCoordinates( geom Geometry ) : Geometry		 base return a geometric object obtained by swapping x- and y-coordinates

SQL functions supporting Geodesic Arcs

Function Syntax OGC
defined		 required
module		 Summary
GeodesicArcLength GeodesicArcLength( geom1 Geometry , geom2 Geometry ) : Double precision
GeodesicArcLength( geom1 Geometry , geom2 Geometry , meters Boolean ) : Double precision		 PROJ GEODESIC returns the Arc length (distance) between geom1 and geom2 as the surface measurement of the outer circle arc / earth surface.
if meters = 0 the result will be in degrees, otherwise meters [default].
will return NULL on invalid arguments, or if PROJ GEODESIC is not supported.
GeodesicChordLength GeodesicChordLength( geom1 Geometry , geom2 Geometry ) : Double precision
GeodesicChordLength( geom1 Geometry , geom2 Geometry , meters Boolean ) : Double precision		 PROJ GEODESIC returns the length of the shortest line (distance) between geom1 and geom2 through the outer circle / earth surface.
if meters = 0 the result will be in degrees, otherwise meters [default].
will return NULL on invalid arguments, or if PROJ GEODESIC is not supported.
GeodesicCentralAngle GeodesicCentralAngle( geom1 Geometry , geom2 Geometry ) : Double precision
GeodesicCentralAngle( geom1 Geometry , geom2 Geometry , radians Boolean ) : Double precision		 PROJ GEODESIC returns the angle from the circle center to the geom1 and geom2 on the outer circle / earth surface.
if radians = 0 the result will be in degrees, otherwise radians [default].
will return NULL on invalid arguments, or if PROJ GEODESIC is not supported.
GeodesicArcArea GeodesicArcArea( geom1 Geometry , geom2 Geometry ) : Double precision PROJ GEODESIC returns the area of the segment/arc between the Chord and Arc, created by geom1 and geom2, inside the outer circle / earth surface.
Since the Radius is in meters, only meter values can be returned.
will return NULL on invalid arguments, or if PROJ GEODESIC is not supported.
GeodesicArcHeight GeodesicArcHeight( geom1 Geometry , geom2 Geometry ) : Double precision PROJ GEODESIC returns the height of the segment/arc (short-Sagitta) between the Chord and Arc, created by geom1 and geom2, inside the outer circle / earth surface.
Since the Radius is in meters, only meter values can be returned.
will return NULL on invalid arguments, or if PROJ GEODESIC is not supported.

SQL functions supporting Affine Transformations and Ground Control Points

Function Syntax OGC
defined		 required
module		 Summary
ATM_Create ATM_Create( void ) : AffineMatrix
ATM_Create( a Integer , b Integer , d Integer , e Integer , xoff Integer , yoff Integer ] ) : AffineMatrix
ATM_Create( a Integer , b Integer , c Integer , d Integer , e Integer , f Integer , g Integer , h Integer , i Integer , xoff Integer , yoff Integer , zoff Integer ] ) : AffineMatrix		 base return a BLOB-encoded Affine Transformation matrix.
  • the first form (no arguments) will return an Identity matrix.
  • the second and third forms will respectively return a fully initialized 2D or 3D Affine Transformation matrix.
will return NULL on invalid arguments.
ATM_CreateTranslate ATM_CreateTranslate( tx Double precision , ty Double precision ] ) : AffineMatrix
ATM_CreateTranslate( tx Double precision , ty Double precision , tz Double precision ] ) : AffineMatrix		 base return a BLOB-encoded Affine Transformation matrix representing a 2D or 3D Translate transformation.
will return NULL on invalid arguments.
ATM_CreateScale ATM_CreateScale( sx Double precision , sy Double precision ] ) : AffineMatrix
ATM_CreateScale( sx Double precision , sy Double precision , sz Double precision ] ) : AffineMatrix		 base return a BLOB-encoded Affine Transformation matrix representing a 2D or 3D Scale transformation.
will return NULL on invalid arguments.
ATM_CreateRotate ATM_CreateRotate( angleInDegrees Double precision ] ) : AffineMatrix
ATM_CreateZRoll( angleInDegrees Double precision ] ) : AffineMatrix		 base return a BLOB-encoded Affine Transformation matrix representing a Rotate transformation (along the Z axis).
will return NULL on invalid argument.
ATM_CreateXRoll ATM_CreateXRoll( angleInDegrees Double precision ] ) : AffineMatrix base return a BLOB-encoded Affine Transformation matrix representing a Rotate transformation (along the X axis).
will return NULL on invalid argument.
ATM_CreateYRoll ATM_CreateYRoll( angleInDegrees Double precision ] ) : AffineMatrix base return a BLOB-encoded Affine Transformation matrix representing a Rotate transformation (along the Y axis).
will return NULL on invalid argument.
ATM_Multiply ATM_Multiply( matrixA AffineMatrix , matrixB AffineMatrix ) : AffineMatrix base return a BLOB-encoded Affine Transformation matrix representing the result of multiplying matrixA by matrixB.
will return NULL on invalid arguments.
ATM_Translate ATM_Translate( matrix AffineMatrix , tx Double precision , ty Double precision ] ) : AffineMatrix
ATM_CreateTranslate( matrix AffineMatrix , tx Double precision , ty Double precision , tz Double precision ] ) : AffineMatrix		 base return a BLOB-encoded Affine Transformation matrix by chaining a further 2D or 3D Translate to a previous transformation matrix.
will return NULL on invalid arguments.
ATM_Scale ATM_Scale( matrix AffineMatrix , sx Double precision , sy Double precision ] ) : AffineMatrix
ATM_Scale( matrix AffineMatrix , sx Double precision , sy Double precision , sz Double precision ] ) : AffineMatrix		 base return a BLOB-encoded Affine Transformation matrix by chaining a further 2D or 3D Scale to a previous transformation matrix.
will return NULL on invalid arguments.
ATM_Rotate ATM_Rotate( matrix AffineMatrix , angleInDegrees Double precision ] ) : AffineMatrix
ATM_ZRoll( matrix AffineMatrix , angleInDegrees Double precision ] ) : AffineMatrix		 base return a BLOB-encoded Affine Transformation matrix by chaining a further Rotate (along the Z axis) to a previous transformation matrix.
will return NULL on invalid argument.
ATM_XRoll ATM_XRoll( matrix AffineMatrix , angleInDegrees Double precision ] ) : AffineMatrix base return a BLOB-encoded Affine Transformation matrix by chaining a further Rotate (along the X axis) to a previous transformation matrix.
will return NULL on invalid argument.
ATM_YRoll ATM_YRoll( matrix AffineMatrix , angleInDegrees Double precision ] ) : AffineMatrix base return a BLOB-encoded Affine Transformation matrix by chaining a further Rotate (along the Y axis) to a previous transformation matrix.
will return NULL on invalid argument.
ATM_Determinant ATM_Determinant( matrix AffineMatrix ] ) : Double precision base return the Determinant from an Affine Transformation matrix.
will return 0.0 on invalid argument.
ATM_IsInvertible ATM_IsInvertible( matrix AffineMatrix ] ) : Integer base return 1 if the Affine Transformation matrix can be inverted, 0 if not.
will return -1 on invalid argument.
ATM_Invert ATM_Invert( matrix AffineMatrix ] ) : AffineMatrix base return an inverted Affine Transformation matrix.
will return NULL on invalid argument.
ATM_IsValid ATM_IsValid( matrix AffineMatrix ] ) : Integer base return 1 if the BLOB argument really contains a valid Affine Transformation matrix, 0 if not.
will return -1 on invalid argument.
ATM_AsText ATM_AsText( matrix AffineMatrix ] ) : Text base return a serialized text string corresponding to an Affine Transformation matrix.
will return NULL on invalid argument.
ATM_Transform ATM_Transform( geom Geometry , matrix AffineMatrix [ , newSRID Integer ] ) : Geometry base return a geometric object obtained by applying an Affine Transformation; if the optional arg newSRID is defined then the returned Geometry will assume the corresponding Reference System, otherwise it will preserve the same Reference System of the input Geometry.
will return NULL on invalid arguments.
GCP_Compute GCP_Compute( pointA Geometry , pointB Geometry [ order Integer] ) : PolynomialCoeffs GrassGis code
GPLv2+		 return BLOB-encoded objects containing Polynomial coefficients computed from a set of matching Ground Control Points pairs.
  • pointA corresponds to the origin Reference System; pointB corresponds to the destination target.
  • the input Geometries must be of the Point type and must have the same dimensions
  • if the points have XYZ or XYZM dimensions then 3D coeffs will be returned, otherwise 2D coeffs will be return.
  • the optional order argument can assume the following values:
    • 0: a set of 2D coeffs will be returned by applying the Thin Plate Spline method.
    • 1: (default setting) a set of 2D or 3D Polynomial coeffs of the 1st order will be returned.
    • 2: a set of 2D or 3D Polynomial coeffs of the 2nd order will be returned.
    • 3: a set of 2D or 3D Polynomial coeffs of the 3rd order will be returned.
will return NULL on invalid arguments
aggreagate function
GCP_IsValid GCP_IsValid( matrix PolynomialCoeffs ] ) : Integer GrassGis code
GPLv2+		 return 1 if the BLOB argument really contains valid Polynomial coeffs, 0 if not.
will return -1 on invalid argument.
GCP_AsText GCP_AsText( matrix PolynomialCoeffs ] ) : Text GrassGis code
GPLv2+		 return a serialized text string corresponding to the Polynomial coeffs.
will return NULL on invalid argument.
GCP2ATM GCP2ATM( matrix PolynomialCoeffs ] ) : AffineMatrix GrassGis code
GPLv2+		 return an Affine Transformation matrix corresponding to the Polynomial coeffs.
Only a set of Polynomial coeffs of the 1st order can be converted to an Affine Transformation matrix.
will return NULL on invalid argument.
GCP_Transform GCP_Transform( geom Geometry , coeffs PolynomialCoeffs [ , newSRID Integer ] ) : Geometry GrassGis code
GPLv2+		 return a geometric object obtained by applying a Transformation based on Polynomial coefficients of the 1st, 2nd or 3rd order; if the optional arg newSRID is defined then the returned Geometry will assume the corresponding Reference System, otherwise it will preserve the same Reference System of the input Geometry.
will return NULL on invalid arguments.

SQL functions for Spatial-MetaData and Spatial-Index handling

Function Syntax OGC
defined		 required
module		 Summary
InitSpatialMetaData InitSpatialMetaData( void ) : Integer
InitSpatialMetaData( transaction Integer ) : Integer
InitSpatialMetaData( mode String ) : Integer
InitSpatialMetaData( transaction Integer , mode String ) : Integer		 base Creates the geometry_columns and spatial_ref_sys metadata tables
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
  • if the optional argument transaction is set to TRUE the whole operation will be handled as a single Transaction (faster): the default setting is transaction=FALSE (slower, but safer).
  • if the optional argument mode is not specified then all possible ESPG SRID definitions will be inserted into the spatial_ref_sys table.
  • if the mode arg 'WGS84' (alias 'WGS84_ONLY') is specified, then only WGS84-related EPSG SRIDs will be inserted
  • if the mode arg 'NONE' (alias 'EMPTY') is specified, no EPSG SRID will be inserted at all
InitAdvancedMetaData InitAdvancedMetaData( void ) : Integer
InitAdvancedMetaData( transaction Integer ) : Integer 		base This one simply is an utility function intended to create several ancillary metadata tables required by libspatialite v.5 and subsequent versions.
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
Note: will be automatically called by both InitSpatialMetaData() or InitSpatialMetaDataFull(), but could be usefull for recovering old databases created by earlier versions; it's absolutely harmless because any existing MetaTable will be left untouched.
InitSpatialMetaDataFull InitSpatialMetaDataFull( void ) : Integer
InitSpatialMetaDataFull( transaction Integer ) : Integer
InitSpatialMetaDataFull( mode String ) : Integer
InitSpatialMetaDataFull( transaction Integer , mode String ) : Integer		 base This one simply is a convenience function accepting the same arguments of InitSpatialMetaData() (with identical meaning).
The intended scope is to fully initialize all metadata tables required by libspatialite 5.0.0 (and following versions). This function will internally call in a single pass:
  • InitSpatialMetaData()
  • InitAdvancedMetaData()
  • CreateRasterCoveragesTable()
  • CreateVectorCoveragesTables()
  • CreateStylingTables()
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
CreateMissingSystemTables CreateMissingSystemTables( void ) : Integer
CreateMissingSystemTables( relaxed Integer ) : Integer
CreateMissingSystemTables( relaxed Integer , transaction Integer ) : Integer 		base This function will create any missing ancillary metadata table required by libspatialite v.5 and subsequent versions.
  • the optional argument relaxed has the same interpretation as in CreateStylingTables(): if it is explicitly set as TRUE then a relaxed version of the validating Triggers will be installed (not checking for strict XSD schema validation).
  • if the optional argument transaction is explicitly set as TRUE then the whole operation will be atomically confined within a monolithic SQL transaction
The return type is Integer, with a return value of 1 on success.
An exception will be raised on invalid arguments or on failure.
Note: this SQL function is intended for safely upgrading old databases created by earlier versions; it's absolutely harmless because any existing MetaTable will be left untouched.
InsertEpsgSrid InsertEpsgSrid( srid Integer ) : Integer base Attempts to insert into spatial_ref_sys the EPSG definition uniquely identified by srid
[the corresponding EPSG SRID definition will be copied from the inlined dataset defined in libspatialite]
the return type is Integer, with a return value of 1 for success or 0 for failure
AddGeometryColumn AddGeometryColumn( table String , column String , srid Integer , geom_type String [ , dimension String [ , not_null Integer ] ] ) : Integer X base Creates a new geometry column updating the Spatial Metadata tables and creating any required trigger in order to enforce constraints
geom_type has to be one of the followings:
  • 'POINT', 'POINTZ', 'POINTM', 'POINTZM'
  • 'LINESTRING', 'LINESTRINGZ', 'LINESTRINGM', 'LINESTRINGZM'
  • 'POLYGON', 'POLYGONZ', 'POLYGONM', 'POLYGONZM'
  • 'MULTIPOINT', 'MULTIPOINTZ', 'MULTIPOINTM', 'MULTIPOINTZM'
  • 'MULTILINESTRING', 'MULTILINESTRINGZ', 'MULTILINESTRINGM', 'MULTILINESTRINGZM'
  • 'MULTIPOLYGON', 'MULTIPOLYGONZ', 'MULTIPOLYGONM', 'MULTIPOLYGONZM'
  • 'GEOMETRYCOLLECTION', 'GEOMETRYCOLLECTIONZ', 'GEOMETRYCOLLECTIONZM', 'GEOMETRYCOLLECTIONZM'
  • 'GEOMETRY', 'GEOMETRYZ', 'GEOMETRYM', 'GEOMETRYZM'
dimension has to be one of the followings:
  • 'XY' or 2: 2D points, identified by X and Y coordinates
  • 'XYM': 2D points, identified by X and Y coordinates. Each point stores an M-value (measure) as well
  • 'XYZ' or 3: 3D points, identified by X, Y and Z coordinates
  • 'XYZM' or 4: 3D points, identified by X, Y and Z coordinates. Each point stores an M-value (measure) as well
Please note: the dimension argument is optional; when specified, if specified, iy's expected to be consistent with the declared geom_type
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
the optional 6th arg [not_null] is a non-standard extension required by the peculiar SQLite arch:
  • if set to 0 [false], then the Geometry column will accept NULL values as well. This is the default behaviour
  • if set to any <> 0 value [true], then the Geometry will be defined using a NOT NULL clause
AddTemporaryGeometryColumn AddTemporaryGeometryColumn( db-prefix String , table String , column String , srid Integer , geom_type String [ , dimension String [ , not_null Integer ] ] ) : Integer base Almost the same as AddGeometryColumn(), with a critical difference:
  • db-prefix is the schema-name of some attached database
  • Such an Attached Database must necessarily be of the :memory: type being previously created by issuing an SQL statement like:
    ATTACH DATABASE ':memory:' AS prefix;
  • If the above prerequisite is not fullfilled this function will always fail.
RecoverGeometryColumn RecoverGeometryColumn( table String , column String , srid Integer , geom_type String [ , dimension Integer ] ) : Integer base Validates an existing ordinary column in order to possibly transform it in a real geometry column, thus updating the Spatial Metadata tables and creating any required trigger in order to enforce constraints
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
DiscardGeometryColumn DiscardGeometryColumn( table String , column String ) : Integer base Removes a geometry column from Spatial MetaData tables and drops any related trigger
the column itself still continues to exist untouched as an ordinary, unconstrained column
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
RegisterVirtualGeometry RegisterVirtualGeometry( table String ) : Integer base Registers a VirtualShape or VirtualGeoJSON table into the Spatial MetaData tables; the VirtualShape table should be previously created by invoking CREATE VIRTUAL TABLE ... USING VirtualShape(...)
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
DropVirtualGeometry DropVirtualGeometry( table String ) : Integer base Removes a VirtualShape or VirtualGeoJSONtable from the Spatial MetaData tables, dropping the VirtualTable table as well.
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
CreateSpatialIndex CreateSpatialIndex( table String , column String ) : Integer base Builds an RTree Spatial Index on a geometry column, creating any required trigger required in order to enforce full data coherency between the main table and Spatial Index
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
-1 will be returned if any physical column named "rowid" (caseless) shadowing the real ROWID is detected.
CreateTemporarySpatialIndex CreateTemporarySpatialIndex( db-prefix String , table String , column String ) : Integer base Almost the same as CreateSpatialIndex(), but specifically intended to support Geometry columns created by AddTemporaryGeometryColumn()
  • db-prefix is the schema-name of some attached database
  • Such an Attached Database must necessarily be of the :memory: type being previously created by issuing an SQL statement like:
    ATTACH DATABASE ':memory:' AS prefix;
  • table and column must identify an already existing Temporary Geometry located on that Database.
  • If the above prerequisites are not fullfilled this function will always fail.
CreateMbrCache CreateMbrCache( table String , column String ) : Integer base Builds an MbrCache on a geometry column, creating any required trigger required in order to enforce full data coherency between the main table and the MbrCache
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
DisableSpatialIndex DisableSpatialIndex( table String , column String ) : Integer base Disables an RTree Spatial Index or MbrCache, removing any related trigger
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
CheckShadowedRowid CheckShadowedRowid( table String ) : Integer base Checks if some table has a physical column named "rowid" (caseless) shadowing the real ROWID.
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
NULL will be returned if the requested table doesn't exist.
CheckWithoutRowid CheckWithoutRowid( table String ) : Integer base Checks if some table was created by specifying a WITHOUT ROWID clause.
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
NULL will be returned if the requested table doesn't exist.
CheckSpatialIndex CheckSpatialIndex( void ) : Integer
CheckSpatialIndex( table String , column String ) : Integer		 base Checks an RTree Spatial Index for validity and consistency
  • if no arguments are passed, then any RTree defined into geometry_columns will be checked
  • otherwise only the RTree corresponding to table and column will be checked
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
NULL will be returned if the requested RTree doesn't exist
-1 will be returned if any physical column named "rowid" (caseless) shadowing the real ROWID is detected.
RecoverSpatialIndex RecoverSpatialIndex( [ no_check : Integer ] ) : Integer
RecoverSpatialIndex( table String , column String [ , no_check : Integer ] ) : Integer		 base Recovers a (possibly broken) RTree Spatial Index
  • if no arguments are passed, then any RTree defined into geometry_columns will be recovered
  • otherwise only the RTree corresponding to table and column will be recovered
  • the optional argument no_check will be interpreted as follows:
    • if no_check = FALSE (default) the RTree will be checked first: and only an invalid RTree will be then actually rebuilt
    • if no_check = TRUE the RTree will be unconditionally rebuilt from scratch
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
NULL will be returned if the requested RTree doesn't exist
-1 will be returned if any physical column named "rowid" (caseless) shadowing the real ROWID is detected.
GetSpatialIndexExtent GetSpatialIndexExtent( db_prefix String , table String , column String ) : Geometry base Retrieves the Full Extent from an RTree Spatial Index supporting a SpatialTable/SpatialView or a GeoPackage-Geometry.
  • The db_prefix argument specifies which one of the ATTACHED databases is expected to contain the RTree; if NULL then the MAIN database will be assumed by default.
  • The table and column arguments specifying which Table and Geometry is intended.
returns a Rectangle (Polygon Geometry). The Polygon is defined by the corner points of the RTree's Full Extent [(MINX, MINY),(MAXX, MINY), (MAXX, MAXY), (MINX, MAXY), (MINX, MINY)].
NULL will be returned on invalid arguments, or on a not existing Table/Geometry, or if no coresponding Spatial Index exists.
Plase note: retrieving the Full Extenxt from an RTree is a very quick operation even when performed on a Table/Geometry containing many million rows.
Also: The returned Geometry will be a Spatialite-Binary, even when a GeoPackage-Table/Geometry has been requested.
InvalidateLayerStatistics InvalidateLayerStatistics( [ void ) : Integer
InvalidateLayerStatistics( table String [ , column String ] ) : Integer		 base Immediately and unconditionally invalidates the internal Layer Statistics
  • if no arguments are passed, then internal statistics will be invalidated for any possible Geometry Column defined in the current DB
  • otherwise statistics will be updated only for Geometry Columns corresponding to the given table
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
Please note: will effectively work only on behalf DB-files supporting the more recent metatables layout introduced starting since version 4.x; in any other case will always return an error and no action will happen.
UpdateLayerStatistics UpdateLayerStatistics( void ) : Integer
UpdateLayerStatistics( table String [ , column String ] ) : Integer		 base Updates the internal Layer Statistics [Feature Count and Total Extent]
  • if no arguments are passed, then internal statistics will be updated for any possible Geometry Column defined in the current DB
  • otherwise statistics will be updated only for Geometry Columns corresponding to the given table
  • When called from inside a GeoPackage Database, the gpkg_contents entry will be updated with the extent information.
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
GetLayerExtent GetLayerExtent( table String [ , column String [ , mode Boolean] ] ) : Geometry base Return the Envelope corresponding to the Total Extent (bounding box] of some Layer; if the Table/Layer only contains a single Geometry column passing the column name isn't strictly required.
The returned extent will be retrieved from the Statistics tables:
  • if the third argument mode is set to TRUE a PESSIMISTIC strategy will be applied, i.e. an attempt will be made in order to update the Statistics tables before returning the Envelope.
  • otherwise the returned Envelope will simply reflect the current values stored into the Statics tables as they are (OPTIMISTIC strategy, adopted by default).
NULL will be returned if any error occurs or if the required table isn't a Layer.
CreateRasterCoveragesTable CreateRasterCoveragesTable( void ) : Integer base Creates the raster_coverages table required by RasterLite-2
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure)
ReCreateRasterCoveragesTriggers ReCreateRasterCoveragesTriggers( void ) : Integer base (Re)Creates all Triggers supporting the raster_coverages table required by RasterLite-2
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure)
CreateVectorCoveragesTables CreateVectorCoveragesTables( void ) : Integer base Creates the vector_coverages and vector_coverages_srid tables required by RasterLite-2
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure)
ReCreateVectorCoveragesTriggers ReCreateVectorCoveragesTriggers( void ) : Integer base (Re)Creates all Triggers supporting the vector_coverages table required by RasterLite-2
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure)
RebuildGeometryTriggers RebuildGeometryTriggers( table_name String , geometry_column_name String ) : integer base This function will reinstall all geometry-related Triggers for the named table.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure)
UpgradeGeometryTriggers UpgradeGeometryTriggers( transaction Integer ) : integer base This function will upgrade all geometry-related Triggers to the latest version (all DB tables declaring at least one Geometry will be affected by the upgrade).
If the transaction argument is set to TRUE then the whole upgrade will be safely executed within an internally defined SQL transaction.
Please note: DB-files created using obsolete versions of SpatiaLite (< 4.0.0) will not be upgraded.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure)

SQL functions supporting the MetaCatalog and related Statistics

Function Syntax OGC
defined		 required
module		 Summary
CreateMetaCatalogTables CreateMetaCatalogTables( transaction Integer ) : Integer base Creates both splite_metacatalog and splite_metacatalog_statistics tables; splite_metacatalog will be populated so to describe every table/column currently defined within the DB.
if the first argument transaction is set to TRUE the whole operation will be handled as a single Transaction (faster): the default setting is transaction=FALSE (slower, but safer).
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
UpdateMetaCatalogStatistics UpdateMetaCatalogStatistics( transaction Integer , table_name String , column_name String ) : Integer
UpdateMetaCatalogStatistics( transaction Integer , master_table String , table_name String , column_name String ) : Integer		 base Updates the splite_metacatalog_statistics table by computing the statistic summary for the required table/column.
if the first argument transaction is set to TRUE the whole operation will be handled as a single Transaction (faster): the default setting is transaction=FALSE (slower, but safer).
the first form (using three arguments) will simply attempt to update the statistic summary for a single table/column as identified by their names: a matching row is expected to be found in splite_metacatalog.
the second form (using four arguments) allows to update the statistic summary for many table/columns in a single pass.
in this case master_table should identify an existing table: table_name and column_name should identify two columns belonging to this tables.
the statistic summary for every table/columns fetched from the master table will then be updated: any mismatching table/column will be simply ignored.
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE

SQL functions supporting SLD/SE Styled Layers

Function Syntax OGC
defined		 required
module		 Summary
CreateStylingTables CreateStylingTables() : Integer
CreateStylingTables( relaxed Integer ) : Integer
CreateStylingTables( relaxed Integer , transaction Integer ) : Integer		 libxml2 Creates a set of tables supporting SLD/SE Styled Layers.
  • if the optional argument relaxed is explicitly set as TRUE then a relaxed version of the validating Triggers will be installed (not checking for strict XSD schema validation).
  • if the optional argument transaction is explicitly set as TRUE then the whole operation will be atomically confined within a monolithic SQL transaction
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
Please note: will implicitly invoke CreateRasterCoveragesTable() and CreateVectorCoveragesTables() so to create the corresponding tables if not already defined.
ReCreateStylingTriggers ReCreateStylingTriggers() : Integer
ReCreateStylingTriggers( relaxed Integer ) : Integer
ReCreateStylingTriggers( relaxed Integer , transaction Integer ) : Integer		 libxml2 (Re)Creates once again all Triggers supporting SLD/SE Styled Layers tables.
  • if the optional argument relaxed is explicitly set as TRUE then a relaxed version of the validating Triggers will be installed (not checking for strict XSD schema validation).
  • if the optional argument transaction is explicitly set as TRUE then the whole operation will be atomically confined within a monolithic SQL transaction
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
Please note: will implicitly invoke ReCreateRasterCoveragesTriggers() and ReCreateVectorCoveragesTriggers() so to update all Triggers on these tables too.
SE_RegisterVectorCoverage SE_RegisterVectorCoverage( coverage_name String , f_table_name String , f_geometry_column String ) : Integer
SE_RegisterVectorCoverage( coverage_name String , f_table_name String , f_geometry_column String , title String , abstract String ) : Integer
SE_RegisterVectorCoverage( coverage_name String , f_table_name String , f_geometry_column String , title String , abstract String , is_queryable Boolen , is_editable Boolean ) : Integer		 libxml2 Creates a Vector Coverage based on an already existing Spatial Table.
  • coverage_name is the symbolic name uniquely identifying each Vector Coverage (Primary Key).
  • f_table_name and f_geometry_column are expected to match the corresponding entries in the geometry_columns table.
  • the optional arguments title and abstract could be eventually be specified for better human readability.
  • if both optional arguments is_queryable and is_editable are left unspecified the Vector Coverage will then be neither queryable nor editable.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterSpatialViewCoverage SE_RegisterSpatialViewCoverage( coverage_name String , view_name String , view_geometry String ) : Integer
SE_RegisterSpatialViewCoverage( coverage_name String , view_name String , view_geometry String , title String , abstract String ) : Integer
SE_RegisterSpatialViewCoverage( coverage_name String , view_name String , view_geometry String , title String , abstract String , is_queryable Boolen , is_editable Boolean ) : Integer		 libxml2 Creates a Vector Coverage based on an already existing Spatial View.
  • coverage_name is the symbolic name uniquely identifying each Vector Coverage (Primary Key).
  • view_name and view_geometry are expected to match the corresponding entries in the views_geometry_columns table.
  • the optional arguments title and abstract could be eventually be specified for better human readability.
  • if both optional arguments is_queryable and is_editable are left unspecified the Vector Coverage will then be neither queryable nor editable.
  • Note: a Spatial View Coverage declared to be editable will not fully support editing operations if the underlying Spatial View is of the read-only type (read-only=1).
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterVirtualTableCoverage SE_RegisterVirtualTableCoverage( coverage_name String , virt_name String , virt_geometry String ) : Integer
SE_RegisterVirtualTableCoverage( coverage_name String , virt_name String , virt_geometry String , title String , abstract String ) : Integer
SE_RegisterVirtualTableeCoverage( coverage_name String , virt_name String , virt_geometry String , title String , abstract String , is_queryable Boolen ) : Integer		 libxml2 Creates a Vector Coverage based on an already existing Virtual Table of the VirtualShape or VirtualGeoJSON type.
  • coverage_name is the symbolic name uniquely identifying each Vector Coverage (Primary Key).
  • virt_name and virt_geometry are expected to match the corresponding entries in the virts_geometry_columns table.
  • the optional arguments title and abstract could be eventually be specified for better human readability.
  • if the optional argument is_queryable is left unspecified then the Vector Coverage will not be queryable.
  • Note: VirtualTable Coverages are never editable.
Both SE_RegisterVirtualShapeCoverage() and SE_RegisterVirtualGeoJsonCoverage() are accepted alias names for this SQL function.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterTopoGeoCoverage SE_RegisterTopoGeoCoverage( coverage_name String , topology_name String ) : Integer
SE_RegisterTopoGeoCoverage( coverage_name String , topology_name String , title String , abstract String ) : Integer
SE_RegisterTopoGeoCoverage( coverage_name String , topology_name String , title String , abstract String , is_queryable Boolen , is_editable Boolean ) : Integer		 libxml2 Creates a Vector Coverage based on an already existing Topology-Geometry.
  • coverage_name is the symbolic name uniquely identifying each Vector Coverage (Primary Key).
  • topogeo_name is expected to match the corresponding entries in the topologies table.
  • the optional arguments title and abstract could be eventually be specified for better human readability.
  • if both optional arguments is_queryable and is_editable are left unspecified the Vector Coverage will then be neither queryable nor editable.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterTopoNetCoverage SE_RegisterTopoNetCoverage( coverage_name String , network_name String ) : Integer
SE_RegisterTopoNetCoverage( coverage_name String , network_name String , title String , abstract String ) : Integer
SE_RegisterTopoNetCoverage( coverage_name String , network_name String , title String , abstract String , is_queryable Boolen , is_editable Boolean ) : Integer		 libxml2 Creates a Vector Coverage based on an already existing Topology-Network.
  • coverage_name is the symbolic name uniquely identifying each Vector Coverage (Primary Key).
  • toponet_name is expected to match the corresponding entries in the networks table.
  • the optional arguments title and abstract could be eventually be specified for better human readability.
  • if both optional arguments is_queryable and is_editable are left unspecified the Vector Coverage will then be neither queryable nor editable.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterVectorCoverage SE_UnregisterVectorCoverage( coverage_name String ) : Integer libxml2 Completely removes an already defined Vector Coverage this including any furher depency; the underlying Spatial Table will be absolutely unaffected.
  • coverage_name must identify an existing Vector Coverage.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_SetVectorCoverageInfos SE_SetVectorCoverageInfos( coverage_name String , title String , abstract String ) : Integer
SE_SetVectorCoverageInfos( coverage_name String , title String , abstract String , is_queryable Boolen , is_editable Boolean ): Integer		 libxml2 Updates the descriptive infos associated to a Vector Coverage.
  • coverage_name must identify an existing Vector Coverage.
  • title and abstract represent the descriptive infos to be set.
  • the optional arguments is_queryable and is_editable can be explicitly set so to determine if the Vector Coverage is queryable and/or editable.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_SetVectorCoverageCopyright SE_SetVectorCoverageCopyright( coverage_name String , copyright String ) : Integer
SE_SetVectorCoverageCopyright( coverage_name String , copyright String , license String ): Integer		 libxml2 Updates Copyright and License infos associated to a Vector Coverage.
  • coverage_name must identify an existing Vector Coverage.
  • copyright identifies the Copyright holder; if NULL the current value will be preserved.
  • the optional argument license must reference one the Data Licenses registered into the data_licenses table, as e.g. CC0 1.0 or CC BY 4.0.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterVectorCoverageSrid SE_RegisterVectorCoverageSrid( coverage_name String , srid Integer ) : Integer libxml2 Adds an alternative SRID to an already defined Vector Coverage.
  • coverage_name must identify an existing Vector Coverage.
  • srid is expected to match the corresponding entries in the spatial_ref_sys table.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterVectorCoverageSrid SE_UnregisterVectorCoverageSrid( coverage_name String , srid Integer ) : Integer libxml2 Removes an already defined alternative SRID from a Vector Coverage.
  • coverage_name and srid must identify some previously defined alternative SRID.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UpdateVectorCoverageExtent SE_UpdateVectorCoverageExtent() : Integer
SE_UpdateVectorCoverageExtent( transaction Integer ) : Integer
SE_UpdateVectorCoverageExtent( coverage_name String ) : Integer
SE_UpdateVectorCoverageExtent( coverage_name String , transaction Integer ) : Integer		 libxml2 Updates the Extent boundary supporting a Vector Coverage, this including any eventually defined alternative SRID.
  • if the optional coverage_name argument is set then only that single Vector Coverage will be updated; otherwise all registered Vector Coverages will be processed in a single pass (may require a long time).
  • if the optional transaction argument is set to TRUE then the whole operation will be internally handled as a single SQL Transaction.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterVectorCoverageKeyword SE_RegisterVectorCoverageeKeyword( coverage_name String , keyword String ) : Integer libxml2 Adds a keyword to an already defined Vector Coverage.
  • coverage_name must identify an existing Vector Coverage.
  • keyword must not be already defined for the same Coverage.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterVectorCoverageKeyword SE_UnregisterVectorCoverageKeyword( coverage_name String , keyword String ) : Integer libxml2 Removes an already defined keyword from a Vector Coverage.
  • coverage_name and keyword must identify some previously defined keyword.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_AutoRegisterStandardBrushes SE_AutoRegisterStandardBrushes( ) : NULL libxml2 Inserts all Graphic Standard Brushes supported by RasterLite2 (if not already inserted).
Will be automatically invoked by CreateStylingTables().
SE_RegisterExternalGraphic SE_RegisterExternalGraphic( xlink_href String , resource BLOB ) : Integer
SE_RegisterExternalGraphic( xlink_href String , resource BLOB , title String , abstract String , file_name String ) : Integer		 libxml2 Inserts (or updates) an External Graphic Resource.
  • xlink_href uniquely identifies each Resource (Primary Key).
  • resource is expected to be a BLOB containing an image/gif, image/png, image/jpeg or image/svg+xml payload.
  • the optional arguments title, abstract and file_name could be eventually be specified for better human readability.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterExternalGraphic SE_UnregisterExternalGraphic( xlink_href String ) : Integer libxml2 Deletes an already existing External Graphic Resource.
  • xlink_href the External Resource to be deleted.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterVectorStyle SE_RegisterVectorStyle( style BLOB ) : Integer libxml2 Inserts a new Vector Style definition.
  • style is expected to be an XmlBLOB containing a valid SLD/SE Style of the Vector type.
    If CreateStylingTables() was invoked without specifying the relaxed option this XmlBLOB is expected to have successfully passed a formal XML Schema Validation.
    The registered Style will be identified either by its unique Style ID or by its Style Name automatically retrieved from SLD/SE XML.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterVectorStyle SE_UnregisterVectorStyle( style_id Integer [ , remove_all Integer ] ) : Integer
SE_UnregisterVectorStyle( style_name Text [ , remove_all Integer ] ) : Integer		 libxml2 Removes an already registered Vector Style definition.
  • The Style to be removed could be referenced either by its unique Style Id or by its Style Name.
    Any attempt to remove a Style identified by its Name will be rejected if the if two (or more) Styles share the same Name thus causing ambiguity.
  • when the optional argument remove_all is explicitly set to TRUE the Style will be removed even if it's already referenced by some VectorStyledLayer and all dependings references will be removed at the same time.
    In any other case any attempt to unregister a Style already referenced by one (or more) VectorStyleLayer will be rejected.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_ReloadVectorStyle SE_ReloadVectorStyle( style_id Integer , style BLOB ) : Integer
SE_ReloadVectorStyle( style_name Text , style BLOB ) : Integer		 libxml2 Updates an already existing Vector Style definition.
  • style is expected to be an XmlBLOB containing a valid SLD/SE Style of the Vector type.
    If CreateStylingTables() was invoked without specifying the relaxed option this XmlBLOB is expected to have successfully passed a formal XML Schema Validation.
    The updated Style will continue to be identified by its current unique Style ID but the Style Name will be automatically updated accordingly to SLD/SE XML.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterVectorStyledLayer SE_RegisterVectorStyledLayer( coverage_name String , style_id Integer ) : Integer
SE_RegisterVectorStyledLayer( coverage_name String , style_name Text ) : Integer		 libxml2 Associates a Vector Style to a Vector Styled Layer.
  • coverage_name must identify an existing Vector Layer.
  • An already registered Style can be referenced either by its unique Stile ID or by its Name; anyway a reference by Name could eventually fail if the Name is ambiguous (duplicate).
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterVectorStyledLayer SE_UnregisterVectorStyledLayer( coverage_name String , style_id Integer ) : Integer
SE_UnregisterVectorStyledLayer( coverage_name String , style_name Text ) : Integer		 libxml2 Removes an association between a Vector Style and a Vector Styled Layer.
  • coverage_name must identify an existing Vector Layer.
  • An already associated Style can be referenced either by its unique Stile ID or by its Name; anyway a reference by Name could eventually fail if the Name is ambiguous (duplicate).
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterRasterStyle SE_RegisterRasterStyle( style BLOB ) : Integer libxml2 Inserts a new Raster Style definition.
  • style is expected to be an XmlBLOB containing a valid SLD/SE Style of the Raster type.
    If CreateStylingTables() was invoked without specifying the relaxed option this XmlBLOB is expected to have successfully passed a formal XML Schema Validation.
    The registered Style will be identified either by its unique Style ID or by its Style Name automatically retrieved from SLD/SE XML.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterRasterStyle SE_UnregisterRasterStyle( style_id Integer [ , remove_all Integer ] ) : Integer
SE_UnregisterRasterStyle( style_name Text [ , remove_all Integer ] ) : Integer		 libxml2 Removes an already registered Raster Style definition.
  • The Style to be removed could be referenced either by its unique Style Id or by its Style Name.
    Any attempt to remove a Style identified by its Name will be rejected if the if two (or more) Styles share the same Name thus causing ambiguity.
  • when the optional argument remove_all is explicitly set to TRUE the Style will be removed even if it's already referenced by some RasterStyledLayer and all dependings references will be removed at the same time.
    In any other case any attempt to unregister a Style already referenced by one (or more) RasterStyleLayer will be rejected.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_ReloadRasterStyle SE_ReloadRasterStyle( style_id Integer , style BLOB ) : Integer
SE_ReloadRasterStyle( style_name Text , style BLOB ) : Integer		 libxml2 Updates an already existing Raster Style definition.
  • style is expected to be an XmlBLOB containing a valid SLD/SE Style of the Raster type.
    If CreateStylingTables() was invoked without specifying the relaxed option this XmlBLOB is expected to have successfully passed a formal XML Schema Validation.
    The updated Style will continue to be identified by its current unique Style ID but the Style Name will be automatically updated accordingly to SLD/SE XML.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterRasterStyledLayer SE_RegisterRasterStyledLayer( coverage_name String , style_id Integer ) : Integer
SE_RegisterRasterStyledLayer( coverage_name String , style_name Text ) : Integer		 libxml2 Associates a Raster Style to a Raster Styled Layer.
  • coverage_name must identify an existing Raster Layer.
  • An already registered Style can be referenced either by its unique Stile ID or by its Name; anyway a reference by Name could eventually fail if the Name is ambiguous (duplicate).
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterRasterStyledLayer SE_UnregisterRasterStyledLayer( coverage_name String , style_id Integer ) : Integer
SE_UnregisterRasterStyledLayer( coverage_name String , style_name Text ) : Integer		 libxml2 Removes an association between a Raster Style and a Raster Styled Layer.
  • coverage_name must identify an existing Raster Layer.
  • An already associated Style can be referenced either by its unique Stile ID or by its Name; anyway a reference by Name could eventually fail if the Name is ambiguous (duplicate).
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterRasterCoverageSrid SE_RegisterRasterCoverageSrid( coverage_name String , srid Integer ) : Integer libxml2 Adds an alternative SRID to an already defined Raster Coverage.
  • coverage_name must identify an existing Raster Coverage.
  • srid is expected to match the corresponding entries in the spatial_ref_sys table.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterRasterCoverageSrid SE_UnregisterRasterCoverageSrid( coverage_name String , srid Integer ) : Integer libxml2 Removes an already defined alternative SRID from a Raster Coverage.
  • coverage_name and srid must identify some previously defined alternative SRID.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UpdateRasterCoverageExtent SE_UpdateRasterCoverageExtent() : Integer
SE_UpdateRasterCoverageExtent( transaction Integer ) : Integer
SE_UpdateRasterCoverageExtent( coverage_name String ) : Integer
SE_UpdateRasterCoverageExtent( coverage_name String , transaction Integer ) : Integer		 libxml2 Updates the Extent boundary supporting a Raster Coverage, this including any eventually defined alternative SRID.
  • if the optional coverage_name argument is set then only that single Raster Coverage will be updated; otherwise all registered Raster Coverages will be processed in a single pass (may require a long time).
  • if the optional transaction argument is set to TRUE then the whole operation will be internally handled as a single SQL Transaction.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterRasterCoverageKeyword SE_RegisterRasterCoverageKeyword( coverage_name String , keyword String ) : Integer libxml2 Adds a keyword to an already defined Raster Coverage.
  • coverage_name must identify an existing Raster Coverage.
  • keyword must not be already defined for the same Coverage.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterRasterCoverageKeyword SE_UnregisterRasterCoverageKeyword( coverage_name String , keyword String ) : Integer libxml2 Removes an already defined keyword from a Raster Coverage.
  • coverage_name and keyword must identify some previously defined keyword.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
RL2_RegisterMapConfiguration RL2_RegisterMapConfiguration( config BLOB ) : Integer libxml2 Inserts a new RL2 Map Configuration definition.
  • config is expected to be an XmlBLOB containing a valid RL2 Map Configuration.
    If CreateStylingTables() was invoked without specifying the relaxed option this XmlBLOB is expected to have successfully passed a formal XML Schema Validation.
    The registered RL2 Map Configuration will be identified either by its unique Config ID or by its Config Name automatically retrieved from XML.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
RL2_UnregisterMapConfiguration RL2_UnregisterMapConfiguration( config_id Integer ) : Integer
RL2_UnregisterMapConfiguration( config_name Text ) : Integer		 libxml2 Removes an already registered RL2 Map Configuration definition.
  • The Map Configuration to be removed could be referenced either by its unique Config Id or by its Config Name.
    Any attempt to remove a Map Configuration identified by its Name will be rejected if the if two (or more) Configurations share the same Name thus causing ambiguity.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
RL2_ReloadMapConfiguration RL2_ReloadMapConfiguration( config_id Integer , config BLOB ) : Integer
RL2_ReloadMapConfiguration( config_name Text , config BLOB ) : Integer		 libxml2 Updates an already existing RL2 Map Configuration definition.
  • config is expected to be an XmlBLOB containing a valid RL2 Map Configuration.
    If CreateStylingTables() was invoked without specifying the relaxed option this XmlBLOB is expected to have successfully passed a formal XML Schema Validation.
    The updated Map Configuration will continue to be identified by its current unique Config ID but the Config Name will be automatically updated accordingly to XML.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.

SQL functions supporting ISO Metadata

Function Syntax OGC
defined		 required
module		 Summary
CreateIsoMetadataTables CreateIsoMetadataTables() : Integer
CreateIsoMetadataTables( relaxed Integer ) : Integer		 libxml2 Creates a set of tables supporting ISO Metadata.
  • if the optional argument relaxed is specified (any value), then a relaxed version of the validating Triggers will be installed.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
RegisterIsoMetadata RegisterIsoMetadata( scope String , metadata BLOB ) : Integer
RegisterIsoMetadata( scope String , metadata BLOB , id Integer ) : Integer
RegisterIsoMetadata( scope String , metadata BLOB , fileIdentifier String ) : Integer		 libxml2 Inserts (or updates) an ISO Metadata definition.
  • scope can be one of undefined, fieldSession, collectionSession, series, dataset, featureType, feature, attributeType, attribute, tile, model, catalogue, schema, taxonomy, software, service, collectionHardware, nonGeographicDataset, dimensionGroup.
  • metadata is expected to be a valid XmlBLOB storing some IsoMetadata payload.
  • the first form (two arguments only) always performs an INSERT; if one the optional arguments id or fileIdentifier an UPDATE could be eventually performed if a corresponding metadata row is already defined.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
GetIsoMetadataId GetIsoMetadataId( fileIdentifier String ) : Integer libxml2 Return the unique id corresponding to the ISO Metadata definition identified by fileIdentifier.
If no corresponding ISO Metadata definition exists, this function will always return ZERO; -1 will be returned for invalid arguments.

SQL functions implementing FDO/OGR compatibility

Function Syntax OGC
defined		 required
module		 Summary
CheckSpatialMetaData CheckSpatialMetaData( void ) : Integer
CheckSpatialMetaData( db_prefix String ) : Integer		 base Checks the Spatial Metadata type, then returning:
  • -1 - on invalid args or if no ATTACHED-DB identified by db_prefix exists.
  • 0 - if the geometry_columns or spatial_ref_sys table does not exists, or if their actual layout doesn't corresponds to any known implementation
  • 1 - if both tables exist, and their layout is the one previously used by SpatiaLite legacy (older versions including 3.1.0 any earlier)
  • 2 - if both tables exist, and their layout is the one used by FDO/OGR
  • 3 - if both tables exist, and their layout is the one currently used by SpatiaLite (4.0.0 or any subsequent version)
  • 4 - if the DB layout is the one defined by the OGC GPKG specification (GeoPackage)
The optional db_prefix argument specifies which one of the ATTACHED databases is intended to be checked; if unspecified or NULL then the MAIN database will be assumed by default.
AutoFDOStart AutoFDOStart( void ) : Integer
AutoFDOStart( db_prefix String ) : Integer		 base This function will inspect the Spatial Metadata, then automatically creating/refreshing a VirtualFDO wrapper for each FDO/OGR geometry table
the return type is Integer [how many VirtualFDO tables have been created]
The optional db_prefix argument specifies which one of the ATTACHED databases is intended to be targeted; if unspecified or NULL then the MAIN database will be assumed by default.
AutoFDOStop AutoFDOStop( void ) : Integer
AutoFDOStop( db_prefix String ) : Integer		 base This function will inspect the Spatial Metadata, then automatically destroying any VirtualFDO wrapper found
the return type is Integer [how many VirtualFDO tables have been destroyed]
the return type is Integer [how many VirtualFDO tables have been created]
The optional db_prefix argument specifies which one of the ATTACHED databases is intended to be targeted; if unspecified or NULL then the MAIN database will be assumed by default.
InitFDOSpatialMetaData InitFDOSpatialMetaData( void ) : Integer base Creates the geometry_columns and spatial_ref_sys metadata tables
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
Please note: Spatial Metadata created using this function will have the FDO/OGR layout, and not the standard SpatiaLite layout
AddFDOGeometryColumn AddFDOGeometryColumn( table String , column String , srid Integer , geom_type Integer , dimension Integer, geometry_format String ) : Integer base Creates a new geometry column updating the FDO/OGR Spatial Metadata tables
geom_type has to be one of the followings:
  • 1 POINT
  • 2 LINESTRING
  • 3 POLYGON
  • 4 MULTIPOINT
  • 5 MULTILINESTRING
  • 6 MULTIPOLYGON
  • 7 GEOMETRYCOLLECTION
dimension may be 2, 3 or 4, accordingly to OGR/FDO specs
geometry_format has to be one of the followings:
  • 'WKB'
  • 'WKT'
  • 'FGF'
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
RecoverFDOGeometryColumn RecoverFDOGeometryColumn( table String , column String , srid Integer , geom_type String , dimension Integer, geometry_format String ) : Integer base Validates an existing ordinary column in order to possibly transform it in a real geometry column, thus updating the FDO/OGR Spatial Metadata tables
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
DiscardFDOGeometryColumn DiscardFDOGeometryColumn( table String , column String ) : Integer base Removes a geometry column from FDO/OGR Spatial MetaData tables
the column itself still continues to exist untouched as an ordinary column
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE

SQL functions implementing OGC GeoPackage compatibility

Function Syntax OGC
defined		 required
module		 Summary
CheckGeoPackageMetaData CheckGeoPackageMetaData( void ) : Integer
CheckGeoPackageMetaData ( db_prefix String ) : Integer		 base This function will inspect the DB layout checking if it corresponds to the GPKG own style.
The optional db_prefix argument specifies which one of the ATTACHED databases is intended to be checked; if unspecified or NULL then the MAIN database will be assumed by default.
the return type is Integer, with a return value of 1 for TRUE, 0 for FALSE and -1 on invalid args or if no ATTACHED-DB identified by db_prefix exists.
AutoGPKGStart AutoGPKGStart( void ) : Integer
AutoGPKGStart( db_prefix String ) : Integer		 GeoPackage This function will inspect the DB layout, then automatically creating/refreshing a VirtualGPKG wrapper for each GPKG geometry table
the return type is Integer [how many VirtualGPKG tables have been created]
The optional db_prefix argument specifies which one of the ATTACHED databases is intended to be targeted; if unspecified or NULL then the MAIN database will be assumed by default.
AutoGPKGStop AutoGPKGStop( void ) : Integer
AutoGPKGStop( db_prefix String ) : Integer		 GeoPackage This function will inspect the DB layout, then automatically destroying any VirtualGPKG wrapper found
the return type is Integer [how many VirtualGPKG tables have been destroyed]
The optional db_prefix argument specifies which one of the ATTACHED databases is intended to be targeted; if unspecified or NULL then the MAIN database will be assumed by default.
gpkgCreateBaseTables gpkgCreateBaseTables( void ) : void GeoPackage This function will create base tables for an "empty" GeoPackage
returns nothing on success, raises exception on error
gpkgInsertEpsgSRID gpkgInsertEpsgSRID( srid Integer ) : void GeoPackage This function will add a spatial reference system entry for the specified EPSG identifier; it is an error to try to add the entry if it already exists
returns nothing on success, raises exception on error
gpkgCreateTilesTable gpkgCreateTilesTable( tile_table_name String , srid Integer , min_x Double precision , min_y Double precision , max_x Double precision , max_y Double precision ) : void GeoPackage This function will create a new (empty) Tiles table and the triggers for that table; It also adds in the matching entries into gpkg_contents and gpkg_tile_matrix_set.
This function assumes usual tile conventions, including that the tiles are power-of-two-zoom.
returns nothing on success, raises exception on error
gpkgCreateTilesZoomLevel gpkgCreateTilesZoomLevel( tile_table_name String, zoom_level Integer , extent_width Double precision , extent_height Double precision ) : void GeoPackage This function will add a zoom level for the specified table.
This function assumes usual tile conventions, including that the tiles are power-of-two-zoom, 256x256 pixels, 1 tile at the top level (zoom level 0).
returns nothing on success, raises exception on error
gpkgAddTileTriggers gpkgAddTileTriggers( tile_table_name String ) : void GeoPackage This function will add Geopackage tile table triggers for the named table.
returns nothing on success, raises exception on error
gpkgGetNormalZoom gpkgGetNormalZoom( tile_table_name String , inverted_zoom_level Integer ) : Integer GeoPackage This function will return the normal integer zoom level for data stored in the specified table.
Note that this function can also be used to convert from a normal zoom level to an inverted zoom level - this conversion is symmetric.
Raises a SQL exception if inverted zoom level is outside the range of tile_matrix_metadata table normal zoom levels defined for the tile_table
gpkgGetNormalRow gpkgGetNormalRow( tile_table_name String , normal_zoom_level Integer , inverted_row_number Integer ) : Integer GeoPackage This function will return the normal integer row number for the specified table, normal zoom level and inverted row number.
Note that this function can also be used to convert from a normal row number to an inverted row number - this conversion is symmetric.
Raises a SQL exception if no zoom level row record in tile_matrix_metadata
gpkgGetImageType gpkgGetImageType( image Blob ) : String GeoPackage This function will return the image type (as a string) of the blob argument, or "unknown" if the image type is not one of the PNG, JPEG, TIFF or WebP format types that are supported in GeoPackage. The result will be one of:
  • "png" for PNG
  • "jpeg" for JPEG
  • "tiff" for TIFF
  • "x-webp" for WebP
These are the mime type for the image format (without the "image/" prefix).
This function raises exception on error (e.g. wrong argument type).
gpkgAddGeometryColumn gpkgAddGeometryColumn( table_name String, geometry_column_name String , geometry_type String , with_z Integer , with_m Integer , srs_id Integer ) : void GeoPackage Adds a geometry column to the specified table:
  • geometry_type is a normal WKT name:
    • "GEOMETRY"
    • "POINT"
    • "LINESTRING"
    • "POLYGON"
    • "MULTIPOINT"
    • "MULTILINESTRING"
    • "MULTIPOLYGON"
    • "GEOMCOLLECTION"
  • with_z is a flag (0 for no z values, 1 for mandatory z values, 2 for optional z values)
  • with_m is a flag (0 for no m values, 1 for mandatory m values, 2 for optional m values)
returns nothing on success, raises exception on error
gpkgAddGeometryTriggers gpkgAddGeometryTriggers( table_name String , geometry_column_name String ) : void GeoPackage This function will add Geopackage geometry table triggers for the named table.
returns nothing on success, raises exception on error
gpkgAddSpatialIndex gpkgAddSpatialIndex( table_name String , geometry_column_name String ) : void GeoPackage This function will add Geopackage Spatial Index support for the named table.
returns nothing on success, raises exception on error
gpkgMakePoint gpkgMakePoint (x Double precision , y Double precision ) : GPKG Blob Geometry
gpkgMakePoint (x Double precision , y Double precision , srid Integer ) : GPKG Blob Geometry		 GeoPackage This function will create a GeoPackage geometry POINT.
Raises a SQL exception on error
gpkgMakePointZ gpkgMakePointZ (x Double precision , y Double precision , z Double precision ) : GPKG Blob Geometry
gpkgMakePointZ (x Double precision , y Double precision , z Double precision , srid Integer ) : GPKG Blob Geometry		 GeoPackage This function will create a GeoPackage geometry POINT Z.
Raises a SQL exception on error
gpkgMakePointM gpkgMakePointM (x Double precision , y Double precision , m Double precision ) : GPKG Blob Geometry
gpkgMakePointM (x Double precision , y Double precision , m Double precision , srid Integer ) : GPKG Blob Geometry		 GeoPackage This function will create a GeoPackage geometry POINT M.
Raises a SQL exception on error
gpkgMakePointZM gpkgMakePointZM (x Double precision , y Double precision , z Double precision , m Double precision ) : GPKG Blob Geometry
gpkgMakePointZM (x Double precision , y Double precision , z Double precision , m Double precision , srid Integer ) : GPKG Blob Geometry		 GeoPackage This function will create a GeoPackage geometry POINT ZM.
Raises a SQL exception on error
IsValidGPB IsValidGPB( geom Blob ) : Integer GeoPackage This function will inspect a BLOB then checking if it really corresponds to a GPKG own Geometry
the return type is Integer, with a return value of 1 for TRUE, 0 for FALSE
AsGPB AsGPB( geom BLOB encoded geometry ) : GPKG Blob Geometry GeoPackage This function will convert a SpatiaLite geometry blob into a GeoPackage format geometry blob.
Will return NULL if any error is encountered
GeomFromGPB GeomFromGPB( geom GPKG Blob Geometry ) : BLOB encoded geometry GeoPackage This function will convert a GeoPackage format geometry blob into a SpatiaLite geometry blob.
Will return NULL if any error is encountered
CastAutomagic CastAutomagic( geom Blob ) : BLOB encoded geometry GeoPackage This function will indifferently accept on input:
  • a SpatiaLite own BLOB Geometry
  • a GPKG own BLOB Geometry
then returning a SpatiaLite own BLOB geometry.
Will return NULL if any error is encountered or on invalid / mismatching argument
GPKG_IsAssignable GPKG_IsAssignable( expected_type_name String , actual_type_name String ) : Integer GeoPackage This function will check if expected_type is the same or is a super-type of actual_type; this function is required by the standard implementation of GPKG Geometry validation Triggers.
the return type is Integer, with a return value of 1 for TRUE, 0 for FALSE

SQL functions for MbrCache-based queries

Function Syntax OGC
defined		 required
module		 Summary
FilterMbrWithin FilterMbrWithin( x1 Double precision , y1 Double precision , x2 Double precision , y2 Double precision ) base Retrieves from an MbrCache any entity whose MBR falls within the rectangle identified by extreme points x1 y1 and x2 y2
FilterMbrContains FilterMbrContains( x1 Double precision , y1 Double precision , x2 Double precision , y2 Double precision ) base Retrieves from an MbrCache any entity whose MBR contains the rectangle identified by extreme points x1 y1 and x2 y2
FilterMbrIntersects FilterMbrIntersects( x1 Double precision , y1 Double precision , x2 Double precision , y2 Double precision ) base Retrieves from an MbrCache any entity whose MBR intersects the rectangle identified by extreme points x1 y1 and x2 y2
BuildMbrFilter BuildMbrFilter( x1 Double precision , y1 Double precision , x2 Double precision , y2 Double precision ) base Creates an MBR identified by extreme points x1 y1 and x2 y2
This fuction is used internally by triggers related to MbrCache management, and is not intended for any other usage

SQL functions for R*Tree-based queries (Geometry Callbacks)

Function Syntax OGC
defined		 required
module		 Summary
RTreeIntersects RTreeIntersects( x1 Double precision , y1 Double precision , x2 Double precision , y2 Double precision ) geocallbacks Retrieves from an R*Tree Spatial Index any entity whose MBR intersect the rectangle identified by extreme points x1 y1 and x2 y2
RTreeWithin RTreeWithin( x1 Double precision , y1 Double precision , x2 Double precision , y2 Double precision ) geocallbacks Deprecated function
Still maintained so to avoid backward compatibility issues, but now simply is an alias-name for RTreeIntersects
RTreeContains RTreeContains( x1 Double precision , y1 Double precision , x2 Double precision , y2 Double precision ) geocallbacks Deprecated function
Still maintained so to avoid backward compatibility issues, but now simply is an alias-name for RTreeIntersects
RTreeDistWithin RTreeDistWithin( x Double precision , y Double precision , radius Double precision ) geocallbacks Retrieves from an R*Tree Spatial Index any entity whose MBR intersect the square circumscribed on the given circle (x y center, radius)

SQL functions supporting XmlBLOB

Function Syntax OGC
defined		 required
module		 Summary
XB_Create XB_Create( xmlPayload BLOB ) : XmlBLOB
XB_Create( xmlPayload BLOB , compressed Boolean ) : XmlBLOB
XB_Create( xmlPayload BLOB , compressed Boolean , schemaURI Text ) : XmlBLOB
XB_Create( xmlPayload BLOB , compressed Boolean , internalSchemaURI Boolean ) : XmlBLOB		 libxml2 Construct an XmlBLOB object starting from an XmlDocument.
  • If compressed is set to TRUE the XmlBlob object will be compressed (default setting).
  • If schemaURI is specified then the XmlDocument will be validated against the given Schema (default setting is skipping any XML validation).
  • If internalSchemaURI is set (any value), then the XmlDocument will be validated againt the Schema URI internally defined within the XmlDocument itself (if any).
NULL will be returned for not well-formed XmlDocuments, or when XML validation is required but XmlDocument fails to pass validation for any reason.
XB_GetPayload XB_GetPayload( xmlObject XmlBLOB [ , indent Integer ] ) : BLOB libxml2 Extracts a generic BLOB from an XmlBLOB object, exactly corresponding to the original XmlDocument and fully preserving the original character encoding.
If the optional argument indent is set to some positive value then the returned XmlDocument will be nicely formatted and properly indented by the required factor; ZERO will cause the whole XmlDocument to be returned as a single line. (default setting is negative indenting, i.e. not reformatting at all).
NULL will be returned for any invalid input (not a valid XmlBLOB object).
XB_GetDocument XB_GetDocument( xmlObject XmlBLOB [ , indent Integer ] ) : String libxml2 Extracts an XmlDocument from an XmlBLOB object; the returned XmlDocument will always be UTF-8 encoded (TEXT), irrespectively from the original internal encoding declaration.
If the optional argument indent is set to some positive value then the returned XmlDocument will be nicely formatted and properly indented by the required factor; ZERO will cause the whole XmlDocument to be returned as a single line. (default setting is negative indenting, i.e. not reformatting at all).
NULL will be returned for any invalid input (not a valid XmlBLOB object).
XB_SchemaValidate XB_SchemaValidate( xmlObject XmlBLOB , schemaURI Text [ , compressed Boolean ] ) : XmlBLOB
XB_SchemaValidate( xmlObject XmlBLOB , internalSchemaURI Boolean [ , compressed Boolean ] ) : XmlBLOB		 libxml2 Construct an XML validated XmlBLOB object starting from an XmlDocument.
  • If compressed is set to TRUE the XmlBlob object will be compressed (default setting).
  • If schemaURI is specified then the XmlDocument will be validated against the given Schema (default setting is skipping any XML validation).
  • If internalSchemaURI is set (any value), then the XmlDocument will be validated againt the Schema URI internally defined within the XmlDocument itself (if any).
NULL will be returned if the input XmlBLOB fails to pass validation for any reason.
XB_Compress XB_Compress( xmlObject XmlBLOB ) : XmlBLOB libxml2 A new compressed XmlBLOB object will be returned.
If the input XmlBLOB is already compressed this one is a harmless no-op.
NULL will be returned for any invalid input (not a valid XmlBLOB object).
XB_Uncompress XB_Uncompress( xmlObject XmlBLOB ) : XmlBLOB libxml2 A new uncompressed XmlBLOB object will be returned.
If the input XmlBLOB is already uncompressed this one is a harmless no-op.
NULL will be returned for any invalid input (not a valid XmlBLOB object).
XB_IsValid XB_IsValid( xmlObject XmlBLOB ) : Integer libxml2 The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL argument.
XB_IsCompressed XB_IsCompressed( xmlObject XmlBLOB ) : Integer libxml2 The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL argument.
XB_IsSchemaValidated XB_IsSchemaValidated( xmlObject XmlBLOB ) : Integer libxml2 The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL argument.
XB_IsIsoMetadata XB_IsIsoMetadata( xmlObject XmlBLOB ) : Integer libxml2 The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL argument.
XB_IsSldSeVectorStyle XB_IsSldSeVectorStyle( xmlObject XmlBLOB ) : Integer libxml2 The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL argument.
XB_IsSldSeRasterStyle XB_IsSldSeRasterStyle( xmlObject XmlBLOB ) : Integer libxml2 The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL argument.
XB_IsSldStyle XB_IsSldStyle( xmlObject XmlBLOB ) : Integer libxml2 The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL argument.
XB_IsSvg XB_IsSvg( xmlObject XmlBLOB ) : Integer libxml2 The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL argument.
XB_IsGpx XB_IsGpx( xmlObject XmlBLOB ) : Integer libxml2 The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL argument.
XB_IsMapConfig XB_IsMapConfig( xmlObject XmlBLOB ) : Integer libxml2 The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL argument.
XB_GetDocumentSize XB_GetDocumentSize( xmlObject XmlBLOB ) : Integer libxml2 Will return the size in bytes of the corresponding uncompressed XmlDocument.
NULL will be returned for any invalid input (not a valid XmlBLOB object).
XB_GetEncoding XB_GetEncoding( xmlObject XmlBLOB ) : String libxml2 Will return the character encoding internally declared by the XmlDocument corresponding to the input XmlBLOB.
NULL will be returned for any invalid input (not a valid XmlBLOB object), or when the XmlDocument doesn't explicitly declares any encoding.
XB_GetSchemaURI XB_GetSchemaURI( xmlObject XmlBLOB ) : String libxml2 Will return the Schema URI effectively used to validate an XmlBLOB.
NULL will be returned for any invalid input (not a valid XmlBLOB object), or when the XmlBLOB isn't validated.
XB_GetInternalSchemaURI XB_GetInternalSchemaURI( xmlPayload BLOB ) : String libxml2 Will return the Schema URI internally declared by the input XmlDocument (xsi:noNamespeceSchemaLocation or xsi:schemaLocation).
NULL will be returned for any invalid input (not a valid XmlBLOB object), or when the XmlDocument doesn't declares any Schema at all.
XB_GetFileId XB_GetFileId( xmlObject XmlBLOB ) : String libxml2 Will return the FileIdentifier defined within the XmlBLOB (if any).
NULL will be returned for any invalid input (not a valid XmlBLOB object), or when no FileIdentifier is defined.
Supported only on ISO Metadata XML Documents.
XB_SetFileId XB_SetFileId( xmlObject XmlBLOB , fileId String ) : XmlBLOB libxml2 Will return a new XmlBLOB by replacing the FileIdentifier value.
The input XmlBLOB is expected to be of the ISO Metadata type and must containt an already defined FileIdentifier.
NULL will be returned for any invalid input.
XB_AddFileId XB_AddFileId( xmlObject XmlBLOB , fileId String , IdNameSpacePrefix String , IdNameSpaceURI String , CsNameSpacePrefix String , CsNameSpaceURI String ) : XmlBLOB libxml2 Will return a new XmlBLOB by inserting a FileIdentifier value.
The input XmlBLOB is expected to be of the ISO Metadata type and must not containt an already defined FileIdentifier.
IdNameSpacePrefix and IdNameSpaceURI are expected to represent the namespace (if any) corresponding to the <fileIdentifier> tag (could be eventually NULL).
CsNameSpacePrefix and CsNameSpaceURI are expected to represent the namespace (if any) corresponding to the <CharacterString> tag (could be eventually NULL).
NULL will be returned for any invalid input.
XB_GetParentId XB_GetParentId( xmlObject XmlBLOB ) : String libxml2 Will return the ParentIdentifier defined within the XmlBLOB (if any).
NULL will be returned for any invalid input (not a valid XmlBLOB object), or when no ParentIdentifier is defined.
Supported only on ISO Metadata XML Documents.
XB_SetParentId XB_SetParentId( xmlObject XmlBLOB , parentId String ) : XmlBLOB libxml2 Will return a new XmlBLOB by replacing the ParentIdentifier value.
The input XmlBLOB is expected to be of the ISO Metadata type and must containt an already defined ParentIdentifier.
NULL will be returned for any invalid input.
XB_AddParentId XB_AddParentId( xmlObject XmlBLOB , parentId String , IdNameSpacePrefix String , IdNameSpaceURI String , CsNameSpacePrefix String , CsNameSpaceURI String ) : XmlBLOB libxml2 Will return a new XmlBLOB by inserting a ParentIdentifier value.
The input XmlBLOB is expected to be of the ISO Metadata type and must not containt an already defined ParentIdentifier.
IdNameSpacePrefix and IdNameSpaceURI are expected to represent the namespace (if any) corresponding to the <parentIdentifier> tag (could be eventually NULL).
CsNameSpacePrefix and CsNameSpaceURI are expected to represent the namespace (if any) corresponding to the <CharacterString> tag (could be eventually NULL).
NULL will be returned for any invalid input.
XB_GetTitle XB_GetTitle( xmlObject XmlBLOB ) : String libxml2 Will return the Title defined within the XmlBLOB (if any).
NULL will be returned for any invalid input (not a valid XmlBLOB object), or when no Title is defined.
Supported only on ISO Metadata and SLD/SE Styles.
XB_GetAbstract XB_GetAbstract( xmlObject XmlBLOB ) : String libxml2 Will return the Abstract defined within the XmlBLOB (if any).
NULL will be returned for any invalid input (not a valid XmlBLOB object), or when no Abstract is defined.
Supported only on ISO Metadata and SLD/SE Styles.
XB_GetGeometry XB_GetGeometry( xmlObject XmlBLOB ) : Geometry libxml2 Will return the Geometry (Bounding Box) defined within the XmlBLOB (if any).
NULL will be returned for any invalid input (not a valid XmlBLOB object), or when no Bounding Box is defined.
Supported only on ISO Metadata XML Documents.
XB_MLineFromGPX XB_MLineFromGPX( xmlObject XmlBLOB ) : Geometry libxml2 Will return a Geometry of the MULTILINESTRING XYZM type by parsing an XmlBLOB corresponding to a GPX document.
NULL will be returned for any invalid input (not a valid XmlBLOB object), or when a valid XmlBLOB does not contains a GPX document, or when a valid GPX does not contains any <trk> tag.
XB_GetLastParseError XB_GetLastParseError( void ) : String libxml2 Will return the most recent XML parsing error (if any).
NULL will be returned if there is no pending parsing error.
XB_GetLastValidateError XB_GetLastValidateError( void ) : String libxml2 Will return the most recent XML validating error (if any).
NULL will be returned if there is no pending validating error.
XB_IsValidXPathExpression XB_IsValidXPathExpression( expr Text ) : Integer libxml2 The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN when called with a NULL argument.
XB_GetLastXPathError XB_GetLastXPathError( void ) : String libxml2 Will return the most recent XPath error (if any).
NULL will be returned if there is no pending XPath error.
XB_CacheFlush XB_CacheFlush( void ) : Boolean libxml2 Reset the internal XML Schema cache to its initial empty state.
XB_LoadXML XB_LoadXML( filepath-or-URL String ) : BLOB libxml2 If filepath-or-URL corresponds to some valid local pathname, and the corresponding file (expected to contain a well-formed XML Document) can be actually accessed in read mode, then the whole file content will be returned as a BLOB value.
This function is even able to acces a remote XML Document identified by an URL.
Otherwise NULL will be returned.
Please note: SQLite doesn't support BLOB values bigger than SQLITE_MAX_LENGTH (usually, 1 GB).
Please note well: this SQL function opens the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.
Please see: CountUnsafeTriggers()
XB_StoreXML XB_StoreXML( XmlObject XmlBLOB , filepath String ) : Integer
XB_StoreXML( XmlObject XmlBLOB , filepath String , indent Integer ) : Integer		 libxml2 If XmlObject is of the XmlBLOB-type, and if filepath corresponds to some valid pathname (accessible in write/create mode), then the corresponding file will be created/overwritten so to contain the corresponding XML Document (fully preserving the original character encoding).
If the optional argument indent is set to some positive value then the returned XmlDocument will be nicely formatted and properly indented by the required factor; ZERO will cause the whole XmlDocument to be returned as a single line. (default setting is negative indenting, i.e. not reformatting at all).
The return type is Integer, with a return value of 1 for success, 0 for failure and -1 for invalid arguments.
Please note well: this SQL function opens the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.
Please see: CountUnsafeTriggers()

SQL functions supporting SRID inspection

Function Syntax Summary
SridIsGeographic SridIsGeographic( SRID Integer ) : Integer Will inspect the SRID definitions checking if the SRID is of the Geographic type;
will return 1 (i.e. TRUE) or 0 (i.e. FALSE).
NULL will be returned on invalid argument or if the SRID is undefined.
SridIsProjected SridIsProjected( SRID Integer ) : Integer Will inspect the SRID definitions checking if the SRID is of the Projected type;
will return 1 (i.e. TRUE) or 0 (i.e. FALSE).
NULL will be returned on invalid argument or if the SRID is undefined.
SridHasFlippedAxes SridHasFlippedAxes( SRID Integer ) : Integer Will inspect the SRID definitions checking if the SRID requires a flipped Axes configuration: i.e. Y,X instead of the most usual X,Y;
will return 1 (i.e. TRUE) or 0 (i.e. FALSE).
NULL will be returned on invalid argument or if the SRID is undefined.
SridGetSpheroid SridGetSpheroid( SRID Integer ) : Text
SridGetEllipsoid( SRID Integer ) : Text		 Will inspect the SRID definitions then returning the appropriate Spheroid name.
NULL will be returned on invalid argument or if the SRID is undefined.
SridGetPrimeMeridian SridGetPrimeMeridian( SRID Integer ) : Text Will inspect the SRID definitions then returning the appropriate Prime Meridian name.
NULL will be returned on invalid argument or if the SRID is undefined.
SridGetDatum SridGetDatum( SRID Integer ) : Text Will inspect the SRID definitions then returning the appropriate Datum name.
NULL will be returned on invalid argument or if the SRID is undefined.
SridGetUnit SridGetUnit( SRID Integer ) : Text Will inspect the SRID definitions then returning the appropriate Unit name.
NULL will be returned on invalid argument or if the SRID is undefined.
SridGetProjection SridGetProjection( SRID Integer ) : Text Will inspect the SRID definitions then returning the appropriate Projection name.
NULL will be returned on invalid argument or if the SRID is undefined.
SridGetAxis_1_Name SridGetAxis_1_Name( SRID Integer ) : Text Will inspect the SRID definitions then returning the appropriate Name for its first axis.
NULL will be returned on invalid argument or if the SRID is undefined.
SridGetAxis_1_Orientation SridGetAxis_1_Orientation( SRID Integer ) : Text Will inspect the SRID definitions then returning the appropriate Orientation for its first axis.
NULL will be returned on invalid argument or if the SRID is undefined.
SridGetAxis_2_Name SridGetAxis_2_Name( SRID Integer ) : Text Will inspect the SRID definitions then returning the appropriate Name for its second axis.
NULL will be returned on invalid argument or if the SRID is undefined.
SridGetAxis_2_Orientation SridGetAxis_2_Orientation( SRID Integer ) : Text Will inspect the SRID definitions then returning the appropriate Orientation for its second axis.
NULL will be returned on invalid argument or if the SRID is undefined.

SQL functions supporting new PROJ.6 API

Function Syntax Summary
PROJ_GetDatabasePath PROJ_GetDatabasePath( void ) : String Will return the currently set pathname leading to the private PROJ's SQLite database.
NULL will be returned if there is no private PROJ's SQLite database currently connected.
Note: this SQL function will be available only when SpatiaLite is built on PROJ.6 (or any later version).
PROJ_SetDatabasePath PROJ_SetDatabasePath( new_path String ) : String Will change the currently set pathname leading to the private PROJ's SQLite database.
NULL will be returned if the passed path is invalid, otherwise the path of the currently set private PROJ's SQLite database will be returned.
Note: this SQL function will be available only when SpatiaLite is built on PROJ.6 (or any later version).
PROJ_AsProjString PROJ_AsProjString( auth_name String , auth_srid Integer ) : String Will return the proj-string expression corresponding to a given Reference System; the definitions will be taken directly from the private PROJ's own database.
  • auth_name and auth_srid identify the requested Reference System.
    auth_name can be NULL, and in this case EPSG will be assumed.
NULL will be returned on failure.
Note: this SQL function will be available only when SpatiaLite is built on PROJ.6 (or any later version).
PROJ_AsWKT PROJ_AsWKT( auth_name String , auth_srid Integer ) : String
PROJ_AsWKT( auth_name String , auth_srid Integer , style String ) : String
PROJ_AsWKT( auth_name String , auth_srid Integer , style String , indented Boolean ) : String
PROJ_AsWKT( auth_name String , auth_srid Integer , style String , indented Boolean , indentation Integer ) : String		 Will return the WKT expression corresponding to a given Reference System; the definitions will be taken directly from the private PROJ's own database.
  • auth_name and auth_srid identify the requested Reference System.
    auth_name can be NULL, and in this case EPSG will be assumed.
  • the optional argument style determines which WKT format should be adopted, and must be one between GDAL, ESRI, ISO-2015 or ISO-2018 (this latter being the default setting).
  • the optional argument indented if set to TRUE will nicely format a multiline WKT expression, otherwise a single monolithic line lacking any white-space or new-line will be printed (the default is TRUE).
  • the optional argument indentation determines how many white-spaces are to be used for indenting (only meaningful if indented=TRUE; the default values is 4).
NULL will be returned on failure.
Note: this SQL function will be available only when SpatiaLite is built on PROJ.6 (or any later version).
PROJ_GuessSridFromWKT PROJ_GuessSridFromWKT( wkt String ) : Integer Will return the SRID value [if any] corresponding to a given WKT expression defining a CRS.
-1 will be returned if no CRS supported by PROJ.6 matches the WKT expression.
NULL will be returned on invalid argument.
Note: this SQL function will be available only when SpatiaLite is built on PROJ.6 (or any later version).
PROJ_GuessSridFromSHP PROJ_GuessSridFromSHP( filename String ) : Integer Will return the SRID value [if any] corresponding to the CRS defined by the .PRJ member of the Shapefile.
Note: exactley as required by ImportSHP() filename must define an absolute or relative path leading to the Shapefile (omitting any .shp, .shx, .dbf or .prj suffix).
-1 will be returned if no CRS supported by PROJ.6 matches the .PRJ member of the Shapefile.
NULL will be returned on invalid path or if no .PRJ member supports the Shapefile.
Note: this SQL function will be available only when SpatiaLite is built on PROJ.6 (or any later version).
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.
PROJ_GuessSridFromZipSHP PROJ_GuessSridFromZipSHP( zip_path String , filename String ) : Integer Will return the SRID value [if any] corresponding to the CRS defined by the .PRJ member of a zipped Shapefile.
This function is almost the same as PROJ_GuessSridFromSHP(), except in that the Shapefile is expected to be stored within some Zipfile.
The following arguments are required:
  • zip_path: expected to be the full or relative pathname of some Zipfile.
  • filename base name of the Shapefile within the Zipfile (omitting any .shp, .shx or .dbf suffix).
Note: this SQL function will be available only when SpatiaLite is built on PROJ.6 (or any later version).
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.

SQL functions supporting Topology-Geometry

Function Syntax ISO 13249-3
defined		 required
module		 Summary
GetLastTopologyException GetLastTopologyException( toponame Text ) : Text RTTOPO Will return the most recent exception raised by this Topo-Geo, or NULL if no exception is currently pending.
CreateTopoTables CreateTopoTables( ) : Integer RTTOPO Will create both topologies and networks meta-tables.
Will return 1 on success) or 0 on failure (including already existing tables).
ReCreateTopoTriggers ReCreateTopoTriggers( ) : Integer RTTOPO Will (re)create all Triggers supporting both topologies and networks meta-tables.
Will return 1 on success) or 0 on failure (including already existing tables).
InitTopoGeo ST_InitTopoGeo( toponame Text ) : Integer X RTTOPO This SQL function is explicitly required by ISO 13249-3, anyway it's simply implemented as an alias-name for CreateTopology ( toponame ).
Will return 1 on success) or 0 on failure: -1 will be returned on invalid arguments.
CreateTopology CreateTopology( toponame Text ) : Integer
CreateTopology( toponame Text , srid Integer ) : Integer
CreateTopology( toponame Text , srid Integer , has_z Boolean ) : Integer
CreateTopology( toponame Text , srid Integer , has_z Boolean , tolerance Double precision ) : Integer		 RTTOPO Will create all DBMS objects (tables, triggers, indices and alike) required in order to store a separate Topo-Geo.
  • toponame: the individual unique name of this Topo-Geo: all subordinated tables will use it as a prefix.
  • srid: the Spatial Reference System supporting this Topo-Geo (-1 by default).
  • has_z: if set to TRUE this Topo-Geo will support 3D - XYZ coordinates (FALSE by default).
  • tolerence: the standard tolerance to be applied to this Topo-Geo. (0.0 by default, i.e. no tolerance at all).
Will return 1 on success), 0 on failure, -1 on invalid args: an exception will be raised while passing a negative tolerance value.
DropTopology DropTopology( toponame Text ) : Integer RTTOPO Completely removes a Topo-Geo (and all data it contains) from the DBMS: to be invoked very cautiously and only if you are absolutely sure of what you are doing.
Will return 1 on success) or 0 on failure: -1 will be returned on invalid arguments.
AddIsoNode ST_AddIsoNode( toponame Text , face-id Integer , point Geometry ) : Integer X RTTOPO Will add a new isolated Node; face-id is expected to exactly match the ID of the Face containing point; by passing a NULL face-id the function itself will take care to identify the appropriate Face.
Will return the ID of the inserted Node on success; an exception will be raised on failure.
MoveIsoNode ST_MoveIsoNode( toponame Text , node-id Integer , point Geometry ) : Text X RTTOPO Will move an isolated Node from a point to another.
Will return a text message on success; an exception will be raised on failure.
RemIsoNode ST_RemIsoNode( toponame Text , node-id Integer ) : Text X RTTOPO Will remove an isolated Node.
Will return a text message on success; an exception will be raised on failure.
AddIsoEdge ST_AddIsoEdge( toponame Text , startnode-id Integer , endnode-id Integer , linestring Geometry ) : Integer X RTTOPO Will add a new isolated Edge connecting two isolated Nodes.
Will return the ID of the inserted Edge on success; an exception will be raised on failure.
ChangeEdgeGeom ST_ChangeEdgeGeom( toponame Text , edge-id Integer , linestring Geometry ) : Text X RTTOPO Will change the geometry of an Edge without affecting Topology relationships.
Will return a text message on success; an exception will be raised on failure.
RemIsoEdge ST_RemIsoEdge( toponame Text , edge-id Integer ) : Text X RTTOPO Will remove an isolated Edge.
Will return a text message on success; an exception will be raised on failure.
NewEdgesSplit ST_NewEdgesSplit( toponame Text , edge-id Integer , point Geometry ) : Integer X RTTOPO Will split an Edge by creating a new intermediate Node. The original Edge will be deleted and will be replaced by two new Edges.
Will return the ID of the inserted Node on success; an exception will be raised on failure.
ModEdgeSplit ST_ModEdgeSplit( toponame Text , edge-id Integer , point Geometry ) : Integer X RTTOPO Will split an Edge by creating a new intermediate Node. The original Edge will be modified and a new Edge will be inserted.
Will return the ID of the inserted Node on success; an exception will be raised on failure.
NewEdgeHeal ST_NewEdgeHeal( toponame Text , edge1-id Integer , edge2-id Integer ) : Integer X RTTOPO Will heal two Edges by deleting the Node connecting them. Both the original Edges will be deleted and will be replaced by a new Edge preserving the same orientation of the first Edge provided.
Will return the ID of the removed Node on success; an exception will be raised on failure.
ModEdgeHeal ST_ModEdgeHeal( toponame Text , edge1-id Integer , edge2-id Integer ) : Integer X RTTOPO Will heal two Edges by deleting the Node connecting them. The first Edge provided will be modified and the second deleted.
Will return the ID of the removed Node on success; an exception will be raised on failure.
AddEdgeNewFaces ST_AddEdgeNewFaces( toponame Text , startnode-id Integer , endnode-id Integer , linestring Geometry ) : Integer X RTTOPO Will add a new Edge connecting two Nodes. If this new Edge splits a Face the original Face will be deleted and replaced by two new Faces.
Will return the ID of the inserted Edge on success; an exception will be raised on failure.
AddEdgeModFace ST_AddEdgeModFace( toponame Text , startnode-id Integer , endnode-id Integer , linestring Geometry ) : Integer X RTTOPO Will add a new Edge connecting two Nodes. If this new Edge splits a Face the original Face will be modified and a new Face will be inserted.
Will return the ID of the inserted Edge on success; an exception will be raised on failure.
RemEdgeNewFace ST_RemEdgeNewFace( toponame Text , edge-id Integer ) : Integer X RTTOPO Will remove an Edge. If the removed Edge separated two Faces the original Faces will be deleted and replaced by a new Face.
Will return the ID of the inserted Face on success; an exception will be raised on failure.
RemEdgeModFace ST_RemEdgeModFace( toponame Text , edge-id Integer ) : Integer X RTTOPO Will remove an Edge. If the removed Edge separated two Faces one of then will be modified and the other deleted.
Will return the ID of the surviving Face on success; an exception will be raised on failure.
GetFaceGeometry ST_GetFaceGeometry( toponame Text , face-id Integer ) : Geometry X RTTOPO Will return the exact Geometry of a Face.
Will return a Polygon on success; an exception will be raised on failure.
GetFaceEdges ST_GetFaceEdges( toponame Text , face-id Integer ) : DB-table X RTTOPO Will update a DB-Table containing the ordered list of all Edges delimiting the given Face. The orientation will always be counterclockwise, and all Edges traversed in the opposite direction (i.e. from end to start) will be marked by a negative sign.
Will return NULL on success; an exception will be raised on failure.
ValidateTopoGeo ST_ValidateTopoGeo( toponame Text ) : DB-table X RTTOPO Will create a DB-Table containing a validation report for the given TopoGeo: if the output table is empty and no exception was raised the Topology is assumed to be fully valid, otherwise a row will be inserted into the table for each detected Topology invalidity.
If the destination table already exists it will be dropped and created yet again.
Will return NULL on success; an exception will be raised on failure.
CreateTopoGeo ST_CreateTopoGeo( toponame Text , geometry BLOB ) X RTTOPO Will populate a full Topology by importing a collection of arbitrary Geometries.
The destination Topology must already exists and must be empty; both SRID and dimensions of input Geometries must match SRID and dimensions declared by Topology.
Will return NULL on success; an exception will be raised on failure.
GetNodeByPoint GetNodeByPoint( toponame Text , point Geometry ) : Integer
GetNodeByPoint( toponame Text , point Geometry , tolerance Double precision ) : Integer		 RTTOPO Will attempt to find the ID of a Node located at Point.
  • The optional argument tolerance if omitted will assume the corresponding value declared when creating the target Topology.
Will return the ID of the Node on success; an exception will be raised on failure.
GetEdgeByPoint GetEdgeByPoint( toponame Text , point Geometry ) : Integer
GetEdgeByPoint( toponame Text , point Geometry , tolerance Double precision ) : Integer		 RTTOPO Will attempt to find the ID of an Edge intersecting the given Point.
  • The optional argument tolerance if omitted will assume the corresponding value declared when creating the target Topology.
Will return the ID of the Edge on success; an exception will be raised on failure.
GetFaceByPoint GetFaceByPoint( toponame Text , point Geometry ) : Integer
GetFaceByPoint( toponame Text , point Geometry , tolerance Double precision ) : Integer		 RTTOPO Will attempt to find the ID of a Face intersecting the given Point.
  • The optional argument tolerance if omitted will assume the corresponding value declared when creating the target Topology.
Will return the ID of the Face on success; an exception will be raised on failure.
TopoGeo_AddPoint TopoGeo_AddPoint( toponame Text , point Geometry ) : Text
TopoGeo_AddPoint( toponame Text , point Geometry , tolerance Double precision ) : Text		 RTTOPO Will attempt to add a Point (or even a MultiPoint) to an already existing Topology, possibly splitting existing Edges.
  • The optional argument tolerance if omitted will assume the corresponding value declared when creating the target Topology.
Will return a comma separated list of all IDs of corresponding Nodes on success; an exception will be raised on failure.
TopoGeo_AddLineString TopoGeo_AddLineString( toponame Text , linestring Geometry ) : Integer
TopoGeo_AddLineString( toponame Text , linestring Geometry , tolerance Double precision ) : Text		 RTTOPO Will attempt to add a Linestring (or even a MultiLinestring) to an already existing Topology, possibly splitting existing Edges/Faces.
  • The optional argument tolerance if omitted will assume the corresponding value declared when creating the target Topology.
Will return a comma separated list of all IDs of the corresponding Edges on success; an exception will be raised on failure.
TopoGeo_AddLineStringNoFace TopoGeo_AddLineStringNoFace( toponame Text , linestring Geometry ) : Integer
TopoGeo_AddLineStringNoFace( toponame Text , linestring Geometry , tolerance Double precision ) : Text		 RTTOPO Very similar to TopoGeo_AddLinestring except for a very critical difference. This function is strongly optimized for maximum speed, and will just update Nodes and Edges purposely ignoring Faces.
Consequently it will always leave the target Topology in an inconsistent state; only after executing TopoGeo_Polygonize() all Faces will be properly restored and the target Topology will finally resume a consistent state.
  • The optional argument tolerance if omitted will assume the corresponding value declared when creating the target Topology.
Will return a comma separated list of all IDs of the corresponding Edges on success; an exception will be raised on failure.
TopoGeo_TopoSnap TopoGeo_TopoSnap( toponame Text , input Geometry , iterate Integer ) : Geometry
TopoGeo_TopoSnap( toponame Text , input Geometry , tolerance_snap Double precision , tolerance_removal Double precision , iterate Integer ) : Geometry		 RTTOPO Will attempt to snap (i.e. renode) the input Geometry (any type) against the Topology identified by toponame.
  • the arguments iterate is intended to be Boolean (0 = FALSE, any other value = TRUE); when set to TRUE the input Geometry will be iteratively processed until no more snaps are found.
  • The optional argument tolerance_snap if omitted will assume the corresponding value declared when creating the target Topology.
  • The optional argument tolerance_removal if omitted (or set to NULL) will suppress the removal phase.
Will return a snapped Geometry on success; an exception will be raised on failure.
TopoGeo_SnappedGeoTable TopoGeo_SnappedGeoTable( toponame Text , db-prefix Text , table-name Text , column-name Text , output-table Text , iterate Integer ) : Integer
TopoGeo_SnappedGeoTable( toponame Text , db-prefix Text , table-name Text , column-name Text , output-table Text , tolerance_snap Double precision , tolerance_removal Double precision , iterate Integer ) : Integer		 RTTOPO Will attempt to create and populate an output-table by snapping against the Topology identified by toponame all Geometries from an input GeoTable identified by db-prefix, table-name and column-name.
  • db-prefix can be NULL, and in this case the input GeoTable is expected to be located within the "MAIN" database.
  • column-name too can be NULL, and in this case the name of the column containing Geometries will be automatically retrieved; if "db"."table" does not contains any Geometry column, or if it contains two or more Geometries an exception will be raised.
  • the input GeoTable must be properly registered and must match both SRID and dimensions declared by the target Topology.
  • this function accepts input Geometries of any class: Point, MultiPoint, Linestring, MultiLinestring, Polygon, MultiPolygon, GeometryCollection and Geometry.
  • argument iterate, tolerance_snap and tolerance_removal will be interpreted exactly in the same way adopted by TopoGeo_TopoSnap().
Will return 1 on success; an exception will be raised on failure.
TopoGeo_SubdivideLines TopoGeo_SubdivideLines( input Geometry , line_max_points Integer ) : MultiLinestring
TopoGeo_SubdivideLines( input Geometry , line_max_points Integer , line_max_length Double precision ) : MultiLinestring		 RTTOPO Will attempt to split a Linestring (or even a MultiLinestring) into a collection of shorter LineStrings fully respecting Topology consistency.
  • if argument line_max_points is set to a positive value (>= 2) then all input Linestrings will be split into many shorter lines having no more than the required number of points.
  • if argument line_max_length is set to a positive value, in units of the SRID, then a separate output line will be split every time that it becomes longer than the allowed limit.
  • either line_max_point and line_max_length can assume a NULL value, this intending ignore.
  • if both line_max_point and line_max_length are active at the same time then the first limit encountered will apply.
  • in any other case all input Linestrings will be simply copied into the output collection exactly as they are.
Will return a MultiLinestring Geometry on success; an exception will be raised on failure.
TopoGeo_FromGeoTable TopoGeo_FromGeoTable( toponame Text , db-prefix Text , table-name Text , column-name Text ) : Integer
TopoGeo_FromGeoTable( toponame Text , db-prefix Text , table-name Text , column-name Text , line_max_points Integer ) : Integer
TopoGeo_FromGeoTable( toponame Text , db-prefix Text , table-name Text , column-name Text , line_max_points Integer , line_max_length Double precision ) : Integer
TopoGeo_FromGeoTable( toponame Text , db-prefix Text , table-name Text , column-name Text , line_max_points Integer , line_max_length Double precision , tolerance Double precision ) : Integer		 RTTOPO Will attempt to import all Geometries from an input GeoTable identified by db-prefix, table-name and column-name into an existing Topology-Geometry created with CreateTopology().
  • db-prefix can be NULL. In such a case the input GeoTable is expected to be located within the "MAIN" database, otherwise in the given db-prefix database.
  • column-name too can be NULL. In such a case the name of the column containing Geometries will be automatically retrieved; if "db"."table" does not contains any Geometry column, or contains more than one, an exception will be raised.
    Otherwise the given column name in "db-prefix"."column-name" will be used if it exists.
  • the input GeoTable must be properly registered and must match both the SRID and dimensions declared in the target Topology.
  • this function accepts input Geometries of any class: Point, MultiPoint, Linestring, MultiLinestring, Polygon, MultiPolygon, GeometryCollection and Geometry.
  • The optional arguments line_max_points and line_max_length will be interpreted in the same way as used in TopoGeo_SubdivideLines() (default for both: NULL [ignore]).
  • The optional argument tolerance, when omitted, will assume the corresponding value used with CreateTopology().
  • This function will end after the first encountered Topology exception.
Will return 1 on success; an exception will be raised on failure.
TopoGeo_FromGeoTableNoFace TopoGeo_FromGeoTableNoFace( toponame Text , db-prefix Text , table-name Text , column-name Text ) : Integer
TopoGeo_FromGeoTableNoFace( toponame Text , db-prefix Text , table-name Text , column-name Text , line_max_points Integer ) : Integer
TopoGeo_FromGeoTableNoFace( toponame Text , db-prefix Text , table-name Text , column-name Text , line_max_points Integer , line_max_length Double precision ) : Integer
TopoGeo_FromGeoTableNoFace( toponame Text , db-prefix Text , table-name Text , column-name Text , line_max_points Integer , line_max_length Double precision , tolerance Double precision ) : Integer		 RTTOPO Very similar to TopoGeo_FromGeoTable except for a very critical difference. This function is strongly optimized for maximum speed, and will only update/create Nodes and Edges, without update/creation of Faces.
Consequently it will always leave the target Topology in an inconsistent state. All Faces will be properly updated/created using TopoGeo_Polygonize(), afterwich the target Topology will return to a consistent state.
Will return 1 on success; an exception will be raised on failure.
TopoGeo_FromGeoTableExt TopoGeo_FromGeoTableExt( toponame Text , db-prefix Text , table-name Text , column-name Text , dustbin-table Text , dustbin-view Text ) : Integer
TopoGeo_FromGeoTableExt( toponame Text , db-prefix Text , table-name Text , column-name Text , dustbin-table Text , dustbin-view Text , line_max_points Integer ) : Integer
TopoGeo_FromGeoTableExt( toponame Text , db-prefix Text , table-name Text , column-name Text , dustbin-table Text , dustbin-view Text , line_max_points Integer , line_max_length Double precision ) : Integer
TopoGeo_FromGeoTableExt( toponame Text , db-prefix Text , table-name Text , column-name Text , dustbin-table Text , dustbin-view Text , line_max_points Integer , line_max_length Double precision , tolerance Double precision ) : Integer		 RTTOPO Will attempt to import all Geometries from an input GeoTable identified by db-prefix, table-name and column-name into an already existing Topology-Geometry, in the same way as TopoGeo_FromGeoTable(). The main difference is how Topology exceptions are handled. For each encountered exception the corresponding Primary Keyb> will be stored into the dustbin-table.
  • dustbin-table is the name of the table intended to store PK values corresponding to features failing to be imported; this table should not already exist and will be automatically created by the function itself.
  • dustbin-view is the name of the Spatial View supporting the above table; this view should not already exist and will be automatically created by the function itself.
    Note: both the dustbin-table and the dustbin-view will be always created on the same DB containing the input GeoTable.
  • db-prefix can be NULL. In such a case the input GeoTable is expected to be located within the "MAIN" database, otherwise in the given db-prefix database.
  • column-name too can be NULL. In such a case the name of the column containing Geometries will be automatically retrieved; if "db"."table" does not contains any Geometry column, or contains more than one, an exception will be raised.
    Otherwise the given column name in "db-prefix"."column-name" will be used if it exists.
  • the input GeoTable must be properly registered and must match both the SRID and dimensions declared in the target Topology.
  • A Primary Key for the input GeoTable is mandatory.
  • dustbin-table is the name of the table intended to store the PK values corresponding to each feature that failed to be imported; this table must not exist, since it will be created by this function.
  • dustbin-view is the name of the Spatial View supporting the above table; this view should not already exist and will be automatically created by the function itself.
    Note: both the dustbin-table and the dustbin-view will be created on the same DB containing the input GeoTable. Use DropGetoTable() for both dustbin-table and dustbin-view before recalling, or even better use different names for each call.
  • this function accepts input Geometries of any class: Point, MultiPoint, Linestring, MultiLinestring, Polygon, MultiPolygon, GeometryCollection and Geometry.
  • The optional arguments line_max_points and line_max_length will be interpreted in the same way as used in TopoGeo_SubdivideLines() (default for both: NULL [ignore]).
  • The optional argument tolerance, when omitted, will assume the corresponding value used with CreateTopology().
  • This function will end after the first encountered Topology exception.
Will return 0 on full success or a positive integer corresponding to the total count of failing features referenced by the dustbin table.
An exception will be raised for any invalid argument.
TopoGeo_FromGeoTableNoFaceExt TopoGeo_FromGeoTableNoFaceExt( toponame Text , db-prefix Text , table-name Text , column-name Text , dustbin-table Text , dustbin-view Text ) : Integer
TopoGeo_FromGeoTableNoFaceExt( toponame Text , db-prefix Text , table-name Text , column-name Text , dustbin-table Text , dustbin-view Text , line_max_points Integer ) : Integer
TopoGeo_FromGeoTableNoFaceExt( toponame Text , db-prefix Text , table-name Text , column-name Text , dustbin-table Text , dustbin-view Text , line_max_points Integer , line_max_length Double precision ) : Integer
TopoGeo_FromGeoTableNoFaceExt( toponame Text , db-prefix Text , table-name Text , column-name Text , dustbin-table Text , dustbin-view Text , line_max_points Integer , line_max_length Double precision , tolerance Double precision ) : Integer		 RTTOPO Very similar to TopoGeo_FromGeoTableExt except for a very critical difference. This function is strongly optimized for maximum speed, and will only update/create Nodes and Edges, without update/creation of Faces.
Consequently it will always leave the target Topology in an inconsistent state. All Faces will be properly updated/created using TopoGeo_Polygonize(), afterwich the target Topology will return to a consistent state.
Will return 0 on full success or a positive integer corresponding to the total count of failing features referenced by the dustbin table.
An exception will be raised for any invalid argument.
TopoGeo_Polygonize TopoGeo_Polygonize( toponame Text )
TopoGeo_Polygonize( toponame Text , force_rebuild Boolean )		 RTTOPO Will remove all existing Faces from a Topology and then rebuild them from scratch.
  • If the Topology already is in a fully consistent state (i.e. all Edges are found to be assigned to their corresponding Faces) the function will immediately return whithout any further action.
  • If the Topology is in an inconsistent state (i.e. some Edges are found missing their corresponding Faces (preasumably due to previous calls of TopoGeo_AddLinesStringNoFace, TopoGeo_FromGeoTableNoFace and/or TopoGeo_FromGeoTableNoFaceExt) the function will effectively rebuild all Faces from scratch.
  • If the optional argument force_rebuild is set to TRUE (not 0) all Faces will be unconditionally rebuilt.
Caveat: for efficiency/speed reasons this function requires a substantial amount of available RAM: any attempt to process a very huge Topology on a platform supporting a very limited amount of RAM will probably fail due to insufficient memory errors.
Will return NULL on full success; an exception will be raised on failure.
TopoGeo_RemoveSmallFaces TopoGeo_RemoveSmallFaces( toponame Text , min-circularity Double precision ) : Integer
TopoGeo_RemoveSmallFaces( toponame Text , min-circularity Double precision , min-area Double precision ) : Integer		 RTTOPO Will remove from the given Topology all Faces presenting both a Circularity index smaller than min-circularity and an area smaller than min-area.
  • for a formal definition of the Circularity index please see Circularity()
  • the Circularity index ranges between 1.0 and 0.0, so setting min-circularity to a value equal or greater than 1.0 will implicitly disable the corresponding check.
  • the optional argument min-area if not specified will assume a 0.0 value thus implicitly disabling the corresponding check.
Will return 1 on full success; an exception will be raised on failure.
TopoGeo_RemoveDanglingEdges TopoGeo_RemoveDanglingEdges( toponame Text ) : Integer RTTOPO Will remove from the given Topology all dangling Edges.
Will return 1 on full success; an exception will be raised on failure.
TopoGeo_RemoveDanglingNodes TopoGeo_RemoveDanglingNodes( toponame Text ) : Integer RTTOPO Will remove from the given Topology all dangling Nodes.
Will return 1 on full success; an exception will be raised on failure.
TopoGeo_NewEdgeHeal TopoGeo_NewEdgeHeal( toponame Text ) : Integer RTTOPO Will remove from the given Topology all unnecessary Nodes.
An unnecessary Node is one connected to exactly two Edges whilst both Edges share the same two Faces; it's obvious that such a Node could be safely removed giving birth to a single Edge without altering in any way the Topological self-consistency.
All unnecessary Nodes identified by this function will be removed by implicitly calling ST_NewEdgeHeal()
Will return 1 on full success; an exception will be raised on failure.
TopoGeo_ModEdgeHeal TopoGeo_ModEdgeHeal( toponame Text ) : Integer RTTOPO Will remove from the given Topology all unnecessary Nodes.
An unnecessary Node is one connected to exactly two Edges whilst both Edges share the same two Faces; it's obvious that such a Node could be safely removed giving birth to a single Edge without altering in any way the Topological self-consistency.
All unnecessary Nodes identified by this function will be removed by implicitly calling ST_ModEdgeHeal()
Will return 1 on full success; an exception will be raised on failure.
TopoGeo_NewEdgesSplit TopoGeo_NewEdgesSplit( toponame Text , line_max_points Integer ) : Integer
TopoGeo_NewEdgesSplit( toponame Text , line_max_points Integer , line_max_length Double precision ) : Integer		 RTTOPO Will attempt to split all Edges into a collection of shorter Edges fully respecting Topology consistency.
The interpretation of line_max_points and line_max_lenght arguments is the same adopted by TopoGeo_SubdivideLines()
Edges and Nodes will be handled by implicitly calling ST_NewEdgesSplit()
Will return 1 on full success; an exception will be raised on failure.
TopoGeo_ModEdgeSplit TopoGeo_ModEdgeSplit( toponame Text , line_max_points Integer ) : Integer
TopoGeo_ModEdgeSplit( toponame Text , line_max_points Integer , line_max_length Double precision ) : Integer		 RTTOPO Will attempt to split all Edges into a collection of shorter Edges fully respecting Topology consistency.
The interpretation of line_max_points and line_max_lenght arguments is the same adopted by TopoGeo_SubdivideLines()
Edges and Nodes will be handled by implicitly calling ST_ModEdgeSplit()
Will return 1 on full success; an exception will be raised on failure.
TopoGeo_Clone TopoGeo_Clone( db-prefix Text , toponame Text , new-toponame Text ) : Integer RTTOPO Will clone an existing Topology into another; the destionation Topology shall not exist and will be automatically created.
  • db-prefix may be NULL, and in this case the input Topology will be searched within the "MAIN" database. The input Topology can be freely located within anyone of the currently attached databases.
  • the output Topology will be always created within the "MAIN" database.
Will return 1 on success; an exception will be raised on failure.
TopoGeo_GetEdgeSeed TopoGeo_GetEdgeSeed( toponame Text , edge-id Integer ) : Geometry X RTTOPO Will return a Point Geometry uniquely identifying an Edge (i.e. spatially intersecting the Edge).
Will return a Point on success; an exception will be raised on failure.
TopoGeo_GetFaceSeed TopoGeo_GetFaceSeed( toponame Text , face-id Integer ) : Geometry X RTTOPO Will return a Point Geometry uniquely identifying a Face (i.e. spatially intersecting the Face).
Will return a Point on success; an exception will be raised on failure.
TopoGeo_SnapPointToSeed TopoGeo_SnapPointToSeed( point Geometry , toponame Text , distance Double ) : Geometry X RTTOPO Will possibly return a new Point precisely snapped to the nearset TopoNode within the given distance; if no such TopoNode exists NULL will be returned.
An exception will be raised on invalid arguments.
TopoGeo_SnapLineToSeed TopoGeo_SnapLineToSeed( line Geometry , toponame Text , distance Double ) : Geometry X RTTOPO Will possibly return a new Linestring precisely snapped to the nearset Edge TopoSeed within the given distance; if no such TopoSeed exists NULL will be returned.
An exception will be raised on invalid arguments.
TopoGeo_DisambiguateSegmentEdges TopoGeo_DisambiguateSegmentEdges( toponame Text ) : Integer X RTTOPO Ensures that all Edges on a Topology-Geometry will have not less than three vertices.
  • all Edges found already definining three or more vertices will be left untouched as they are.
  • all Edges found simply being a two-points segment will be changed by interpolating a third intermediate vertex.
  • this function is harmless (doesn't substantially alters the Topology in any way) and ensures that all Seeds will certainly be not ambiguous.
Will return the total number of changed Edges on success; an exception will be raised on failure.
TopoGeo_UpdateSeeds TopoGeo_UpdateSeeds( toponame Text ) : Integer
TopoGeo_UpdateSeeds( toponame Text , incremental-mode Integer ) : Integer 		X RTTOPO Will update all persistent Edge- and Face-Seeds so to correctly represent the current state of the underlying Topology.
  • if the optional argument incremental-mode is set to TRUE an incremental update (faster) will be applied, otherwise all persistent Seeds will be rebuilt from scratch (slower).
Will return 1 on success; an exception will be raised on failure.
TopoGeo_PolyFacesList TopoGeo_PolyFacesList( toponame Text , db-prefix Text , ref-table-name Text , ref-column-name Text, out-table Text ) : Integer RTTOPO Will attempt to export into an output Table all relationships between the Faces of some Topology-Geometry and Polygons/Multipolygons found within a given Reference-GeoTable.
  • db-prefix can be NULL, and in this case the reference GeoTable is expected to be located within the "MAIN" database.
  • ref-column-name too can be NULL, and in this case the name of the column containing Geometries will be automatically retrieved; if "db"."table" does not contains any Geometry column, or if it contains two or more Geometries an exception will be raised.
  • the reference GeoTable must be properly registered and must match both SRID and dimensions declared by the target Topology.
  • this function accepts input Geometries only of the Polygon or MultiPolygon types.
  • the output Table will be always located on the "MAIN" DB and must not exists; it will be automatically created.
Will return 1 on success; an exception will be raised on failure.
Warning: if the Geometry column identified by ref-column-name is not supported by a Spatial Index this function will run in a painfully slow mode.
TopoGeo_LineEdgesList TopoGeo_LineEdgesList( toponame Text , db-prefix Text , ref-table-name Text , ref-column-name Text, out-table Text ) : Integer RTTOPO Will attempt to export into an output Table all relationships between the Edges of some Topology-Geometry and Linestrings/Multilinestrings found within a given Reference-GeoTable.
  • db-prefix can be NULL, and in this case the reference GeoTable is expected to be located within the "MAIN" database.
  • ref-column-name too can be NULL, and in this case the name of the column containing Geometries will be automatically retrieved; if "db"."table" does not contains any Geometry column, or if it contains two or more Geometries an exception will be raised.
  • the reference GeoTable must be properly registered and must match both SRID and dimensions declared by the target Topology.
  • this function accepts input Geometries only of the Linestring or MultiLinestring types.
  • the output Table will be always located on the "MAIN" DB and must not exists; it will be automatically created.
Will return 1 on success; an exception will be raised on failure.
Warning: if the Geometry column identified by ref-column-name is not supported by a Spatial Index this function will run in a painfully slow mode.
TopoGeo_ToGeoTable TopoGeo_ToGeoTable( toponame Text , db-prefix Text , ref-table-name Text , ref-column-name Text, out-table Text ) : Integer
TopoGeo_ToGeoTable( toponame Text , db-prefix Text , ref-table-name Text , ref-column-name Text, out-table Text , with-spatial-index Boolenan ) : Integer		 RTTOPO Will attempt to export into an Output GeoTable all Geometries out from a Topology-Geometry matching (via Seed-based references) a given Reference-GeoTable containing information attributes.
  • db-prefix can be NULL, and in this case the reference GeoTable is expected to be located within the "MAIN" database.
  • ref-column-name too can be NULL, and in this case the name of the column containing Geometries will be automatically retrieved; if "db"."table" does not contains any Geometry column, or if it contains two or more Geometries an exception will be raised.
  • the reference GeoTable must be properly registered and must match both SRID and dimensions declared by the target Topology.
  • this function accepts input Geometries of any class: Point, MultiPoint, Linestring, MultiLinestring, Polygon, MultiPolygon, GeometryCollection and Geometry.
  • the output GeoTable will be always located on the "MAIN" DB and must not exists; it will be automatically created with the same identical attributes of the reference GeoTable.
  • all spatial features found into the reference GeoTable will be copied into the output GeoTable. All information attributes will be preserved exactly as they are, and the feature's Geometry will be built by aggregating all Topology objects matching the reference Geometry by Seed correspondences.
    If no matching Topology objectes are found the result will be a NULL output Geometry.
  • Please note: this function will automatically invoke TopoGeo_UpdateSeeds() (incremental mode).
Will return 1 on success; an exception will be raised on failure.
TopoGeo_ToGeoTableGeneralize TopoGeo_ToGeoTableGeneralize( toponame Text , db-prefix Text , ref-table-name Text , ref-column-name Text, out-table Text , tolerance Double precision ) : Integer
TopoGeo_ToGeoTableGeneralize( toponame Text , db-prefix Text , ref-table-name Text , ref-column-name Text, out-table Text , tolerance Double precision , with-spatial-index Boolean ) : Integer		 RTTOPO Exactly the same as TopoGeo_ToGeoTable() except in that all exported geometries will be simplified / generalized still maintaining full topological consistency.
  • tolerance represents the approximation radius required by the Douglas-Peuker simplification algorithm.
Will return 1 on success; an exception will be raised on failure.
TopoGeo_CreateTopoLayer TopoGeo_CreateTopoLayer( toponame Text , db-prefix Text , ref-table-name Text , ref-column-name Text, topolayer-name Text ) : Integer
TopoGeo_CreateTopoLayer( toponame Text , db-prefix Text , ref-table-name Text , ref-column-name Text, topolayer-name Text , is-view Boolean ) : Integer		 RTTOPO Will create a fully defined new TopoLayer starting from a reference GeoTable:
  • db-prefix can be NULL, and in this case the reference GeoTable is expected to be located within the "MAIN" database.
  • ref-column-name too can be NULL, and in this case the name of the column containing Geometries will be automatically retrieved; if "db"."table" does not contains any Geometry column, or if it contains two or more Geometries an exception will be raised.
  • the reference GeoTable must be properly registered and must match both SRID and dimensions declared by the target Topology.
  • this function accepts input Geometries of any class: Point, MultiPoint, Linestring, MultiLinestring, Polygon, MultiPolygon, GeometryCollection and Geometry.
  • if the optional boolean argument is-view is set to TRUE (any other value different from zero) then a View or a Table containing a unregistered Geometry column will be accepted (FALSE by default).
    In this case ref-column must be always explicitly defined, and must contain all Geometries matching both SRID and dimensions declared by the target Topology.
Will return 1 on success; an exception will be raised on failure.
TopoGeo_InitTopoLayer TopoGeo_InitTopoLayer( toponame Text , db-prefix Text , ref-table-name Text , topolayer-name Text ) : Integer RTTOPO Will initialize a partialy defined new TopoLayer starting from a reference plain Table or View:
  • db-prefix can be NULL, and in this case the reference Table or View is expected to be located within the "MAIN" database.
Will return 1 on success; an exception will be raised on failure.
TopoGeo_RemoveTopoLayer TopoGeo_RemoveTopoLayer( toponame Text , topolayer-name Text ) : Integer RTTOPO Will completely remove an existing TopoLayer.
Will return 1 on success; an exception will be raised on failure.
TopoGeo_ExportTopoLayer TopoGeo_ExportTopoLayer( toponame Text , topolayer-name Text , out-table Text ) : Integer
TopoGeo_ExportTopoLayer( toponame Text , topolayer-name Text , out-table Text , with-spatial-index Boolean ) : Integer
TopoGeo_ExportTopoLayer( toponame Text , topolayer-name Text , out-table Text , with-spatial-index Boolean , create-only Boolean ) : Integer		 RTTOPO Will create and populate a GeoTable corresponding to a TopoLayer.
  • if the optional boolean argument with-spatial-index is set to TRUE (any other value different from zero) then a Spatial Index supporting the output GeoTable will be automatically created (FALSE by default).
  • if the optional boolean argument create-only is set to TRUE (any other value different from zero) then the output GeoTable will be created but will not be populated thus remaining completely empty (FALSE by default).
Will return 1 on success; an exception will be raised on failure.
TopoGeo_InsertFeatureFromTopoLayer TopoGeo_InsertFeatureFrom( toponame Text , topolayer-name Text , out-table Text , fid Integer ) : Integer RTTOPO Will inserting a single TopoFeature identified by is fid into a GeoTable corresponding to a TopoLayer.
  • the output GeoTable must exist and is expected to be created by a previous call to TopoGeo_ExportTopoLayer().
  • the TopoFeature identified by fid must exist and a corresponding row must not be already inserted into the output GeoTable.
Will return 1 on success; an exception will be raised on failure.

SQL functions supporting Topology-Network

Function Syntax ISO 13249-3
defined		 required
module		 Summary
GetLastNetworkException GetLastNetworkException( netname Text ) : Text RTTOPO Will return the most recent exception raised by this Topo-Net, or NULL if no exception is currently pending.
InitTopoNet ST_InitTopoNet( netname Text ) : Integer X RTTOPO This SQL function is explicitly required by ISO 13249-3, anyway it's simply implemented as an alias-name for CreateNetwork ( netname ).
Will return 1 on success) or 0 on failure: -1 will be returned on invalid arguments.
CreateNetwork CreateNetwork( netname Text ) : Integer
CreateNetwork( netname Text , spatial Boolean ) : Integer
CreateNetwork( netname Text , spatial Boolean , srid Integer ) : Integer
CreateNetwork( netname Text , spatial Boolean , srid Integer , has_z Boolean ) : Integer
CreateNetwork( netname Text , spatial Boolean , srid Integer , has_z Boolean , allow_coincident Boolean ) : Integer		 RTTOPO Will create all DBMS objects (tables, triggers, indices and alike) required in order to store a separate Topo-Net.
  • netname: the individual unique name of this Topo-Net: all subordinated tables will use it as a prefix.
  • spatial: if set to TRUE this Topo-Net will be considered a Spatial-Network, otherwise a Logical-Network will be assumed (FALSE by default).
  • srid: the Spatial Reference System supporting this Topo-Net (-1 by default).
  • has_z: if set to TRUE this Topo-Net will support 3D - XYZ coordinates (FALSE by default).
  • allow_coincident: if set to FALSE all Node-on-Node, Link-on-Node or Node-on-Link conditions will raise an exception (TRUE by default).
Will return 1 on success), 0 on failure, -1 on invalid arguments.
DropNetwork DropNetwork( netname Text ) : Integer RTTOPO Completely removes a Topo-Net (and all data it contains) from the DBMS: to be invoked very cautiously and only if you are absolutely sure of what you are doing.
Will return 1 on success) or 0 on failure: -1 will be returned on invalid arguments.
AddIsoNetNode ST_AddIsoNetNode( netname Text , point Geometry ) : Integer X RTTOPO Will add a new isolated NetNode.
Will return the ID of the inserted NetNode on success; an exception will be raised on failure.
MoveIsoNetNode ST_MoveIsoNetNode( netname Text , node-id Integer , point Geometry ) : Text X RTTOPO Will move an isolated NetNode from a point to another.
Will return a text message on success; an exception will be raised on failure.
RemIsoNetNode ST_RemIsoNetNode( netname Text , node-id Integer ) : Text X RTTOPO Will remove an isolated NetNode.
Will return a text message on success; an exception will be raised on failure.
AddLink ST_AddLink( netname Text , startnode-id Integer , endnode-id Integer , linestring Geometry ) : Integer X RTTOPO Will add a new Link connecting two NetNodes.
Will return the ID of the inserted Link on success; an exception will be raised on failure.
ChangeLinkGeom ST_ChangeLinkGeom( netname Text , link-id Integer , linestring Geometry ) : Text X RTTOPO Will change the geometry of a Link without affecting Topology relationships.
Will return a text message on success; an exception will be raised on failure.
RemoveLink ST_RemoveLink( netname Text , link-id Integer ) : Text X RTTOPO Will remove a Link.
Will return a text message on success; an exception will be raised on failure.
NewLogLinkSplit ST_NewLogLinkSplit( netname Text , link-id Integer ) : Integer X RTTOPO Will split a Link (of the Logical type) by creating a new intermediate NetNode. The original Link will be deleted and will be replaced by two new Links.
Will return the ID of the inserted Node on success; an exception will be raised on failure.
ModLogLinkSplit ST_ModLogLingSplit( netname Text , link-id Integer ) : Integer X RTTOPO Will split a Link (of the Logical type) by creating a new intermediate NetNode. The original Link will be modified and a new Link will be inserted.
Will return the ID of the inserted Node on success; an exception will be raised on failure.
NewGeoLinkSplit ST_NewGeoLinkSplit( netame Text , link-id Integer , point Geometry ) : Integer X RTTOPO Will split a Link (of the Spatial type) by creating a new intermediate NetNode. The original Link will be deleted and will be replaced by two new Links.
Will return the ID of the inserted NetNode on success; an exception will be raised on failure.
ModGeoLinkSplit ST_ModGeoLingSplit( netame Text , link-id Integer , point Geometry ) : Integer X RTTOPO Will split a Link (of the Spatial type) by creating a new intermediate NetNode. The original Link will be modified and a new Link will be inserted.
Will return the ID of the inserted NetNode on success; an exception will be raised on failure.
NewLinkHeal ST_NewLinkHeal( netname Text , link1-id Integer , link2-id Integer ) : Integer X RTTOPO Will heal two Links by deleting the NetNode connecting them. Both the original Links will be deleted and will be replaced by a new Link preserving the same orientation of the first Link provided.
Will return the ID of the removed NetNode on success; an exception will be raised on failure.
ModLinkHeal ST_ModLinkHeal( netname Text , link1-id Integer , link2-id Integer ) : Integer X RTTOPO Will heal two Links by deleting the NetNode connecting them. The first Link provided will be modified and the second deleted.
Will return the ID of the removed NetNode on success; an exception will be raised on failure.
LogiNetFromTGeo ST_LogiNetFromTGeo( netname Text , toponame Text ) : Integer X RTTOPO Will create a Logical Topology-Network from an existing Topology-Geometry.
The destination TopoNet is expected to exist and to be completely empty.
Will return 1 on success; an exception will be raised on failure.
Calling this function on behalf of some Network of the Spatial type will raise an exception.
SpatNetFromTGeo ST_SpatNetFromTGeo( netname Text , toponame Text ) : Integer X RTTOPO Will create a Spatial Topology-Network from an existing Topology-Geometry.
The destination TopoNet is expected to exist and to be completely empty.
Will return 1 on success; an exception will be raised on failure.
Calling this function on behalf of some Network of the Logical type will raise an exception; both the TopoNet and the TopoGeo are expected to declared the same identical SRID and dimensions (2D or 3D), otherwise an exception will be raised.
SpatNetFromGeom ST_SpatNetFromGeom( netname Text , geometry BLOB ) X RTTOPO Will populate a full Network by importing a collection of arbitrary Geometries.
The destination Network must already exists and must be empty; both SRID and dimensions of input Geometries must match SRID and dimensions declared by Network.
Calling this function on behalf of some Network of the Logical type will raise an exception
Will return NULL on success; an exception will be raised on failure.
ValidLogicalNet ST_ValidLogicalNet( netname Text ) : DB-table X RTTOPO Will create a DB-Table containing a validation report for the given TopoNet of the Logical type: if the output table is empty and no exception was raised the Network is assumed to be fully valid, otherwise a row will be inserted into the table for each detected Topology invalidity.
If the destination table already exists it will be dropped and created yet again.
Will return NULL on success; an exception will be raised on failure.
Calling this function on behalf of some Network of the Spatial type will raise an exception.
ValidSpatialNet ST_ValidSpatialNet( netname Text ) : DB-table X RTTOPO Will create a DB-Table containing a validation report for the given TopoNet of the Spatial type: if the output table is empty and no exception was raised the Network is assumed to be fully valid, otherwise a row will be inserted into the table for each detected Topology invalidity.
If the destination table already exists it will be dropped and created yet again.
Will return NULL on success; an exception will be raised on failure.
Calling this function on behalf of some Network of the Logical type will raise an exception.
GetNetNodeByPoint GetNetNodeByPoint( netname Text , point Geometry ) : Integer
GetNetNodeByPoint( netname Text , point Geometry , tolerance Double precision ) : Integer		 RTTOPO Will attempt to find the ID of a NetNode located at Point.
  • The optional argument tolerance if omitted will imply an exact coincidence (0.0 by default).
Will return the ID of the NetNode on success; an exception will be raised on failure.
Note: this function cannot be applied to a Logical Network
GetLinkByPoint GetLinkByPoint( netname Text , point Geometry ) : Integer
GetLinkByPoint( netname Text , point Geometry , tolerance Double precision ) : Integer		 RTTOPO Will attempt to find the ID of a Link intersecting the given Point.
  • The optional argument tolerance if omitted will imply an exact coincidence (0.0 by default).
Will return the ID of the Link on success; an exception will be raised on failure.
Note: this function cannot be applied to a Logical Network
TopoNet_FromGeoTable TopoNet_FromGeoTable( toponame Text , db-prefix Text , table-name Text , column-name Text ) : Integer RTTOPO Will attempt to import all Geometries from an input GeoTable identified by db-prefix, table-name and column-name into an already existing Topology-Network.
  • db-prefix can be NULL, and in this case the input GeoTable is expected to be located within the "MAIN" database.
  • column-name too can be NULL, and in this case the name of the column containing Geometries will be automatically retrieved; if "db"."table" does not contains any Geometry column, or if it contains two or more Geometries an exception will be raised.
  • the input GeoTable must be properly registered and must match both SRID and dimensions declared by the target Topology.
  • this function only accepts input Geometries of the Linestring or MultiLinestring classes.
Will return 1 on success; an exception will be raised on failure.
TopoNet_Clone TopoNet_Clone( netname Text , new-netname Text ) : Integer RTTOPO Will clone an existing Network into another; the destionation Network shall not exist and will be automatically created.
Will return 1 on success; an exception will be raised on failure.
TopoNet_GetLinkSeed TopoNet_GetLinkSeed( netname Text , link-id Integer ) : Geometry RTTOPO Will return a Point Geometry uniquely identifying a Link (i.e. spatially intersecting the Link).
Will return a Point on success; an exception will be raised on failure.
TopoNet_DisambiguateSegmentLinks TopoNet_DisambiguateSegmentLinks( toponame Text ) : Integer X RTTOPO Ensures that all Links on a Topology-Network will have not less than three vertices.
  • all Links found already definining three or more vertices will be left untouched as they are.
  • all Links found simply being a two-points segment will be changed by interpolating a third intermediate vertex.
  • this function is harmless (doesn't substantially alters the Network in any way) and ensures that all Seeds will certainly be not ambiguous.
Will return the total number of changed Links on success; an exception will be raised on failure.
TopoNet_UpdateSeeds TopoNet_UpdateSeeds( netname Text ) : Integer
TopoNet_UpdateSeeds( netname Text , incremental-mode Integer ) : Integer 		RTTOPOM Will update all persistent Link-Seeds so to correctly represent the current state of the underlying Network.
  • if the optional argument incremental-mode is set to TRUE an incremental update (faster) will be applied, otherwise all persistent Seeds will be rebuilt from scratch (slower).
Will return 1 on success; an exception will be raised on failure.
TopoNet_LineLinksList TopoNet_LineLinksList( netname Text , db-prefix Text , ref-table-name Text , ref-column-name Text, out-table Text ) : Integer RTTOPO Will attempt to export into an output Table all relationships between the Links of some Topology-Network and Linestrings/Multilinestrings found within a given Reference-GeoTable.
  • db-prefix can be NULL, and in this case the reference GeoTable is expected to be located within the "MAIN" database.
  • ref-column-name too can be NULL, and in this case the name of the column containing Geometries will be automatically retrieved; if "db"."table" does not contains any Geometry column, or if it contains two or more Geometries an exception will be raised.
  • the reference GeoTable must be properly registered and must match both SRID and dimensions declared by the target Network.
  • this function accepts input Geometries only of the Linestring or MultiLinestring types.
  • the output Table will be always located on the "MAIN" DB and must not exists; it will be automatically created.
Will return 1 on success; an exception will be raised on failure.
Warning: if the Geometry column identified by ref-column-name is not supported by a Spatial Index this function will run in a painfully slow mode.
TopoNet_ToGeoTable TopoNet_ToGeoTable( toponame Text , db-prefix Text , ref-table-name Text , ref-column-name Text, out-table Text ) : Integer
TopoNet_ToGeoTable( toponame Text , db-prefix Text , ref-table-name Text , ref-column-name Text, out-table Text , with-spatial-index Boolean ) : Integer		 RTTOPO Will attempt to export into an Output GeoTable all Geometries out from a Topology-Network matching (via Seed-based references) a given Reference-GeoTable containing information attributes.
  • db-prefix can be NULL, and in this case the reference GeoTable is expected to be located within the "MAIN" database.
  • ref-column-name too can be NULL, and in this case the name of the column containing Geometries will be automatically retrieved; if "db"."table" does not contains any Geometry column, or if it contains two or more Geometries an exception will be raised.
  • the reference GeoTable must be properly registered and must match both SRID and dimensions declared by the target Network.
  • this function accepts input Geometries of the following classes: Point, MultiPoint, Linestring, MultiLinestring, GeometryCollection and Geometry.
    All areal Geometries (Polygon, MultiPolygon) will be always ignored.
  • the output GeoTable will be always located on the "MAIN" DB and must not exists; it will be automatically created with the same identical attributes of the reference GeoTable.
  • all spatial features found into the reference GeoTable will be copied into the output GeoTable. All information attributes will be preserved exactly as they are, and the feature's Geometry will be built by aggregating all Topology objects matching the reference Geometry by Seed correspondences.
    If no matching Topology objectes are found the result will be a NULL output Geometry.
  • Please note: this function will automatically invoke TopoNet_UpdateSeeds() (incremental mode).
Will return 1 on success; an exception will be raised on failure.
TopoNet_ToGeoTableGeneralize TopoNet_ToGeoTableGeneralize( toponame Text , db-prefix Text , ref-table-name Text , ref-column-name Text, out-table Text , tolerance Double precision ) : Integer
TopoNet_ToGeoTableGeneralize( toponame Text , db-prefix Text , ref-table-name Text , ref-column-name Text, out-table Text , tolerance Double precision , with-spatial-index Boolean ) : Integer		 RTTOPO Exactly the same as TopoNet_ToGeoTable() except in that all exported geometries will be simplified / generalized still maintaining full topological consistency.
  • tolerance represents the approximation radius required by the Douglas-Peuker simplification algorithm.
Will return 1 on success; an exception will be raised on failure.

SQL functions supporting WMS datasources

Function Syntax Summary
WMS_CreateTables WMS_CreateTables() : Integer Creates all DB Tables required for permanently egistering WMS datasources and their configurations.
Will return 1 on success; 0 on failure.
WMS_RegisterGetCapabilities WMS_RegisterGetCapabilities( url Text ) : Integer
WMS_RegisterGetCapabilities( url Text , title Text , abstract Text ) : Integer		 Registers a WMS server.
Will return 1 on success; 0 on failure; -1 on invalid arguments.
WMS_UnRegisterGetCapabilities WMS_UnRegisterGetCapabilities( url Text ) : Integer Unregisters a WMS server (and all related children).
Will return 1 on success; 0 on failure; -1 on invalid arguments.
WMS_SetGetCapabilitiesInfos WMS_SetGetCapabilitiesInfos( url Text , title Text , abstract Text ) Sets or updates the Title and Abstract for the WMS server identified by url.
Will return 1 on success; 0 on failure; -1 on invalid arguments.
WMS_RegisterGetMap WMS_RegisterGetMap( getcapabilitites_url Text , getmap_url Text , layer_name Text , version Text, ref_sys Text , image_format Text , style Text , is_transparent Boolean , flip_axes Boolean ) : Integer
WMS_RegisterGetMap( getcapabilitites_url Text , getmap_url Text , layer_name Text , version Text, ref_sys Text , image_format Text , style Text , is_transparent Boolean , flip_axes Boolean , is_tiled Boolean , is_cached Boolean , tile_width Integer , tile_height Integer ) : Integer
WMS_RegisterGetMap( getcapabilitites_url Text , getmap_url Text , layer_name Text , title Text , abstract Text , version Text, ref_sys Text , image_format Text , style Text , is_transparent Boolean , flip_axes Boolean , is_tiled Boolean , is_cached Boolean , tile_width Integer , tile_height Integer , bgcolor Text , is_queryable Boolean , getfeatureinfo_url Text ) : Integer		 Registers a WMS layer.
  • getcapabilities_url: URL referencing the parent WMS GetCapabilities request (must be already registered).
  • getmap_url: base URL corresponding to the WMS GetMap request.
  • layer_name name of the WMS Layer.
  • title (optional arg): Title of the WMS Service.
  • abstract (optional arg): Abstract of the WMS Service.
  • version: version of the WMS protocol to be used for GetMap requests (e.g. '1.1.1' or '1.3.0').
  • ref_sys: reference system to be used for GetMap requests (e.g. 'EPSG:4326' or 'EPSG:32632').
  • image_format: MIME type of the image returned by a WMS GetMap request (e.g. 'image/png' or 'image/jpeg').
  • style: name of the style to be applied to the WMS Layer (could be 'default').
  • is_transparent: if the image returned by a WMS GetMap request should be opaque or transparent.
  • flip_axes: if a WMS GetMap request requires flipping the horizontal and vertical axes.
  • is_tiled (optional arg): if WMS GetMap requests should be handled by tiles or not.
  • is_cached (optional arg): if WMS GetMap results should be cached or not (applies only to tiled requests).
  • tile_width (optional arg): tile width expressed in pixels (applies only to tiled requests).
  • tile_height (optional arg): tile height expressed in pixels (applies only to tiled requests).
  • bgcolor (optional arg): image background color (Hex RGB color as e.g. 'ff8000' or 'A8A8A8').
  • is_queriable (optional arg): if the WMS Layer supports or not GetFeatureInfo requests.
  • getfeatureinfo_url (optional arg): base URL corresponding to a WMS GetFeatureInfo request.
Will return 1 on success; 0 on failure; -1 on invalid arguments.
WMS_UnRegisterGetMap WMS_UnRegisterGetMap( getmap_url Text , layer_name Text ) : Integer Unregisters a WMS Layer (and all related children).
Will return 1 on success; 0 on failure; -1 on invalid arguments.
WMS_SetGetMapInfos WMS_SetGetMapInfos( getmap_url Text , layer_name Text , title Text , abstract Text ) : Integer Sets or updates the Title and Abstract for the WMS Layer identified by getmap_url and layer_name.
Will return 1 on success; 0 on failure; -1 on invalid arguments.
WMS_SetGetMapCopyright WMS_SetGetMapCopyright( getmap_url Text , layer_name String , copyright String ) : Integer
WMS_SetGetMapCopyright( getmap_url Text , layer_name String , copyright String , license String ): Integer		 Updates Copyright and License infos associated to a WMS Layer.
  • getmap_url and layer_name must identify an existing WMS Layer.
  • copyright identifies the Copyright holder; if NULL the current value will be preserved.
  • the optional argument license must reference one the Data Licenses registered into the data_licenses table, as e.g. CC0 1.0 or CC BY 4.0.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
WMS_SetGetMapOptions WMS_SetGetMapOptions( getmap_url Text , layer_name Text , transparent Boolean , flip_axes Boolean )
WMS_SetGetMapOptions( getmap_url Text , layer_name Text , is_tiled Boolean , cached Boolean , tile_width Integer , tile_height Integer) : Integer
WMS_SetGetMapOptions( getmap_url Text , layer_name Text , is_queryable Boolean , getfeatureinfo_url Text ) : Integer
WMS_SetGetMapOptions( getmap_url Text , layer_name Text , bgcolor Text ) : Integer		 Sets or updates configurable options for the WMS Layer identified by getmap_url and layer_name.
Please check WMS_RegisterGetMap for more informations about supported options.
Will return 1 on success; 0 on failure; -1 on invalid arguments.
WMS_RegisterSetting WMS_RegisterSetting( getmap_url Text , layer_name Text , key Text , value Text ) : Integer
WMS_RegisterSetting( getmap_url Text , layer_name Text , key Text , value Text , is_default Boolean ) : Integer		 Registers an alternative setting for the WMS Layer identified by getmap_url and layer_name.
  • key: identifies a specific multi-value setting, and should be one between version, format or style.
  • value: an alternative setting value. Examples:
    • key = 'version' , value = ''1.0.0'
    • key = 'version' , value = ''1.1.0'
    • key = 'version' , value = ''1.1.1'
    • key = 'version' , value = ''1.3.0'
  • is_default (optional arg): if the corresponding value will become the standard setting for the corresponding WMS Layer (FALSE if undefined).
Will return 1 on success; 0 on failure; -1 on invalid arguments.
WMS_UnRegisterSetting WMS_UnRegisterSetting( getmap_url Text , layer_name Text , key Text , value Text ) : Integer Unregisters an alternative setting from the corresponding WMS Layer.
Will return 1 on success; 0 on failure; -1 on invalid arguments.
WMS_DefaultSetting WMS_DefaultSetting( getmap_url Text , layer_name Text , key Text , value Text ) : Integer Makes an alternative setting to become the standard setting for the corresponding WMS Layer.
Will return 1 on success; 0 on failure; -1 on invalid arguments.
WMS_RegisterRefSys WMS_RegisterRefSys( getmap_url Text , layer_name Text , ref_sys Text , minx Double , miny Double , maxx Double , maxy Double ) : Integer
WMS_RegisterRefSys( getmap_url Text , layer_name Text , ref_sys Text , minx Double , miny Double , maxx Double , maxy Double , is_default Boolean ) : Integer		 Registers an alternative Reference System for the WMS Layer identified by getmap_url and layer_name
  • ref_sys: name of Reference System (e.g. 'EPSG:4326' or 'EPSG:32632').
  • minx, miny, maxx and maxy: the corresponding WMS Layer's Bounding Box (aka full extent).
  • is_default (optional arg): if the corresponding SRS will become the standard Reference System for the corresponding WMS Layer (FALSE if undefined).
Will return 1 on success; 0 on failure; -1 on invalid arguments.
WMS_UnRegisterRefSys WMS_UnRegisterRefSys( getmap_url Text , layer_name Text , ref_sys Text ) : Integer Unregisters an alternative Reference System from the corresponding WMS Layer.
Will return 1 on success; 0 on failure; -1 on invalid arguments.
WMS_DefaultRefSys WMS_DefaultRefSys( getmap_url Text , layer_name Text , ref_sys Text ) : Integer Makes an alternative SRS to become the standard Reference System for the corresponding WMS Layer.
Will return 1 on success; 0 on failure; -1 on invalid arguments.
WMS_GetMapRequestURL WMS_GetMapRequestURL( getmap_url Text , layer_name Text , width Integer , height Integer , minx Double , miny Double , maxx Double , maxy Double ) : Text Creates a WMS GetMap request URL for the WMS Layer identified by getmap_url and layer_name by applying the currently set options.
  • width and height: horizontal and vertical dimensions (expressed in pixels) of the returned map image.
  • minx, miny, maxx and maxy: the Bounding Box coverred by the returned map image.
Will return a request URL on success; NULL on failure or on invalid arguments.
WMS_GetFeatureInfoRequestURL WMS_GetFeatureInfoRequestURL( getmap_url Text , layer_name Text , width Integer , height Integer , x Integer, y Integer , minx Double , miny Double , maxx Double , maxy Double ) : Text
WMS_GetFeatureInfoRequestURL( getmap_url Text , layer_name Text , width Integer , height Integer , x Integer, y Integer , minx Double , miny Double , maxx Double , maxy Double , feature_count Integer ) : Text		 Creates a WMS GetFeatureInfo request URL for the WMS Layer identified by getmap_url and layer_name by applying the currently set options.
  • width and height: horizontal and vertical dimensions (expressed in pixels) of the map image.
  • x and y: position of a Point on the map image to be queried: the map image coordidate origin (0, 0) is located on the upper left corner, the X axis increases going towards the right, the Y axis increases going towards the bottom.
  • minx, miny, maxx and maxy: the Bounding Box coverred by the map image.
  • feature_count (optional arg): maximum number of features allowed (1 if unspecified).
Will return a request URL on success; NULL on failure or on invalid arguments.

SQL functions supporting Data Licenses

Function Syntax Summary
RegisterDataLicense RegisterDataLicense( license_name Text ) : Integer
RegisterDataLicense( license_name Text , url Text ) : Integer		 Registers a Data License.
  • license_name is expected to be a text string uniquely identifying a license/version.
    Note: the following licenses are always self-defined when creating a new DB:
    • Undefined
    • Proprietary - Non Free
    • PD - Public Domain
    • CC0 1.0
    • CC BY 3.0
    • CC BY 4.0
    • CC BY-SA 3.0
    • CC BY-SA 4.0
    • CC BY-SA-NC 3.0
    • CC BY-SA-NC 4.0
  • the optional argument url may eventually contain an URL pointing to the license's legal terms and conditions.
Will return 1 on success; 0 on failure; -1 on invalid arguments.
UnRegisterDataLicense UnRegisterDataLicense( license_name Text ) : Integer Unregisters a Data License.
  • license_name is expected to match an already registered license/version.
Will return 1 on success; 0 on failure; -1 on invalid argument.
RenameDataLicense RenameDataLicense( old_name Text , new_name Text ) : Integer Renames a Data License.
  • old_name is expected to match an already registered license/version.
  • new_name must not match any already registered license/version so to respect the unique constraint.
Will return 1 on success; 0 on failure; -1 on invalid arguments.
SetDataLicenseUrl SetDataLicenseURL( license_name Text , url Text ) : Integer Sets or updates the URL corresponding to a Data License.
  • license_name is expected to match an already registered license/version.
  • url is expected to be an URL pointing to the license's legal terms and conditions.
Will return 1 on success; 0 on failure; -1 on invalid arguments.

miscellaneous advanced SQL functions

Function Syntax Summary
CloneTable CloneTable( db-prefix Text , input_table Text , output_table Text , transaction Integer ) : Integer
CloneTable( db-prefix Text , input_table Text , output_table Text , transaction Integer , option_1 Text [ , ... , option_10 Text ] ) : Integer		 Will clone (i.e. create+copy) an origin table into a destination table: the origin could be eventually located into some attached DB, but the destination is always assumed to be located in the primary DB.
  • Mandatory arguments:
    • db-prefix corresponding to the origin; the primary DB always using the main prefix.
      Can be legitimately set to NULL, and in this case MAIN will be implicitly assumed.
    • input_table name of the origin table
    • output_table name of the destination table inside the primary DB.
    • transaction a boolean values stating if the whole operation has to be atomically confined within a monolithic SQL transaction
  • Optional arguments: a maximum of 10 further options could be eventually be specified. Valid options are:
    • ::with-foreign-keys::
    • ::with-triggers::
    • ::resequence::
    • ::append::
    • ::ignore::column_name
    • ::cast2multi::geometry_column
Will return 0 (i.e. FALSE) on failure, any other value (i.e. TRUE) on success. NULL will be returned on invalid arguments.
CreateClonedTable CreateClonedTable( db-prefix Text , input_table Text , output_table Text , transaction Integer ) : Integer
CreateClonedTable( db-prefix Text , input_table Text , output_table Text , transaction Integer , option_1 Text [ , ... , option_10 Text ] ) : Integer		 A strict derivative of CloneTable() accepting the same arguments with identical meaning.
The only difference is in that this second variant will only create the output Table definition, without any data being copied.
Will return 0 (i.e. FALSE) on failure, any other value (i.e. TRUE) on success. NULL will be returned on invalid arguments.
CheckDuplicateRows CheckDuplicateRows( table Text ) : Integer Will check if the given table does contain duplicate rows, i.e. rows presenting identical values for all columns (ignoring any Primary Key column).
Will return the total number of duplicate rows found.
NULL will be returned on invalid arguments.
RemoveDuplicateRows RemoveDuplicateRows( table Text ) : Integer
RemoveDuplicateRows( table Text , transaction Boolean ) : Integer		 Will remove all duplicate rows from the given table preserving only a single occurrence.
The optional argument transaction determines if an internal SQL Transaction should be automatically started or not (the default setting if not explicitly overridden is TRUE).
Will return the total number of deleted rows.
NULL will be returned on invalid arguments.
ElementaryGeometries ElementaryGeometries( in_table Text , geom_column Text , out_table Text , out_pk Text , out_multi_id Text ) : Integer
ElementaryGeometries( in_table Text , geom_column Text , out_table Text , out_pk Text , out_multi_id Text , transaction Boolean ) : Integer
ElementaryGeometries( in_table Text , geom_column Text , out_table Text , out_pk Text , out_multi_id Text , transaction Boolean , option_1 Text [ , ... , option_10 Text ] ) : Integer		 Will create a new out_table directly corresponding to in_table. The output table will be arranged in such a way that each row will always contain an elementary Geometry; so each time that a MULTI-type Geometry is found in the input table it will be split into many distinct rows.
  • out_pk is the name to be set for the output Primary Key, and out_multi_id is the name to be set for a second column within the output table where to store the a zero-based index from which Geometry the Element-Geometry comes from, that together with the original Primary Key can be identified.
    Note: both out_pk and out_multi_id must not exist in the given in_table table.
  • The optional argument transaction determines if an internal SQL Transaction should be automatically started or not (the default setting if not explicitly overridden is TRUE).
Optional arguments: a maximum of 10 further options could be eventually be specified. Valid options are:
  • ::ignore::column_name
    Note: the geom-column will never be ignored, even if explicitly requested to be.
  • ::cast2multi::
    Will automatically apply a CastToMulti ( geom_column ) directive.
  • All references to mismatching options or not existing columns will be silently discarded.
Will return the total number of rows inserted into the output table.
NULL will be returned on invalid arguments.
DropGeoTable


Deprecated !!!
Please, use DropTable() instead.		 DropGeoTable( table Text ) : Integer
DropGeoTable( table Text , transaction Boolean ) : Integer
DropGeoTable( db-prefix Text , table Text ) : Integer
DropGeoTable( db-prefix Text , table Text , transaction Boolean ) : Integer		 Will completely remove a Geometry Table (or Spatial View) this including any eventual SpatialIndex, metadata and statistics definitions an alike.
The optional argument transaction determines if an internal SQL Transaction should be automatically started or not (the default setting if not explicitly overridden is TRUE).
Will return 0 (i.e. FALSE) on failure, any other value (i.e. TRUE) on success..
NULL will be returned on invalid arguments.
DropTable DropTable( db-prefix Text , table Text ) : Integer
DropTable( db-prefix Text , table Text , permissive Boolean ) : Integer		 Will safely remove a Geometry Table (or Spatial View), including any SpatialIndex, triggers, metadata, statistics tables.
Will also work on ordinary (non-Spatial) Tables and Views, RasterLite2 raster_coverage, GeoPackage (Vector/Raster) and OGR/FDO Geometries.
  • db-prefix can be NULL, where the Table or View to be dropped is expected to be located within the "MAIN" database.
  • The optional argument permissive determines if a retcode will be returned in the case of failure.
    The default setting, when not explicitly overridden, is FALSE, where an exception will then be raised.
Will return 1 (i.e. TRUE) on success, 0 (i.e. FALSE) on failure based on the permissive setting.
An exception will be always raised on invalid arguments and on failure, except when permissive has been explicitly set.
In this case any generic failure (as e.g. attempting to drop a non existing table) will simply return an error code without raising any exception; said in other words, enabling permissive is more or less equivalent to declare an IF EXISTS SQL clause and allows for a relaxed (non blocking) failure handling.
RenameTable RenameTable( db-prefix Text , old_name Text , new_name Text ) : Integer
RenameTable( db-prefix Text , old_name Text , new_name Text , permissive Boolean ) : Integer		 Will safely rename a Geometry Table, including any SpatialIndex, triggers, metadata, statistics tables.
Will also work on ordinary (non-Spatial) Tables, RasterLite2 raster_coverage, GeoPackage (Vector/Raster) and OGR/FDO Geometries.
  • db-prefix can be NULL, where the Table to be renamed is expected to be located within the "MAIN" database.
  • The optional argument permissive determines if a retcode will be returned in the case of failure.
    The default setting, when not explicitly overridden, is FALSE, where an exception will then be raised.
  • Will not work on Virtual Tables or Views; Spatial Tables can be renamed only if they are located in the MAIN database.
  • Any Views using a renamed Table will be adapted with the renamed Table-Name
Will return 1 (i.e. TRUE) on success.
An exception will be raised on invalid arguments or on failure, except when permissive has been explicitly set.
In this case any generic failure (as e.g. attempting to rename a non existing table) will simply return an error code without raising any exception; said in other words, enabling permissive is more or less equivalent to declare an IF EXISTS SQL clause and allows for a relaxed (non blocking) failure handling.
When used with a SQLite version < than 3.25 this function will raise an exception.
RenameColumn RenameColumn( db-prefix Text , table Text , old_colname Text , new_colname Text ) : Integer
RenameColumn( db-prefix Text , table Text , old_colname Text , new_colname Text , permissive Boolean ) : Integer		 Will safely rename a Column belonging to a Geometry Table, including any SpatialIndex, triggers, metadata, statistics tables.
Will also work on Columns of ordinary (non-Spatial) Tables, GeoPackage and OGR/FDO Geometries.
  • db-prefix can be NULL, where the Table containing the Column to be renamed is expected to be located within the "MAIN" database.
  • The optional argument permissive determines if a retcode will be returned in the case of failure.
    The default setting, when not explicitly overridden, is FALSE, where an exception will then be raised.
  • Will not work on Virtual Tables or Views; Spatial Tables can rename their Column only if they are located in the MAIN database.
  • Any Views using a renamed Column will be adapted with the renamed Column-Name
Will return 1 (i.e. TRUE) on success.
An exception will be raised on invalid arguments or on failure, except when permissive has been explicitly set.
In this case any generic failure (as e.g. attempting to rename a non existing column) will simply return an error code without raising any exception; said in other words, enabling permissive is more or less equivalent to declare an IF EXISTS SQL clause and allows for a relaxed (non blocking) failure handling.
When used with a SQLite version < than 3.25 this function will raise an exception.
ImportSHP ImportSHP( filename Text , table Text , charset Text ) : Integer
ImportSHP( filename Text , table Text , charset Text [ , srid Integer [ , geom_column Text [ , pk_column Text [ , geometry_type Text [ , coerce2D Integer [ , compressed Integer [ , spatial_index Integer [ , text_dates Integer [ , colname_case Text [ , update_statistics Integer [ , verbose Integer ] ] ] ] ] ] ] ] ] ] ] ) : Integer		 Will import an external Shapfile into an internal Table:
  • Mandatory arguments:
    • filename absolute or relative path leading to the Shapefile (omitting any .shp, .shx or .dbf suffix).
    • table name of the table to be created.
    • charset the character encoding adopted by the DBF member, as e.g. UTF-8 or CP1252
  • Optional arguments:
    • srid EPSG SRID value; -1 by default.
    • geom_column name to assigned to the Geometry column; Geometry by default.
    • pk_column name of a DBF column to be used in the Primary Key role; an INTEGER AUTOINCREMENT PK will be created by default.
    • geometry_type one between: AUTO, POINT|Z|M|ZM, LINESTRING|Z|M|ZM, POLYGON|Z|M|ZM, MULTIPOINT|Z|M|ZM, LINESTRING|Z|M|ZM, MULTIPOLYGON|Z|M|ZM; by default AUTO.
    • coerce2D boolean flag: casting to 2D or not; 0 by default.
    • compressed boolean flag; compressed geometries or not; 0 by default.
    • spatial_index boolean flag: immediately building a Spatial Index or not; 0 by default.
    • text_dates boolean flag: interpreting DBF dates as plaintext or not: 0 by default (i.e. as Julian Day).
    • colname_case one between:
      • LOWER or LOWERCASE: all column names will be converted to lowercase (default option).
      • UPPER or UPPERCASE: all column names will be converted to uppercase.
      • SAME or SAMECASE: all column names will be left as they are.
    • update_statistics boolean flag: immediately updating Layer Statistics or not; 1 by default.
    • verbose boolean flag: verbose console output: 1 by default, you can explicitly pass 0 if you better whish a silent output.
Will return the total number of imported rows.
NULL will be returned on invalid arguments.
Please note well: this SQL function opens the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.
ImportZipSHP ImportZipSHP( zip_path Text , basename Text , table Text , charset Text ) : Integer
ImportZipSHP( zip_path Text , basename Text , table Text , charset Text [ , srid Integer [ , geom_column Text [ , pk_column Text [ , geometry_type Text [ , coerce2D Integer [ , compressed Integer [ , spatial_index Integer [ , text_dates Integer [ , colname_case Text [ , update_statistics Integer [ , verbose Integer ] ] ] ] ] ] ] ] ] ] ] ) : Integer		 Will import an external Shapfile from a Zipfile into an internal Table. This function is almost the same as ImportSHP(), except in that the Shapefile is expected to be stored within some Zipfile.
The only two arguments having a different meaning are:
  • zip_path: expected to be the full or relative pathname of some Zipfile.
  • basename base name of the Shapefile within the Zipfile (omitting any .shp, .shx or .dbf suffix).
Please note well: this SQL function opens the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.
Zipfile_NumSHP Zipfile_NumSHP( zip_path Text ) : Integer return the number of Shapefiles contained within the Zipfile identified by zip_path
NULL on invalid argument or if the Zipfile does not exist or is corrupted.
Zipfile_ShpN Zipfile_ShpN( zip_path Text , idx Integer ) : Text return the basename of the nth (1-based) Shapefile contained within the Zipfile identified by zip_path
NULL on invalid arguments or if the Zipfile does not exist or is corrupted.
ExportSHP ExportSHP( table Text , geom_column Text , filename Text , charset Text ) : Integer
ExportSHP( table Text , geom_column Text , filename Text , charset Text , [ geom_type Text [ , colname_case Text ] ] ) : Integer		 Will export an internal Table as an external Shapefile:
  • table name of the table to be exported.
  • geom_column name of the Geometry column.
  • filename absolute or relative path leading to the Shapefile (omitting any .shp, .shx or .dbf suffix).
  • charset the character encoding adopted by the DBF member, as e.g. UTF-8 or CP1252
  • the optional argument geom_type is useful when exporting unregistered Geometries, and can be one between: POINT, LINESTRING, POLYGON or MULTIPOINT.
  • colname_case (optional argument) can be one between:
    • LOWER or LOWERCASE: all column names will be converted to lowercase (default option).
    • UPPER or UPPERCASE: all column names will be converted to uppercase.
    • SAME or SAMECASE: all column names will be left as they are.
Will return the total number of exported rows.
NULL will be returned on invalid arguments.
Please note well: this SQL function opens the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.
ImportDBF ImportDBF( filename Text , table Text , charset Text ) : Integer
ImportDBF( filename Text , table Text , charset Text [ , pk_column Text [ , text_dates Integer [ , colname_case Text ] ] ] ) : Integer		 Will import an external DBF file into an internal Table:
  • Mandatory arguments:
    • filename absolute or relative path leading to the DBF (including the .dbf suffix).
    • table name of the table to be created.
    • charset the character encoding adopted by the DBF, as e.g. UTF-8 or CP1252
  • Optional arguments:
    • pk_column name of a DBF column to be used in the Primary Key role; an INTEGER AUTOINCREMENT PK will be created by default.
    • text_dates boolean flag: interpreting DBF dates as plaintext or not: 0 by default (i.e. as Julian Day).
    • colname_case one between:
      • LOWER or LOWERCASE: all column names will be converted to lowercase (default option).
      • UPPER or UPPERCASE: all column names will be converted to uppercase.
      • SAME or SAMECASE: all column names will be left as they are.
Will return the total number of imported rows.
NULL will be returned on invalid arguments.
Please note well: this SQL function opens the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.
ImportZipDBF ImportZipDBF( zip_path Text , filename Text , table Text , charset Text ) : Integer
ImportZipDBF( zip_path Text , filename Text , table Text , charset Text [ , pk_column Text [ , text_dates Integer [ , colname_case Text ] ] ] ) : Integer		 Will import an external DBF file from a Zipfile into an internal Table. This function is almost the same as ImportDBF(), except in that the DBF file is expected to be stored within some Zipfile.
The only two arguments having a different meaning are:
  • zip_path: expected to be the full or relative pathname of some Zipfile.
  • filename name of the Shapefile within the Zipfile (including the .dbf suffix).
Will return the total number of imported rows.
NULL will be returned on invalid arguments.
Please note well: this SQL function opens the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.
Zipfile_NumDBF Zipfile_NumDBF( zip_path Text ) : Integer return the number of DBF files contained within the Zipfile identified by zip_path
NULL on invalid argument or if the Zipfile does not exist or is corrupted.
Zipfile_DbfN Zipfile_DbfN( zip_path Text , idx Integer ) : Text return the filename of the nth (1-based) DBF file contained within the Zipfile identified by zip_path
NULL on invalid arguments or if the Zipfile does not exist or is corrupted.
ExportDBF ExportDBF( table Text , filename Text , charset Text [ , colname_case Text ] ) : Integer Will export an internal Table as an external DBF file:
  • table name of the table to be exported.
  • filename absolute or relative path leading to the DBF (including the .dbf suffix).
  • charset the character encoding adopted by the DBF, as e.g. UTF-8 or CP1252
  • colname_case (optional argument) can be one between:
    • LOWER or LOWERCASE: all column names will be converted to lowercase (default option).
    • UPPER or UPPERCASE: all column names will be converted to uppercase.
    • SAME or SAMECASE: all column names will be left as they are.
Will return the total number of exported rows.
NULL will be returned on invalid arguments.
Please note well: this SQL function opens the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.
ExportKML ExportKML( table Text , geo_column Text , filename Text ) : Integer
ExportKML( table Text , geo_column Text , filename Text [ , precision Integer [ , name_column Text [ , description Text ] ] ] ) : Integer		 Will export an internal Table as an external KML file:
  • Mandatory aguments:
    • table name of the table to be exported.
    • geom_column name of the Geometry column.
    • filename absolute or relative path leading to the KML file.
  • Optional aguments:
    • precision number of decimal digits to be exported; 8 by default.
    • name_column name of the table's column containing KML names.
    • description_column name of the table's column containing KML descriptions.
Will return the total number of exported rows.
NULL will be returned on invalid arguments.
Please note well: this SQL function opens the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.
ExportGeoJSON


Obsolete and deprecated !!!
Please, use ExportGeoJSON2() instead.		 ExportGeoJSON( table Text , geo_column Text , filename Text ) : Integer
ExportGeoJSON( table Text , geo_column Text , filename Text [ , format Text [ , precision Integer ] ] ) : Integer		 Will export an internal Table as an external GeoJSON file:
  • Mandatory aguments:
    • table name of the table to be exported.
    • geom_column name of the Geometry column.
    • filename absolute or relative path leading to the GeoJSON file.
  • Optional aguments:
    • format specifies one of the several alternative GeoJSON formats; allowed values are none, MBR, withShortCRS, MBRwithShortCRS, withLongCRS or MBRwithLongCRS.
    • precision number of decimal digits to be exported; 8 by default.
Will return the total number of exported rows.
NULL will be returned on invalid arguments.
Please note well: this SQL function opens the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.
ExportGeoJSON2 ExportGeoJSON2( table Text , geo_column Text , filename Text ) : Integer
ExportGeoJSON2( table Text , geo_column Text , filename Text [ , precision Integer [ , lon_lat Boolen [ , M_coords Boolean [ , indented Boolean [ , colname_case Text ] ] ] ] ] ) : Integer		 Will export an internal Table as an external GeoJSON file that is conformant to the RFC 4796 specifications:
  • Mandatory aguments:
    • table name of the table to be exported.
    • geom_column name of the Geometry column.
    • filename absolute or relative path leading to the GeoJSON file.
  • Optional aguments:
    • precision number of decimal digits to be exported; 8 by default.
    • lon_lat if TRUE all coordinates are assumed to be expressed as WGS84 longitudes and latitudes (as required by RFC 7946), and ST_Transform will be automatically called if and when required.
      If FALSE all coordinates are assumed to be expressed in any other (undefined) CRS.
    • M_coords if TRUE eventual M-values will be exported as extra coordinates.
      If FALSE strict RFC 4796 conformance will be applied (no M-Values will be exported)
    • indented if TRUE the GeoJSON file will be properly indented for enhanced human readibility
      If FALSE the whole GeoJSON file will be in a single monolithic line without blank spaces.
    • colcase_name one between LOWER | LOWERCASE, UPPER | UPPERCASE or SAME | SAMECASE (same meaning as in ExportSHP).
Will return the total number of exported rows.
NULL will be returned on invalid arguments.
Please note well: this SQL function opens the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.
ImportGeoJSON ImportGeoJSON( filename Text , table Text ) : Integer
ImportGeoJSON( filename Text , table Text [ , geo_column Text [ , spatial_index Boolean [ , srid Interger [ , colname_case Text ]]]] ) : Integer		 Will create a Spatial Table by importing an external GeoJSON file conformant to the RFC 4796 specifications:
  • Mandatory aguments:
    • table name of the table to be created.
    • filename absolute or relative path leading to the GeoJSON file.
  • Optional aguments:
    • geo_column name of the Geometry column of the Spatial Table (if not specified it will be 'geometry').
    • spatial_index if TRUE a SpatialIndex supporting the Geometry Column will be immediately created.
      No SpatialIndex will be created.
    • srid when positive, the SRID value to be assigned to all Geometries.
      If 0 or negative SRID=4326 (lon-lat WGS84) will be always assumed accordingly to RFC 7946.
    • colcase_name one between LOWER | LOWERCASE, UPPER | UPPERCASE or SAME | SAMECASE (same meaning as in ImportSHP).
Will return the total number of imported rows.
NULL will be returned on invalid arguments.
Please note well: this SQL function opens the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.
ImportXLS ImportXLS( filename Text , table Text ) : Integer
ImportXLS( filename Text , table Text [ , worksheet_index Integer [ , first_line_titles Integer ] ] ) : Integer		 Will import an external spreadsheet file (Microsoft .xls format) into an internal Table:
  • Mandatory arguments:
    • filename absolute or relative path leading to the spreadsheet file.
    • table name of the table to be created.
  • Optional arguments:
    • worksheet_index a positive integer selecting the target worksheet in the case of a multi-sheet: 0 by default..
    • first_line_titles boolean flag: the first line of the worksheet contains column names or not: 0 by default.
Will return the total number of imported rows.
NULL will be returned on invalid arguments.
Please note well: this SQL function opens the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.
ImportWFS ImportWFS( filename_or_url Text , layer_name Text , table Text ) : Integer
ImportWFS( filename_or_url Text , layer_name Text , table Text [ , pk_column Text [ , swap_axes Integer [ , page_size Integer [ , spatial_index Integer ] ] ] ] ) : Integer		 Will import data from a WFS datasource:
  • Mandatory arguments:
    • filename_or_url absolute or relative path leading to the WFS file.
      Alternatively an URL corresponding to a WFS service.
    • layer_name name of the WFS layer.
    • table name of the table to be created.
  • Optional arguments:
    • pk_column name of a WFS column to be used in the Primary Key role; an INTEGER AUTOINCREMENT PK will be created by default.
    • swap_axes boolean flag: swapping the X and Y axes or not: 0 by default.
    • page_size : how many features for each WFS paged requests; a negative number or zero if you don't intend to activate WFS paging. -1 by default.
    • spatial_index boolean flag: immediately building a Spatial Index or not; 0 by default.
Will return the total number of imported rows.
NULL will be returned on invalid arguments.
Please note well: this SQL function opens the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.
ImportDXF ImportDXF( filename String ) : Integer
ImportDXF( filename String [ , srid Integer, append Integer, dimensions Text, mode Text , special_rings Text , table_prefix Text , layer_name Text ] ) : Integer		 Will import an external DXF file.
  • filename absolute or relative path leading to the DXF file.
  • srid EPSG SRID value; -1 by default.
  • append boolean flag: enabling or not append mode: 0 by default.
  • dimensions one between AUTO, 2D or 3D.
  • mode one between DISTINCT or MIXED.
  • special_rings one between NONE, LINKED or UNLINKED.
  • table_prefix: a prefix for table names; NULL if no prefix is required.
  • layer_name: name of a single DXF layer to be imported: NULL will import all layers found.
Will return 0 (i.e. FALSE) on failure, any other value (i.e. TRUE) on success.
NULL will be returned on invalid arguments.
Please note well: this SQL function opens the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.
ImportDXFfromDir ImportDXFfromDir( dir_path String ) : Integer
ImportDXFfromDir( dir_path String [ , srid Integer, append Integer, dimensions Text, mode Text , special_rings Text , table_prefix Text , layer_name Text ] ) : Integer		 Will import all DXF files found within a given Directory.
  • dir_path absolute or relative path leading to a directory containing all the *.dxf files to be imported.
  • srid EPSG SRID value; -1 by default.
  • append boolean flag: enabling or not append mode: 0 by default.
  • dimensions one between AUTO, 2D or 3D.
  • mode one between DISTINCT or MIXED.
  • special_rings one between NONE, LINKED or UNLINKED.
  • table_prefix: a prefix for table names; NULL if no prefix is required.
  • layer_name: name of a single DXF layer to be imported: NULL will import all layers found.
Will return 0 (i.e. FALSE) on failure, any other value (i.e. TRUE) on success.
NULL will be returned on invalid arguments.
Please note well: this SQL function opens the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.
ExportDXF ExportDXF( out_dir String , filename String , sql_query String , layer_col_name String , geom_col_name String , label_col_name String , text_height_col_name String , text_rotation_col_name String , geom_filter Geometry [ , precision Integer ] ) : Integer Will export a whole DXF file.
  • The output file path is controlled by out_dir and filename.
  • sql_query is a complete SQL Statement returning the dataset to be exported.
  • layer_col_name, geom_col_name, label_col_name, text_height_col_name and text_rotation_col_name must specify the corresponding column names within the resultset returned by sql_query (label_col_name, text_height_col_name and text_rotation_col_name could be eventually NULL).
  • geom_filter acts as a spatial filter selecting which entities have to be exported (could be NULL).
  • the optional argument precision specificies how many decimal digits are required for coordinate values: if not specified the default is 3.
Will return 0 (i.e. FALSE) on failure, any other value (i.e. TRUE) on success.
Please note well: this SQL function opens the door to many potential security issues, and thus is always disabled by default.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.
ST_Cutter ST_Cutter( input-db-prefix String , input-table String , input-geometry String , blade-db-prefix String , blade-table String , blade-geom String , output-table String [ , transaction Boolean [ , ram-temp-storage Boolean ] ] ) : Integer Will precisely cut in a topological consistent way a whole Input dataset using a Blade dataset (i.e. an arbitrary polygonal dataset).
All cut fragments will be stored into a further Output dataset, and all mother-child relationships will be fully preserved by saving the Primary Key values allowing to trace back Input and Blade pairs giving birth to each single fragment.
Any cut fragment stored into the Output dataset eventually falling outside any Blade will reference a conventional NULL Blade.
  • The Input dataset is always expected to declare a Primary Key and must declare a POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING or MULTIPOLYGON Geometry type.
    It can be located either in the MAIN database or in any other attached DB, and must be fully identified by the input-db-prefix, input-table and input-geometry arguments.
  • The Blade dataset is always expected to be a POLYGON or MULTIPOLYGON, with a defined Primary Key.
    It can be located either in the MAIN database or in any other attached DB, and is fully identified by the blade-db-prefix, blade-table and blade-geometry arguments.
    Note: both the Input and Blade Geometries must have the same identical SRID value.
  • The Output dataset will be always created in the MAIN database.
    The Output Geometry will have the same name and SRID as the input-geom: the Geometry type will always be one of POINT, LINESTRING or POLYGON (all cut fragments will be stored as simple features on distinct rows).
  • Both the input-db-prefix and blade-db-prefix arguments could be set as NULL, where the MAIN database will then always be assumed.
  • also the input-geom and blade-geom arguments can be set as NULL.
    In such a case the appropriate Geometry column name will be chosen when only one Geometry column is defined, otherwise an exception will be raised.
  • The optional argument transaction determines if an internal SQL Transaction should be automatically started or not (the default setting, if not explicitly overridden, is FALSE).
  • The optional argument ram-tmp-storage determines if the intermediate temporary tables internally used by this function should be created in RAM or not (the default setting if not explicitly overridden is FALSE).
Will return -1 on invalid arguments, 0 on failure, 1 on full success and 2 on partial success (i.e.when the output table contains one or more invalid geometries).
GetCutterMessage GetCutterMessage( void ) : String Will return the most recent diagnostic message emitted by ST_Cutter().
NULL will be returned if no such message currently exists.
GetVirtualTableExtent GetVirtualTableExtent( virtual_table_name String ) : Geometry virtual_table_name is expected to identify some Table of the VirtualShape or VirtualGeoJSON type.
An Envelope Geometry will be returned corresponding to the Full Extent; NULL will be returned if any error occurs (invalid argument type or non existing VirtualTable).
Both GetVirtualShapeExtent() and GetVirtualGeoJsonExtent() are accepted alias names for this SQL function.
CreateRouting CreateRouting( routing_data_table String , virtual_routing_table String , input_table String , from_column String , to_column String , geom_column String , cost_column String ) : Boolean
CreateRouting( routing_data_table String , virtual_routing_table String , input_table String , from_column String , to_column String , geom_column String , cost_column String , road_name_column String , a_star_enabled Boolean , bidirectional Boolean ) : Boolean
CreateRouting( routing_data_table String , virtual_routing_table String , input_table String , from_column String , to_column String , geom_column String , cost_column String , road_name_column String , a_star_enabled Boolean , bidirectional Boolean , oneway_from String , oneway_to String ) : Boolean
CreateRouting( routing_data_table String , virtual_routing_table String , input_table String , from_column String , to_column String , geom_column String , cost_column String , road_name_column String , a_star_enabled Boolean , bidirectional Boolean , oneway_from String , oneway_to String , overwrite Boolean ) : Boolean		 Will attempt to create a VirtualRouting Table (and the corresponding Routing Binary Data Table) starting from a topologically correct Road Network.
  • routing_data_table: name of the Routing Binary Data Table to be created.
  • virtual_routing_table: name of the VirtualRouting Table to be created.
  • input_table: name of the input Table containing the Road Network.
  • from_column: name of the input Table column containing FromNode values.
  • to_column: name of the input Table column containing ToNode values.
  • geom_column: name of the input Table column containing Geometries.
    Could be eventually NULL for Networks just describing relations but lacking any Spatial representation.
  • cost_column: name of the input Table column containing Cost values.
    Could be eventually NULL and in this case ST_Length(geom_column) will be assumed to be the Cost: in the case of Networks based on some Geographic Reference System (long/lat, as e.g. SRID=4326) a precise measure of lengths expressed in meters will be computed by using geodesic formulas.
  • road_name_column: name of the input Table column containing Road Name values. Could be eventually NULL.
  • a_star_enabled: if the A* algorithm should be enabled or not (default: 1).
  • bidirectional: if arcs/links should be considered uni- or bi-directionals (default: 0).
  • oneway_from: name of the input Table column containing OneWay flags in the From-To direction. Could be eventually NULL.
  • oneway_to: name of the input Table column containing OneWay flags in the To-From direction. Could be eventually NULL.
  • overwrite: if set to TRUE already existing Routing Binary Data and/or VirtualRouting Tables will be silently overwritten (default: 0).
1 (aka TRUE) will be returned on success, an exception will be raised on failure.
CreateRoutingNodes CreateRoutingNodes( db_prefix String , spatial_table String , geom_column String , node_from String , node_to String ) : Boolean Will attempt to add both node_from and nodes_to columns to the Spatial Table identified by db_prefix, spatial_table and geom_column. These two columns will be populated by inserting appropriate Node-IDs (of the INTEGER type) fully respecting the topological consistency of the network.
  • db_prefix could be NULL, and in this case the MAIN DB is implicitly assumed.
  • also geom_column could be NULL, and in this case the name of the Geometry column will be automatically detected if the Spatial Table has just a single Geometry.
  • geom_column must contain Geometries of the LINESTRING type.
  • both node_from and node_to columns should not exist.
1 (aka TRUE) will be returned on success, an exception will be raised on failure.
CreateRouting_GetLastError CreateRouting_GetLastError( void ) : String Will return the most recent error message emitted by CreateRouting() or CreateRoutingNodes().
NULL will be returned if no such error message currently exists.
IsLowASCII IsLowASCII( text_string String ) : Integer Inspects an UTF-8 encoded text_string testing if it only contains ASCII 7-bit characters.
The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and -1 for UNKNOWN when called with invalid arguments.
GetDbObjectScope GetDbObjectScope( db_prefix String , db_object_name String ) : String Will return a text string explaining the intended scope of any DB object (Table, View, Index or Trigger), distinguishing between system (internal/private objects required by SQLite or SpatiaLite) and userland objects.
NULL will be returned on invalid arguments or a non existing DB object.
The db_prefix argument may be eventually NULL, and in this case the MAIN DB will be assumed.
Pause Pause( void ) : NULL Will suspend the execution of the current process. (mainly intended for debugging purposes).
Note: Pause() will effectively work only if EnablePause() has been explicitly called before.
IsPauseEnabled IsPauseEnabled( void ) : Boolean Will test if Pause() is currently enabled (TRUE) or not (FALSE).
EnablePause EnablePause( void ) : NULL Will enable all subsequent calls to Pause() to be effective.
Note: by default Pause() is always kept disabled for each connection, so you necessarily have to call EnablePause() in order to effectively enable it.
DisablePause DisablePause( void ) : NULL Will make all subsequent calls to Pause() to be effectless no-ops.

SQL Procedures, Stored Procedures and Stored Variables related SQL functions

Function Syntax Summary
SqlProc_GetLastError SqlProc_GetLastError( void ) : String Will return the most recent error message returned by SQL Procedures and friends (if any).
NULL will be returned if there is no pending SQL Procedures error.
SqlProc_SetLogfile SqlProc_SetLogfile( filepath String ) : Integer
SqlProc_SetLogfile( filepath String , append Boolean ) : Integer		 Will activate a SQL Logfile supporting all following calls to SqlProc_Execute(), SqlProc_ExecuteLoop(), StoredProc_Execute() and StoredProc_ExecuteLoop().
  • The filepath argument is expected to be a valid relative or absolute pathname; it could be set to NULL so to disable SQL Logging.
  • The optional argument append if set to TRUE requires to open the Logfile for appending; if set to FALSE (default setting) the Logfile will be truncated.
1 will be returned on success; an exception will be raised on invalid argument or if the SQL Logfile can't be succesfully opened.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.
SqlProc_GetLogfile SqlProc_GetLogfile( void ) : String Will return the pathname of the currently set SQL Logfile.
NULL will be returned if no SQL Logfile is currently set.
SqlProc_FromText SqlProc_FromText( sql_body String ) : BLOB
SqlProc_FromText( sql_body String, charset_encoding String ) : BLOB		 Will return a BLOB object of the Sql Procedure type encapsulating the given SQL Body.
If the optional second argument is explicitly specified the SQL Body will be automatically converted from the given charset encoding into UTF-8.
1 will be returned on success; an exception will be raised on failure or invalid arguments.
SqlProc_FromFile SqlProc_FromFile( filepath String ) : BLOB
SqlProc_FromFile( filepath String, charset_encoding String ) : BLOB		 Will return a BLOB object of the Sql Procedure type encapsulating the SQL Body loaded from the given filepath.
If the optional second argument is explicitly specified the SQL Body will be automatically converted from the given charset encoding into UTF-8.
1 will be returned on success; an exception will be raised on failure or invalid arguments.
Explicitly setting the environment variable SPATIALITE_SECURITY=relaxed is absolutely required in order to enable this function.
SqlProc_IsValid SqlProc_IsValid( object BLOB ) : Boolean Returns TRUE if the passed BLOB is valid SQL Procedure object, otherwise FALSE.
An exception will be raised on invalid argument.
SqlProc_NumVariables SqlProc_NumVariables( object BLOB ) : Integer Returns the total number of Variables declared by the given SQL Procedure object.
An exception will be raised on failure or invalid arguments.
SqlProc_VariableN SqlProc_VariableN( object BLOB , index Integer ) : String Returns the symbolic name of the Nth Variable declared by the given SQL Procedure object.
The first Variable always corresponds to index=0, and the last one to SqlProc_NumVariables() - 1.
An exception will be raised on failure or invalid arguments.
SqlProc_AllVariables SqlProc_AllVariables( object BLOB ) : String Returns a space delimited list of all Variable symbolic names declared by the given SQL Procedure object.
An exception will be raised on failure or invalid argument.
SqlProc_VarValue SqlProc_VarValue( var_name String , var_value NULL ) : String
SqlProc_VarValue( var_name String , var_value Integer ) : String
SqlProc_VarValue( var_name String , var_value Double ) : String
SqlProc_VarValue( var_name String , var_value Text ) : String
SqlProc_VarValue( var_name String , var_value BLOB ) : String		 Returns a properly formatted Variable with Value Text string.
An exception will be raised on failure or invalid argument.
SqlProc_RawSQL SqlProc_RawSQL( object BLOB ) : String Returns the raw SQL Body declared by the given SQL Procedure object.
An exception will be raised on failure or invalid argument.
SqlProc_CookedSQL SqlProc_CookedSQL( object BLOB ) : String
SqlProc_CookedSQL( object BLOB , vararg String ) : String
SqlProc_CookedSQL( object BLOB , vararg1 String [ , ... ] , varargN String ) : String		 Returns the cooked SQL Body declared by the given SQL Procedure object after applying all Variable Values.
Note: this function accepts from 0 to 64 well-formed Variable with Value arguments; declaring twice or more the same Variable is considered to be a fatal error.
An exception will be raised on failure or invalid arguments.
SqlProc_Execute SqlProc_Execute( object BLOB ) : variable Type
SqlProc_Execute( object BLOB , vararg String ) : variable Type
SqlProc_Execute( object BLOB , vararg1 String [ , ... ] , varargN String ) : variable Type		 Executes the cooked SQL Body declared by the given SQL Procedure object after applying all Variable Values.
Note: this function accepts from 0 to 64 well-formed Variable with Value arguments; declaring twice or more the same Variable is considered to be a fatal error.
On success will return the Return Value defined (explicitly or implicitly) by SqlProc_Return().
An exception will be raised on failure or invalid arguments.
SqlProc_Return SqlProc_Return( NULL ) : Integer
StoredProc_Return( NULL ) : Integer
SqlProc_Return( value Integer ) : Integer
StoredProc_Return( value Integer : Integer
SqlProc_Return( value Double ) : Integer
StoredProc_Return( value Double : Integer
SqlProc_Return( value Text ) : Integer
StoredProc_Return( value Text : Integer
SqlProc_Return( value BLOB ) : Integer
StoredProc_Return( value BLOB : Integer		 Will notify a pending EXIT request to SqlProc_Execute(), SqlProc_ExecuteLoop(), StoredProc_Execute() or StoredProc_ExecuteLoop(); the internal execution loop will immediately stop and exit after completing the current SQL statement from the SQL Body.
The current call to SqlProc_Execute(), SqlProc_ExecuteLoop(), StoredProc_Execute() or StoredProc_ExecuteLoop() will then return to its caller the Return Value specified by SqlProc_Return() or StoredProc_Return().
Any SQL Body terminating without explicitly calling SqlProc_Return() or StoredProc_Return() will always behave as if SqlProc_Return(NULL) was implicitly called.

Notes:
  1. These functions are completely effectless outside the specific execution context of SqlProc_Execute(), SqlProc_ExecuteLoop(), StoredProc_Execute() or StoredProc_ExecuteLoop().
  2. SqlProc_Return() and StoredProc_Return() are just alias names.
Will return 1 on success, an exception will be raised on failure.
SqlProc_ExecuteLoop SqlProc_ExecuteLoop( object BLOB ) : Integer
SqlProc_ExecuteLoop( object BLOB , vararg String ) : Integer
SqlProc_ExecuteLoop( object BLOB , vararg1 String [ , ... ] , varargN String ) : Integer		 This SQL function is almost exactly the same of SqlProc_Execute(), with just an outstanding differece:
  • the cooked SQL Body will be iteratively executed, as if it was declared within a while (true) { ... } never ending loop.
  • the execution loop will break only when SqlProc_Return() will set a negative or zero return value of the Integer type.
  • Warning: a badly implemented call to SqlProc_ExecuteLoop() may easily cause a nastry endless loop
Returns TRUE on success.
An exception will be raised on failure or invalid arguments.
StoredProc_CreateTables StoredProc_CreateTables( void ) : Boolean Returns TRUE if the Stored Procedures and Variables tables have been succesfully created or if they already exist, otherwise FALSE.
StoredProc_Register StoredProc_Register( name String , title String , object BLOB ) : Boolean Registers a permanent Stored Procedure:
  • name is expected to uniquely identify the Stored Procedure.
  • title is expected to specify a short description of the Stored Procedure intended scopes.
  • object is expected to be a valid BLOB of the SQL Procedure type created by SqlProc_FromText() or SqlProc_FromFile().
Returns TRUE on success, otherwise FALSE. An exception will be raised on invalid arguments.
StoredProc_Get StoredProc_Get( name String ) : BLOB Will return a SQL Procedure BLOB object from a Stored Procedure. NULL will be returned if the Stored Procedure does not exists.
An exception will be raised on invalid arguments.
StoredProc_Delete StoredProc_Delete( name String ) : Boolean Will delete a Stored Procedure.
Returns TRUE on success, otherwise FALSE. An exception will be raised on invalid arguments.
StoredProc_UpdateTitle StoredProc_UpdateTitle( name String , title String ) : Boolean Will change the Title on the given Stored Procedure.
Returns TRUE on success, otherwise FALSE. An exception will be raised on invalid arguments.
StoredProc_UpdateSqlBody StoredProc_UpdateSqlBody( name String , object BLOB ) : Boolean Will change the SQL Body on the given Stored Procedure.
Returns TRUE on success, otherwise FALSE. An exception will be raised on invalid arguments.
StoredProc_Execute StoredProc_Execute( name String ) : variable Type
StoredProc_Execute( name String , vararg String ) : variable Type
StoredProc_Execute( name String , vararg1 String [ , ... ] , varargN String ) : variable Type		 Executes the given Stored Procedure after applying all Variable Values.
Note: this function accepts from 0 to 64 well-formed Variable with Value arguments; declaring twice or more the same Variable is considered to be a fatal error.
On success will return the Return Value defined (explicitly or implicitly) by StoredProc_Return().
An exception will be raised on failure or invalid arguments.
StoredProc_ExecuteLoop StoredProc_ExecuteLoop( name String ) : Integer
StoredProc_ExecuteLoop( name String , vararg String ) : Integer
StoredProc_ExecuteLoop( name String , vararg1 String [ , ... ] , varargN String ) : Integer		 This SQL function is almost exactly the same of StoredProc_Execute(), with just an outstanding differece:
  • the cooked SQL Body will be iteratively executed, as if it was declared within a while (true) { ... } never ending loop.
  • the execution loop will break only when StoredProc_Return() will set a negative or zero return value of the Integer type.
  • Warning: a badly implemented call to StoredProc_ExecuteLoop() may easily cause a nastry endless loop
Returns TRUE on success.
An exception will be raised on failure or invalid arguments.
StoredVar_Register StoredVar_Register( name String , title String , value NULL ) : Boolean
StoredVar_Register( name String , title String , value Integer ) : Boolean
StoredVar_Register( name String , title String , value Double ) : Boolean
StoredVar_Register( name String , title String , value Text ) : Boolean
StoredVar_Register( name String , title String , value BLOB ) : Boolean		 Registers a permanent Stored Variable:
  • name is expected to uniquely identify a Variable Name.
  • title is expected to specify a short description of the Stored Variable intended scopes.
  • value can be any data-type and will be automatically converted to its correspondined textual representation.
Returns TRUE on success, otherwise FALSE. An exception will be raised on invalid arguments.
StoredVar_Get StoredVar_Get( name String ) : BLOB Will return a Variable with Value string from a Stored Variable. NULL will be returned if the Stored Variable does not exists.
An exception will be raised on invalid arguments.
StoredVar_GetValue StoredVar_GetValue( name String ) : BLOB Will return the undecorated Value string from a Stored Variable. NULL will be returned if the Stored Variable does not exists.
An exception will be raised on invalid arguments.
StoredVar_Delete StoredVar_Delete( name String ) : Boolean Will delete a Stored Variable.
Returns TRUE on success, otherwise FALSE. An exception will be raised on invalid arguments.
StoredVar_UpdateTitle StoredVar_UpdateTitle( name String , title String ) : Boolean Will change the Title on the given Stored Variable.
Returns TRUE on success, otherwise FALSE. An exception will be raised on invalid arguments.
StoredVar_UpdateValue StoredVar_UpdateValue( name String , var_value NULL ) : Boolean
StoredVar_UpdateValue( name String , var_value Integer ) : Boolean
StoredVar_UpdateValue( name String , var_value Double ) : Boolean
StoredVar_UpdateValue( name String , var_value Text ) : Boolean
StoredVar_UpdateValue( name String , var_value BLOB ) : Boolean		 Will change the Value on the given Stored Variable.
Returns TRUE on success, otherwise FALSE. An exception will be raised on invalid arguments.

back