Skip to content

Latest commit

 

History

History
943 lines (864 loc) · 300 KB

spatialite_functions.md

File metadata and controls

943 lines (864 loc) · 300 KB

spl.js supported SpatiaLite SQL functions reference list

Compiled from http://www.gaia-gis.it/gaia-sins/spatialite-sql-5.0.1.html

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
MD5TotalChecksum MD5TotalChecksum( BLOB TEXT ) : Text
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 valuesaggregate function
Stddev_samp Stddev_samp( x Double precision ) : Double precision returns the sample standard deviation of the input valuesaggregate 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
GEOS_GetLastWarningMsg GEOS_GetLastWarningMsg( void ) : String GEOS
GEOS_GetLastErrorMsg GEOS_GetLastErrorMsg( void ) : String GEOS
GEOS_GetLastAuxErrorMsg GEOS_GetLastAuxErrorMsg( void ) : String GEOS
GEOS_GetCriticalPointFromMsg GEOS_GetCriticalPointFromMsg( void ) : Point
GEOS_GetCriticalPointFromMsg( SRID Integer ) : Point
GEOS
RTTOPO_GetLastWarningMsg RTTOPO_GetLastWarningMsg( void ) : String RTTOPO
RTTOPO_GetLastErrorMsg RTTOPO_GetLastErrorMsg( void ) : String RTTOPO

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 vST_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 representationalways 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.
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
OpenGis name for this function is Length(), but it conflicts with an SQLite reserved keyword
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
NumInteriorRingNumInteriorRings 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. OpenGis name for this function is Union(), but it conflicts with an SQLite reserved keyword
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]

Function Syntax OGC defined required module Summary
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.
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.
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
ShiftCoordsShiftCoordinates 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.
ScaleCoordsScaleCoordinates 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]
RotateCoordsRotateCoordinates 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.
ReflectCoordsReflectCoordinates 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
SwapCoordsSwapCoordinates 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 codeGPLv2+ 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 codeGPLv2+ 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 codeGPLv2+ return a serialized text string corresponding to the Polynomial coeffs. will return NULL on invalid argument.
GCP2ATM GCP2ATM( matrix PolynomialCoeffs ] ) : AffineMatrix GrassGis codeGPLv2+ 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 codeGPLv2+ 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 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 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-3defined 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-3defined 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.
DropGeoTableDeprecated !!!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.
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.
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
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.
ExportGeoJSONObsolete 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
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
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: These functions are completely effectless outside the specific execution context of SqlProc_Execute(), SqlProc_ExecuteLoop(), StoredProc_Execute() or StoredProc_ExecuteLoop(). 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.