SpatiaLite 4.4.0-RC0          SQL functions reference list

back

SQL Version Info [and build options testing] functions

Function Syntax Summary
spatialite_version spatialite_version( void ) : String returns the current SpatiaLite version as a text string
spatialite_target_cpu spatialite_target_cpu( void ) : String returns the current SpatiaLite Target CPU as a text string
freexl_version freexl_version( void ) : String returns the current FreeXL version as a text string
or NULL if FreeXL is currently unsupported
proj4_version proj4_version( void ) : String returns the current PROJ.4 version as a text string
or NULL if PROJ.4 is currently unsupported
geos_version geos_version( void ) : String returns the current GEOS version as a text string
or NULL if GEOS is currently unsupported
lwgeom_version lwgeom_version( void ) : String returns the current LWGEOM version as a text string
or NULL if LWGEOM 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 underlaying library was built enabling ICONV
HasMathSQL HasMathSQL( void ) : Boolean TRUE if the underlaying library was built enabling MATHSQL
HasGeoCallbacks HasGeoCallbacks( void ) : Boolean TRUE if the underlaying library was built enabling GEOCALLBACKS
HasProj HasProj( void ) : Boolean TRUE if the underlaying library was built enabling PROJ
HasGeos HasGeos( void ) : Boolean TRUE if the underlaying library was built enabling GEOS
HasGeosAdvanced HasGeosAdvanced( void ) : Boolean TRUE if the underlaying library was built enabling GEOSADVANCED
HasGeosTrunk HasGeosTrunk( void ) : Boolean TRUE if the underlaying library was built enabling GEOSTRUNK
HasGeosReentrant HasGeosReentrant( void ) : Boolean TRUE if the underlaying library was built enabling GEOSREENTRANT
HasGeosOnlyReentrant HasGeosOnlyReentrant( void ) : Boolean TRUE if the underlaying library was built enabling GEOSONLYREENTRANT
HasLwGeom HasLwGeom( void ) : Boolean TRUE if the underlaying library was built enabling LWGEOM
HasLibXML2 HasLibXML2( void ) : Boolean TRUE if the underlaying library was built enabling LibXML2
HasEpsg HasEpsg( void ) : Boolean TRUE if the underlaying library was built enabling EPSG
HasFreeXL HasFreeXL( void ) : Boolean TRUE if the underlaying library was built enabling FREEXL
HasGeoPackage HasGeoPackage( void ) : Boolean TRUE if the underlaying library was built enabling GeoPackage support (GPKG)
HasGCP HasGCP( void ) : Boolean
HasGroundControlPoints ( void ) : Boolean		 TRUE if the underlaying library was built enabling Ground Control Points support (GGP)
HasTopology HasTopology( void ) : Boolean TRUE if the underlaying library was built enabling Topology support

Generic SQL functions

Function Syntax Summary
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 much trailing ZEROs so 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 assumption fails then NULL will be returned.
NULL if no conversion is possible.
ForceAsNull ForceAsNull( val1 Generic , val2 Generic) : Generic if val1 and val2 are equal (and exactly of the same data-type) NULL will be returned; otherwise val1 will be returned absolutely untouched and still preserving its originale data-type.
CreateUUID CreateUUID( void ) : Text returns a Version 4 (random) UUID (Universally unique identifier).
MD5Checksum MD5Checksum( BLOB | TEXT ) : Text returns the MD5 checksum corresponding to the input value.
Will return NULL for non-BLOB or non-TEXT input.
MD5TotalChecksum MD5TotalChecksum( BLOB | TEXT ) : Text returns a cumulative MD5 checksum.
aggregate function
EncodeURL EncodeURL( TEXT ) : Text returns the percent encoded URL corresponding to the input value.
Will return NULL for invalid input.
DecodeURL DecodeURL( TEXT ) : Text returns a plain URL from its corresponding percent encoding.
Will return NULL for invalid input.
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.
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 effectively enable this function.

Global settings per connection

Function Syntax Summary
EnableGpkgMode EnableGpkgMode( void ) : void Enables the Geopackage mode
all connections initially start by disabling the GPKG mode, that has always to be explicitly enabled whenever reguired.
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 initially start by disabling the amphibious mode, that has always to be explicitly enabled whenever reguired.
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.

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 in 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 eturn 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( b Double precision , x 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(b, x) is equivalent to Log(x) / Log(b)
Log2 Log2( x Double precision ) : Double precision returns the base-2 logarithm of x
Log2(x) is equivalent to Log(x) / Log(2)
Log10 Log10( x Double precision ) : Double precision returns the base-10 logarithm of x
Log10(x) is equivalent to Log(x) / Log(10)
PI PI( void ) : Double precision returns the value of PI
Pow
Power		 Pow( x Double precision , y Double precision ) : Double precision
Power( x Double precision , y Double precision ) : Double precision		 returns the value of x raised to the power of y
Radians Radians( x Double precision ) : Double precision returns the argument x, converted from degrees to radians
Sign Sign( x Double precision ) : Double precision returns the sign of the argument as -1, 0, or 1, depending on whether x is negative, zero, or positive.
Sin Sin( x Double precision ) : Double precision returns the sine of x, where x is given in radians
Sqrt Sqrt( x Double precision ) : Double precision returns the square root of a non-negative number x
Stddev_pop Stddev_pop( x Double precision ) : Double precision returns the population standard deviation of the input values
aggregate function
Stddev_samp Stddev_samp( x Double precision ) : Double precision returns the sample standard deviation of the input values
aggregate function
Tan Tan( x Double precision ) : Double precision returns the tangent of x, where x is given in radians
Var_pop Var_pop( x Double precision ) : Double precision returns the population variance of the input values (square of the population standard deviation)
aggregate function
Var_samp Var_samp( x Double precision ) : Double precision returns the sample variance of the input values (square of the sample standard deviation)
aggregate function

SQL functions reporting GEOS / LWGEOM errors and warnings

Function Syntax Summary
GEOS_GetLastWarningMsg GEOS_GetLastWarningMsg( void ) : String GEOS Will return the most recent warning message returned by GEOS (if any).
NULL will be returned if there is no pending GEOS warning.
GEOS_GetLastErrorMsg GEOS_GetLastErrorMsg( void ) : String GEOS Will return the most recent error message returned by GEOS (if any).
NULL will be returned if there is no pending GEOS error.
GEOS_GetLastAuxErrorMsg GEOS_GetLastAuxErrorMsg( void ) : String GEOS Will return the most recent error message (auxiliary) returned by GEOS (if any).
NULL will be returned if there is no pending GEOS (auxiliary) error.
GEOS_GetCriticalPointFromMsg GEOS_GetCriticalPointFromMsg( void ) : Point
GEOS_GetCriticalPointFromMsg( SRID Integer ) : Point		 GEOS Will (possibly) return a Point Geometry extracted from the latest error / warning message returned by GEOS.
NULL will be returned if there is no pending GEOS message, or if the current GEOS message doesn't contain a critical Point.
LWGEOM_GetLastWarningMsg LWGEOM_GetLastWarningMsg( void ) : String LWGEOM Will return the most recent warning message returned by LWGEOM (if any).
NULL will be returned if there is no pending LWGEOM warning.
LWGEOM_GetLastErrorMsg LWGEOM_GetLastErrorMsg( void ) : String LWGEOM Will return the most recent error message returned by LWGEOM (if any).
NULL will be returned if there is no pending LWGEOM error.

SQL length/distance unit-conversion functions

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

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

Function Syntax Summary
DD to DMS LongLatToDMS( longitude Double precision , latitude Double precision ) : String will return a DMS string (Degrees, Minutes and Seconds) starting from DD (Decimal Degrees) input coordinates
NULL will be returned on invalid input.
DMS to DD LongitudeFromDMS( dms_expression Sting ) : Double precision
LatitudeFromDMS( dms_expression Sting ) : Double precision		 will return the DD coordinates from within a DMS expression
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 corresponding to a function invocation on NULL or not-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 corresponding to a function invocation on NULL or not-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 corresponding to a function invocation on NULL or not-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 corresponding to a function invocation on NULL or not-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 corresponding to a function invocation on NULL or not-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 corresponding to a function invocation on NULL or not-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 corresponding to a function invocation on NULL or not-BLOB argument.
TRUE if this BLOB object corresponds to a valid EXIF image
Please note: any valid EXIF image is a valid JPEG as well
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 corresponding to a function invocation on NULL or not-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 corresponding to a function invocation on NULL or not-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 corresponding to a function invocation on NULL or not-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 could be returned for an invalid argument or if no valid mime-type is detected.
BlobFromFile BlobFromFile( filepath String ) : BLOB If filepath corresponds to some valid pathname, and the corresponding file can be actually accessed in read mode, 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 open the doors 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 effectively enable this function.
BlobToFile BlobToFile( payload BLOB , filepath String ) : Integer If payload is of the BLOB-type, and if filepath corresponds to some valid pathname (accessible in write/create mode), then the corresponding file will be created/overwritten so to contain the payload.
The return type is Integer, with a return value of 1 for success, 0 for failure.
Please note well: this SQL function open the doors 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 effectively enable this function.
CountUnsafeTriggers CountUnsafeTriggers( ) : Integer This SQL function checks if the currently connected DB does contain any potentially malicious Trigger; 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 build 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 precisin [ , 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 [ , edges_only Boolean , [ origing PointGeometry ] ] ) : Geometry
ST_SquareGrid( geom ArealGeometry , size Double precision [ , edges_only Boolean , [ origing PointGeometry ] ] ) : Geometry		 GEOS return a grid of square cells (having the edge length of size) precisely covering the input Geometry.
The returned Geometry will usually be of the MultiPolygon type (a collection of Squares), but will be a MultiLinestring if the optional edges_only argument is set to TRUE
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 [ , edges_only Boolean , [ origing PointGeometry ] ] ) : Geometry
ST_TriangularGrid( geom ArealGeometry , size Double precision [ , edges_only Boolean , [ origing PointGeometry ] ] ) : Geometry		 GEOS return a grid of triangular cells (having the edge length of size) precisely covering the input Geometry.
The returned Geometry will usually be of the MultiPolygon type (a collection of Squares), but will be a MultiLinestring if the optional edges_only argument is set to TRUE
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 [ , edges_only Boolean , [ origing PointGeometry ] ] ) : Geometry
ST_HexagonalGrid( geom ArealGeometry , size Double precision [ , edges_only Boolean , [ origing PointGeometry ] ] ) : Geometry		 GEOS return a grid of hexagonal cells (having the edge length of size) precisely covering the input Geometry.
The returned Geometry will usually be of the MultiPolygon type (a collection of Squares), but will be a MultiLinestring if the optional edges_only argument is set to TRUE
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 base return the minimum Z-coordinate value for geom as a double precision number.
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 base return the maximum Z-coordinate value for geom as a double precision number.
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 base return the minimum M-coordinate value for geom as a double precision number.
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 base return the maximum M-coordinate value for geom as a double precision number.
NULL will be returned if geom isn't a valid Geometry or if geom has no M dimension.

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

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

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

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

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

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

SQL functions supporting exotic geometric formats

Function Syntax OGC
defined		 required
module		 Summary
AsSVG AsSVG( geom Geometry [ , relative Integer [ , precision Integer ] ] ) : String base returns the SVG [Scalable Vector Graphics] representation
AsKml AsKml( geom Geometry [ , precision Integer ] ) : String
AsKml( name String, description String, geom Geometry [ , precision Integer ] ) : String		 PROJ.4 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
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 ) : Binary base returns the FGF [FDO Geometry Binary Format] representation
GeomFromFGF GeomFromFGF( fgfGeometry Binary [ , SRID Integer] ) : Geometry base construct a geometric object given its FGF binary Representation

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 number of dimensions used by the geometric object as:
2, 3 or 4
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 corresponding to a function invocation on 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 corresponding to a function invocation on 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 ) : Integer base directly sets the Spatial Reference System ID for this geometric object [no reprojection is applied]
The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN corresponding to a function invocation on NULL arguments.
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 corresponding to a function invocation on NULL arguments.
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 corresponding to a function invocation on NULL arguments.
TRUE if this geometric object is simple, as defined in the Geometry Model
IsValid IsValid( geom Geometry ) : Integer
ST_IsValid( geom Geometry ) : Integer		 GEOS The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN corresponding to a function invocation on NULL arguments.
TRUE if this geometric object does not contains any topological error
IsValidReason IsValidReason( geom Geometry ) : String
ST_IsValidReason( geom Geometry ) : String		 GEOS Will return a TEXT string stating if a Geometry is valid and if not valid, a reason why.
NULL will be returned on invalid arguments.
IsValidDetail IsValidDetail( geom Geometry ) : Geometry
ST_IsValidDetail( geom Geometry ) : Geometry		 GEOS Will return a Geometry detail (usually a POINT) causing invalidity.
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 returns a new Geometry [if a valid Geometry was supplied], or NULL in any other case.
Any Polygon Ring will be oriented accordingly to Left Hand Rule (Exterior Ring will be clockwise oriented, and Interior Rings will be counter-clockwise oriented).

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

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 base returns a Geometry using the [XYZ] space dimension
CastToXYM CastToXYM( geom Geometry ) : geom Geometry base returns a Geometry using the [XYZM] space dimension
CastToXYZM CastToXYZM( geom Geometry ) : geom Geometry base returns a Geometry using the [XYZM] space dimension

SQL functions on type Point

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

SQL functions on type Curve [Linestring or Ring]

Function Syntax OGC
defined		 required
module		 Summary
StartPoint StartPoint( c Curve ) : Point
ST_StartPoint( c Curve ) : Point		 X base return a Point containing the first Point of c
EndPoint EndPoint( c Curve ) : Point
ST_EndPoint( c Curve ) : Point		 X base return a Point containing the last Point of c
Length GLength( c Curve ) : Double precision
OpenGis name for this function is Length(), but it conflicts with an SQLite reserved keyword
ST_Length( c Curve ) : Double precision 		X GEOS return the length of c
Starting since v.4.0.0 this function will simply consider Linestrings and MultiLinestrings, ignoring any Polygon or MultiPolygon
GLength( c Curve , use_ellipsoid Boolean ) : Double precision
ST_Length( c Curve , use_ellipsoid Boolean ) : Double precision 		X GEOS return the length of c (measured in meters).
If the use_ellipsoid argument is set to TRUE the precise (but slower) length will be computed on the Ellipsoid, otherwise will be computed on the Great Cicle (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 Cicle (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 corresponding to a function invocation on NULL arguments;
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 corresponding to a function invocation on NULL arguments.
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.

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 LWGEOM 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

SQL functions on type Polygon

Function Syntax OGC
defined		 required module Summary
ExteriorRing ExteriorRing( polyg Polygon ) : LineString
ST_ExteriorRing( polyg Polygon ) : LineString		 X base return the exteriorRing of p
NumInteriorRing
NumInteriorRings		 NumInteriorRing( polyg Polygon ) : Integer
NumInteriorRings( polyg Polygon ) : Integer
ST_NumInteriorRing( polyg Polygon ) : Integer		 X base return the number of interiorRings
InteriorRingN InteriorRingN( polyg Polygon , n Integer ) : LineString
ST_InteriorRingN( polyg Polygon , n Integer ) : LineString		 X base return the nth 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 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 corresponding to a function invocation on NULL 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 corresponding to a function invocation on NULL 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 corresponding to a function invocation on NULL 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 corresponding to a function invocation on NULL 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 corresponding to a function invocation on NULL 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 corresponding to a function invocation on NULL 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 corresponding to a function invocation on 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 corresponding to a function invocation on 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 corresponding to a function invocation on 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 corresponding to a function invocation on 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 corresponding to a function invocation on 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 corresponding to a function invocation on 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 corresponding to a function invocation on 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 corresponding to a function invocation on 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 corresponding to a function invocation on 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 corresponding to a function invocation on 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 corresponding to a function invocation on 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 corresponding to a function invocation on NULL arguments;
convenience predicate: TRUE if g1 is completely covered by g2
Relate Relate( geom1 Geometry , geom2 Geometry , patternMatrix String ) : Integer
ST_Relate( geom1 Geometry , geom2 Geometry , patternMatrix String ) : Integer		 X GEOS The return type is Integer, with a return value of 1 for TRUE, 0 for FALSE, and –1 for UNKNOWN corresponding to a function invocation on NULL arguments;
returns TRUE if the spatial relationship specified by the patternMatrix holds

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 Cicle (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.4 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 that implement spatial operators

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

SQL functions that implement spatial operators
[GEOS specific features]

HausdorffDistance HausdorffDistance( geom1 Geometry , geom2 Geometry ) : Double precision
ST_HausdorffDistance( geom1 Geometry , geom2 Geometry ) : Double precision		 GEOS return the Hausdorff distance between geom1 and geom2
learn more
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
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).
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.

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
[LWGEOM features]

Function Syntax OGC
defined		 required
module		 Summary
MakeValid MakeValid( geom Geometry ) : Geometry
ST_MakeValid( geom Geometry ) : Geometry		 LWGEOM 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		 LWGEOM 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		 LWGEOM 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		 LWGEOM 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		 LWGEOM 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		 LWGEOM 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).
Azimuth Azimuth( pt1 Geometry, pt2 Geometry ) : Double precision
ST_Azimuth( pt1 Geometry , pt2 Geometry ) : Double precision		 LWGEOM 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		 LWGEOM 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		 LWGEOM 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 		LWGEOM 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		 LWGEOM return the max distance between geom1 and geom2
3DDistance ST_3DDistance( geom1 Geometry , geom2 Geometry ) : Double precision LWGEOM return the 3D-distance between geom1 and geom2 (Z coordinates will be considered)
3DMaxDistance ST_3DMaxDistance( geom1 Geometry , geom2 Geometry ) : Double precision LWGEOM return the max 3D-distance between geom1 and geom2 (Z coordinates will be considered)
3dLength ST_3dLength( geom Geometry ) : Double precision LWGEOM 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 LWGEOM 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		 LWGEOM 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.

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		 PROJ.4 return a geometric object obtained by reprojecting coordinates into the Reference System identified by newSRID
SridFromAuthCRS SridFromAuthCRS( auth_name String , auth_SRID Integer ) : Integer base return the internal SRID corresponding to auth_name and auth_SRID
-1 will be returned if no such CRS is defined
ShiftCoords
ShiftCoordinates		 ShiftCoords( geom Geometry , shiftX Double precision , shiftY Double precision ) : Geometry
ShiftCoordinates( geom Geometry , shiftX Double precision , shiftY Double precision ) : Geometry		 base return a geometric object obtained by translating coordinates according to shiftX and shiftY values
ST_Translate ST_Translate( geom Geometry , shiftX Double precision , shiftY Double precision , shiftZ Double precision ) : Geometry base return a geometric object obtained by translating coordinates according to shiftX, shiftY and shiftZ values
ST_Shift_Longitude ST_Shift_Longitude( geom Geometry ) : Geometry base return a geometric object obtained by translating any negative longitude by 360.
Only meaningful for geographic (longitude/latitude) coordinates.
Negative longitudes (-180/0) will be shifted by 360, thus allowing to represent longitudes in the 0/360 range and effectively crossing the International Date Line.
NormalizeLonLat NormalizeLonLat( geom Geometry ) : Geometry base return a geometric object obtained by normalizing any longitude in the range [-180 / +180] and any latitude in the range [-90 / + 90].
Only meaningful for geographic (longitude/latitude) coordinates.
ScaleCoords
ScaleCoordinates		 ScaleCoords( geom Geometry , scaleX Double precision [ , scaleY Double precision ] ) : Geometry
ScaleCoordinates( geom Geometry , scaleX Double precision [ , scaleY Double precision ] ) : Geometry		 base return a geometric object obtained by scaling coordinates according to scaleX and scaleY values
if only one scale factor is specified, then an isotropic scaling occurs [i.e. the same scale factor is applied to both axis]
otherwise an anisotropic scaling occurs [i.e. each axis is scaled according to its own scale factor]
RotateCoords
RotateCoordinates		 RotateCoords( geom Geometry , angleInDegrees Double precision ) : Geometry
RotateCoordinates( geom Geometry , angleInDegrees Double precision ) : Geometry		 base return a geometric object obtained by rotating coordinates according to angleInDegrees value.
Positive angle = clockwise rotation.
Negative angle = counterclockwise rotation.
ReflectCoords
ReflectCoordinates		 ReflectCoords( geom Geometry , xAxis Integer , yAxis Integer ) : Geometry
ReflectCoordinates( geom Geometry , xAxis Integer , yAxis Integer ) : Geometry		 base return a geometric object obtained by reflecting coordinates according to xAxis and yAxis switches
i.e. if xAxis is 0 (FALSE), then x-coordinates remains untouched; otherwise x-coordinates will be reflected
SwapCoords
SwapCoordinates		 SwapCoords( geom Geometry ) : Geometry
SwapCoordinates( geom Geometry ) : Geometry		 base return a geometric object obtained by swapping x- and y-coordinates

SQL functions supporting Affine Transformations and Ground Control Points

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

SQL functions for Spatial-MetaData and Spatial-Index handling

Function Syntax OGC
defined		 required
module		 Summary
InitSpatialMetaData InitSpatialMetaData( void ) : Integer
InitSpatialMetaData( transaction Integer ) : Integer
InitSpatialMetaData( mode String ) : Integer
InitSpatialMetaData( transaction Integer , mode String ) : Integer		 base Creates the geometry_columns and spatial_ref_sys metadata tables
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
  • if the optional argument transaction is set to TRUE the whole operation will be handled as a single Transaction (faster): the default setting is transaction=FALSE (slower, but safer).
  • if the optional argument mode is not specified then any possible ESPG SRID definition 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
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; anyway, 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
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 table into 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 table from Spatial MetaData tables, dropping the VirtualShape 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.
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.
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
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 TRUEa 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)
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)
RebuildGeometryTriggers RebuildGeometryTriggers( table_name String , geometry_column_name String ) : integer base This function will reinstall all geometry-related Triggers for the named table.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure)
UpgradeGeometryTriggers UpgradeGeometryTriggers( transaction Integer ) : integer base This function will upgrade all geometry-related Triggers to the latest version (all DB tables declaring at least one Geometry will be affected by the upgrade).
If the transaction argument is set to TRUE then the whole upgrade will be safely executed within an internally defined SQL transaction.
Please note: DB-files created using obsolete versions of SpatiaLite (< 4.0.0) will not be upgraded.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure)

SQL functions supporting the MetaCatalog and related Statistics

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

SQL functions supporting SLD/SE Styled Layers

Function Syntax OGC
defined		 required
module		 Summary
CreateStylingTables CreateStylingTables() : Integer
CreateStylingTables( relaxed Integer ) : Integer
CreateStylingTables( relaxed Integer , transaction Integer ) : Integer		 libxml2 Creates a set of tables supporting SLD/SE Styled Layers.
  • if the optional argument relaxed is explicitly set as TRUE then a relaxed version of the validating Triggers will be installed (not checking for strict XSD schema validation).
  • if the optional argument transaction is explicitly set as TRUE then the whole operation will be atomically confined within a monolithic SQL transaction
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
Please note: will implicitly invoke CreateRasterCoverages() if the raster_coverages table has not yet been created.
SE_RegisterVectorCoverage SE_RegisterVectorCoverage( coverage_name String , f_table_name String , f_geometry_column Sting ) : Integer
SE_RegisterVectorCoverage( coverage_name String , f_table_name String , f_geometry_column Sting , title String , abstract String ) : Integer		 libxml2 Creates a Vector Coverage based on an already existing Spatial Table.
  • coverage_name is the symbolic name uniquely identifying each Vector Coverage (Primary Key).
  • f_table_name and f_geometry_column are expected to match a corresponding entry in the geometry_columns table.
  • the optional arguments title and abstract could be eventually specified for better human readability.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterVectorCoverage SE_UnregisterVectorCoverage( coverage_name String ) : Integer libxml2 Completely removes an already defined Vector Coverage this including any furher depency; the underlying Spatial Table will be absolutely unaffected.
  • coverage_name must identify an existing Vector Coverage.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_SetVectorCoverageInfos SE_SetVectorCoverageInfos( coverage_name String , title String , abstract String ) : Integer libxml2 Updates the descriptive infos associated to a Vector Coverage.
  • coverage_name must identify an existing Vector Coverage.
  • title and abstract represent the descriptive infos to be set.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterVectorCoverageSrid SE_RegisterVectorCoverageSrid( coverage_name String , srid Integer ) : Integer libxml2 Adds an alternative SRID to an already defined Vector Coverage.
  • coverage_name must identify an existing Vector Coverage.
  • srid is expected to match a corresponding entry in the spatial_ref_sys table.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterVectorCoverageSrid SE_UnregisterVectorCoverageSrid( coverage_name String , srid Integer ) : Integer libxml2 Removes an already defined alternative SRID from a Vector Coverage.
  • coverage_name and srid must identify some previously defined alternative SRID.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UpdateVectorCoverageExtent SE_UpdateVectorCoverageExtent() : Integer
SE_UpdateVectorCoverageExtent( transaction Integer ) : Integer
SE_UpdateVectorCoverageExtent( coverage_name String ) : Integer
SE_UpdateVectorCoverageExtent( coverage_name String , transaction Integer ) : Integer		 libxml2 Updates the Extent boundary supporting a Vector Coverage, this including any eventually defined alternative SRID.
  • if the optional coverage_name argument is set then only that single Vector Coverage will be updated; otherwise all registered Vector Coverages will be processed in a single pass (may require a long time).
  • if the optional transaction argument is set to TRUE then the whole operation will be internally handled as a single SQL Transaction.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterVectorCoverageKeyword SE_RegisterVectorCoverageeKeyword( coverage_name String , keyword String ) : Integer libxml2 Adds a keyword to an already defined Vector Coverage.
  • coverage_name must identify an existing Vector Coverage.
  • keyword must not be already defined for the same Coverage.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterVectorCoverageKeyword SE_UnregisterVectorCoverageKeyword( coverage_name String , keyword String ) : Integer libxml2 Removes an already defined keyword from a Vector Coverage.
  • coverage_name and keyword must identify some previously defined keyword.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterExternalGraphic SE_RegisterExternalGraphic( xlink_href String , resource BLOB ) : Integer
SE_RegisterExternalGraphic( xlink_href String , resource BLOB , title String , abstract String , file_name String ) : Integer		 libxml2 Inserts (or updates) an External Graphic Resource.
  • xlink_href uniquely identifies each Resource (Primary Key).
  • resource is expected to be a BLOB containing an image/gif, image/png, image/jpeg or image/svg+xml payload.
  • the optional arguments title, abstract and file_name could be eventually specified for better human readability.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterExternalGraphic SE_UnregisterExternalGraphic( xlink_href String ) : Integer libxml2 Deletes an already existing External Graphic Resource.
  • xlink_href the External Resource to be deleted.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterVectorStyle SE_RegisterVectorStyle( style BLOB ) : Integer libxml2 Inserts a new Vector Style definition.
  • style is expected to be an XmlBLOB containing a valid SLD/SE Style of the Vector type.
    If CreatedStylingTables() was invoked without specifying the relaxed option this XmlBLOB is expected to have successfully passed a formal XML Schema Validation.
    The registered Style will be identified either by its unique Style ID or by its Style Name automatically retrieved from SLD/SE XML.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterVectorStyle SE_UnregisterVectorStyle( style_id Integer [ , remove_all Integer ] ) : Integer
SE_UnregisterVectorStyle( style_name Text [ , remove_all Integer ] ) : Integer		 libxml2 Removes an already registered Vector Style definition.
  • The Style to be removed could be referenced either by its unique Style Id or by its Style Name.
    Anyway any attempt to remove a Style identified by its Name will be rejected if the if two (or more) Styles share the same Name thus causing ambiguity.
  • when the optional argument remove_all is explicitly set to TRUE the Style will be removed even if it's already referenced by some VectorStyledLayer and all dependings references will be removed at the same time.
    In any other case any attempt to unregister a Style already referenced by one (or more) VectorStyleLayer will be rejected.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_ReloadVectorStyle SE_ReloadVectorStyle( style_id Integer , style BLOB ) : Integer
SE_ReloadVectorStyle( style_name Text , style BLOB ) : Integer		 libxml2 Updates an already existing Vector Style definition.
  • style is expected to be an XmlBLOB containing a valid SLD/SE Style of the Vector type.
    If CreatedStylingTables() was invoked without specifying the relaxed option this XmlBLOB is expected to have successfully passed a formal XML Schema Validation.
    The updated Style will continue to be identified by its current unique Style ID but the Style Name will be automatically updated accordingly to SLD/SE XML.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterVectorStyledLayer SE_RegisterVectorStyledLayer( coverage_name String , style_id Integer ) : Integer
SE_RegisterVectorStyledLayer( coverage_name String , style_name Text ) : Integer		 libxml2 Associates a Vector Style to a Vector Styled Layer.
  • coverage_name must identify an existing Vector Layer.
  • An already registered Style can be referenced either by its unique Stile ID or by its Name; anyway a reference by Name could eventually fail if the Name is ambiguous (duplicate).
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterVectorStyledLayer SE_UnregisterVectorStyledLayer( coverage_name String , style_id Integer ) : Integer
SE_UnregisterVectorStyledLayer( coverage_name String , style_name Text ) : Integer		 libxml2 Removes an association between a Vector Style and a Vector Styled Layer.
  • coverage_name must identify an existing Vector Layer.
  • An already associated Style can be referenced either by its unique Stile ID or by its Name; anyway a reference by Name could eventually fail if the Name is ambiguous (duplicate).
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterRasterStyle SE_RegisterRasterStyle( style BLOB ) : Integer libxml2 Inserts a new Raster Style definition.
  • style is expected to be an XmlBLOB containing a valid SLD/SE Style of the Raster type.
    If CreatedStylingTables() was invoked without specifying the relaxed option this XmlBLOB is expected to have successfully passed a formal XML Schema Validation.
    The registered Style will be identified either by its unique Style ID or by its Style Name automatically retrieved from SLD/SE XML.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterRasterStyle SE_UnregisterRasterStyle( style_id Integer [ , remove_all Integer ] ) : Integer
SE_UnregisterRasterStyle( style_name Text [ , remove_all Integer ] ) : Integer		 libxml2 Removes an already registered Raster Style definition.
  • The Style to be removed could be referenced either by its unique Style Id or by its Style Name.
    Anyway any attempt to remove a Style identified by its Name will be rejected if the if two (or more) Styles share the same Name thus causing ambiguity.
  • when the optional argument remove_all is explicitly set to TRUE the Style will be removed even if it's already referenced by some RasterStyledLayer and all dependings references will be removed at the same time.
    In any other case any attempt to unregister a Style already referenced by one (or more) RasterStyleLayer will be rejected.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_ReloadRasterStyle SE_ReloadRasterStyle( style_id Integer , style BLOB ) : Integer
SE_ReloadRasterStyle( style_name Text , style BLOB ) : Integer		 libxml2 Updates an already existing Raster Style definition.
  • style is expected to be an XmlBLOB containing a valid SLD/SE Style of the Raster type.
    If CreatedStylingTables() was invoked without specifying the relaxed option this XmlBLOB is expected to have successfully passed a formal XML Schema Validation.
    The updated Style will continue to be identified by its current unique Style ID but the Style Name will be automatically updated accordingly to SLD/SE XML.
    • <
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterRasterStyledLayer SE_RegisterRasterStyledLayer( coverage_name String , style_id Integer ) : Integer
SE_RegisterRasterStyledLayer( coverage_name String , style_name Text ) : Integer		 libxml2 Associates a Raster Style to a Raster Styled Layer.
  • coverage_name must identify an existing Raster Layer.
  • An already registered Style can be referenced either by its unique Stile ID or by its Name; anyway a reference by Name could eventually fail if the Name is ambiguous (duplicate).
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterRasterStyledLayer SE_UnregisterRasterStyledLayer( coverage_name String , style_id Integer ) : Integer
SE_UnregisterRasterStyledLayer( coverage_name String , style_name Text ) : Integer		 libxml2 Removes an association between a Raster Style and a Raster Styled Layer.
  • coverage_name must identify an existing Raster Layer.
  • An already associated Style can be referenced either by its unique Stile ID or by its Name; anyway a reference by Name could eventually fail if the Name is ambiguous (duplicate).
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterRasterCoverageSrid SE_RegisterRasterCoverageSrid( coverage_name String , srid Integer ) : Integer libxml2 Adds an alternative SRID to an already defined Raster Coverage.
  • coverage_name must identify an existing Raster Coverage.
  • srid is expected to match a corresponding entry in the spatial_ref_sys table.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterRasterCoverageSrid SE_UnregisterRasterCoverageSrid( coverage_name String , srid Integer ) : Integer libxml2 Removes an already defined alternative SRID from a Raster Coverage.
  • coverage_name and srid must identify some previously defined alternative SRID.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UpdateRasterCoverageExtent SE_UpdateRasterCoverageExtent() : Integer
SE_UpdateRasterCoverageExtent( transaction Integer ) : Integer
SE_UpdateRasterCoverageExtent( coverage_name String ) : Integer
SE_UpdateRasterCoverageExtent( coverage_name String , transaction Integer ) : Integer		 libxml2 Updates the Extent boundary supporting a Raster Coverage, this including any eventually defined alternative SRID.
  • if the optional coverage_name argument is set then only that single Raster Coverage will be updated; otherwise all registered Raster Coverages will be processed in a single pass (may require a long time).
  • if the optional transaction argument is set to TRUE then the whole operation will be internally handled as a single SQL Transaction.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterRasterCoverageKeyword SE_RegisterRasterCoverageKeyword( coverage_name String , keyword String ) : Integer libxml2 Adds a keyword to an already defined Raster Coverage.
  • coverage_name must identify an existing Raster Coverage.
  • keyword must not be already defined for the same Coverage.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterRasterCoverageKeyword SE_UnregisterRasterCoverageKeyword( coverage_name String , keyword String ) : Integer libxml2 Removes an already defined keyword from a Raster Coverage.
  • coverage_name and keyword must identify some previously defined keyword.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_SetStyledGroupInfos SE_SetStyledGroupInfos( group_name String , title String , abstract String ) : Integer libxml2 Inserts (or updates) the descriptive infos associated to a Styled Group.
  • group_name must identify a Styled Group: if the Styled Group doesn't yet exists it will be implicitly created.
  • title and abstract represent the descriptive infos to be set.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterStyledGroup SE_UnregisterStyledGroup( group_name String ) : Integer libxml2 Completely removes a Styled Group and any related item.
  • group_name must identify an existing Styled Group.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterStyledGroupVector SE_RegisterStyledGroupVector( group_name String , coverage_name String ) : Integer libxml2 Registers a Vector Layer within a Styled Group.
  • group_name must identify a Styled Group: if the Styled Group doesn't yet exists it will be implicitly created.
  • coverage_name must identify an existing Vector Layer.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterStyledGroupRaster SE_RegisterStyledGroupRaster( group_name String , coverage_name String ) : Integer libxml2 Registers a Raster Layer within a Styled Group.
  • group_name must identify a Styled Group: if the Styled Group doesn't yet exists it will be implicitly created.
  • coverage_name must identify an existing Raster Layer.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_SetStyledGroupLayerPaintOrder SE_SetStyledGroupLayerPaintOrder( item_id Integer , paint_order Integer ) : Integer libxml2 Explictly assigns the paint_order for a Raster or Vector Layer within a Styled Group.
  • If paint_order is a negative value then the next available order position will be automatically assigned (top level).
    Please note: the painting order will always follow the Painter rule, i.e. a lower paint-order value will always be painted before painting any higher value.
  • An already defined Vector or Raster Layer Item within a Styled Group can always be referenced its unique item_id.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_SetStyledGroupVectorPaintOrder SE_SetStyledGroupVectorPaintOrder( group_name Text , coverage_name String , paint_order Integer ) : Integer libxml2 Explictly assigns the paint_order for a Vector Layer within a Styled Group.
  • If paint_order is a negative value then the next available order position will be automatically assigned (top level).
    Please note: the painting order will always follow the Painter rule, i.e. a lower paint-order value will always be painted before painting any higher value.
  • An already defined Vector Layer Item can be safely referenced by its Name.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_SetStyledGroupRasterPaintOrder SE_SetStyledGroupRasterPaintOrder( group_name Text , coverage_name String , paint_order Integer ) : Integer libxml2 Explictly assigns the paint_order for a Raster Layer within a Styled Group.
  • If paint_order is a negative value then the next available order position will be automatically assigned (top level).
    Please note: the painting order will always follow the Painter rule, i.e. a lower paint-order value will always be painted before painting any higher value.
  • An already defined Raster Layer Item can be safely referenced by its Name.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterStyledGroupLayer SE_UnregisterStyledGroupLayer( item_id Integer ) : Integer libxml2 Removes a reference to a Raster or Vector Layer from within a Styled Group.
  • An already defined Vector or Raster Layer Item within a Styled Group can always be referenced its unique item_id.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterStyledGroupVector SE_UnregisterStyledGroupVector( group_name Text , coverage_name String ) : Integer libxml2 Removes a reference to a Vector Layer from within a Styled Group.
  • An already defined Vector Layer Item can be safely referenced by its Name.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterStyledGroupRaster SE_UnregisterStyledGroupRaster( group_name Text , coverage_name String ) : Integer libxml2 Removes a reference to a Raster Layer from within a Styled Group.
  • An already defined Raster Layer Item can be safely referenced by its Name.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterGroupStyle SE_RegisterGroupStyle( style BLOB ) : Integer libxml2 Inserts a new Group Style definition.
  • style is expected to be an XmlBLOB containing a valid SLD complex Style.
    If CreatedStylingTables() was invoked without specifying the relaxed option this XmlBLOB is expected to have successfully passed a formal XML Schema Validation.
    The registered Style will be identified either by its unique Style ID or by its unique Style Name automatically retrieved from SLD/SE XML.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterGroupStyle SE_UnregisterGroupStyle( style_id Integer [ , remove_all Integer ] ) : Integer
SE_UnregisterGroupStyle( style_name Text [ , remove_all Integer ] ) : Integer		 libxml2 Removes an already registered Group Style definition.
  • The Style to be removed could be referenced either by its unique Style Id or by its Style Name.
    Anyway any attempt to remove a Style identified by its Name will be rejected if the if two (or more) Styles share the same Name thus causing ambiguity.
  • when the optional argument remove_all is explicitly set to TRUE the Style will be removed even if it's already referenced by some RasterStyledLayer and all dependings references will be removed at the same time.
    In any other case any attempt to unregister a Style already referenced by one (or more) RasterStyleLayer will be rejected.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_ReloadGroupStyle SE_ReloadGroupStyle( style_id Integer , style BLOB ) : Integer
SE_ReloadGroupStyle( style_name Text , style BLOB ) : Integer		 libxml2 Updates an already existing Group Style definition.
  • style is expected to be an XmlBLOB containing a valid SLD complex Style.
    If CreatedStylingTables() was invoked without specifying the relaxed option this XmlBLOB is expected to have successfully passed a formal XML Schema Validation.
    The updated Style will continue to be identified by its current unique Style ID but the Style Name will be automatically updated accordingly to SLD/SE XML.
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_RegisterStyledGroupStyle SE_RegisterStyledGroupStyle( group_name String , style_id Integer ) : Integer
SE_RegisterStyledGroupStyle( group_name String , style_name Text ) : Integer		 libxml2 Associates a Group Style to a Styled Group.
  • group_name must identify an existing Styled Group.
  • An already registered Style can be referenced either by its unique Stile ID or by its Name; anyway a reference by Name could eventually fail if the Name is ambiguous (duplicate).
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.
SE_UnregisterStyledGroupStyle SE_UnregisterStyledGroupStyle( group_name String , style_id Integer ) : Integer
SE_UnregisterStyledGroupStyle( group_name String , style_name Text ) : Integer		 libxml2 Removes an association between a Group Style and a Styled Group.
  • group_name must identify an existing Styled Group.
  • An already associated Style can be referenced either by its unique Stile ID or by its Name; anyway a reference by Name could eventually fail if the Name is ambiguous (duplicate).
the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure): -1 will be returned on invalid arguments.

SQL functions supporting ISO Metadata

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

SQL functions implementing FDO/OGR compatibility

Function Syntax OGC
defined		 required
module		 Summary
CheckSpatialMetaData CheckSpatialMetaData( void ) : Integer base Checks the Spatial Metadata type, then returning:
  • 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)
AutoFDOStart AutoFDOStart( void ) : 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]
AutoFDOStop AutoFDOStop( void ) : 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]
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:
  • 'WBT'
  • '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 base This function will inspect the DB layout checking if it corresponds to the GPKG own style
the return type is Integer, with a return value of 1 for TRUE, 0 for FALSE
AutoGPKGStart AutoGPKGStart( void ) : 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]
AutoGPKGStop AutoGPKGStop( void ) : 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]
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 gpkgAddGeomtryColumn( table_name Sting, geometry_column_name String , geometry_type String , with_z Integer , with_m Integer , srs_id Integer ) : void GeoPackage Adds a geometry column to the specified table:
  • geometry_type is a normal WKT name:
    • "GEOMETRY"
    • "POINT"
    • "LINESTRING"
    • "POLYGON"
    • "MULTIPOINT"
    • "MULTILINESTRING"
    • "MULTIPOLYGON"
    • "GEOMCOLLECTION"
  • with_z is a flag (0 for no z values, 1 for mandatory z values, 2 for optional z values)
  • with_m is a flag (0 for no m values, 1 for mandatory m values, 2 for optional m values)
returns nothing on success, raises exception on error
gpkgAddGeometryTriggers gpkgAddGeometryTriggers( table_name String , geometry_column_name String ) : void GeoPackage This function will add Geopackage geometry table triggers for the named table.
returns nothing on success, raises exception on error
gpkgAddSpatialIndex gpkgAddSpatialIndex( table_name String , geometry_column_name String ) : void GeoPackage This function will add Geopackage Spatial Index support for the named table.
returns nothing on success, raises exception on error
gpkgMakePoint gpkgMakePoint (x Double precision , y Double precision ) : GPKG Blob Geometry
gpkgMakePoint (x Double precision , y Double precision , srid Integer ) : GPKG Blob Geometry		 GeoPackage This function will create a GeoPackage geometry POINT.
Raises a SQL exception on error
gpkgMakePointZ gpkgMakePointZ (x Double precision , y Double precision , z Double precision ) : GPKG Blob Geometry
gpkgMakePointZ (x Double precision , y Double precision , z Double precision , srid Integer ) : GPKG Blob Geometry		 GeoPackage This function will create a GeoPackage geometry POINT Z.
Raises a SQL exception on error
gpkgMakePointM gpkgMakePointM (x Double precision , y Double precision , m Double precision ) : GPKG Blob Geometry
gpkgMakePointM (x Double precision , y Double precision , m Double precision , srid Integer ) : GPKG Blob Geometry		 GeoPackage This function will create a GeoPackage geometry POINT M.
Raises a SQL exception on error
gpkgMakePointZM gpkgMakePointZM (x Double precision , y Double precision , z Double precision , m Double precision ) : GPKG Blob Geometry
gpkgMakePointZM (x Double precision , y Double precision , z Double precision , m Double precision , srid Integer ) : GPKG Blob Geometry		 GeoPackage This function will create a GeoPackage geometry POINT ZM.
Raises a SQL exception on error
IsValidGPB IsValidGPB( geom Blob ) : Integer GeoPackage This function will inspect a BLOB then checking if it really corresponds to a GPKG own Geometry
the return type is Integer, with a return value of 1 for TRUE, 0 for FALSE
AsGPB AsGPB( geom BLOB encoded geometry ) : GPKG Blob Geometry GeoPackage This function will convert a SpatiaLite geometry blob into a GeoPackage format geometry blob.
Will return NULL if any error is encountered
GeomFromGPB GeomFromGPB( geom GPKG Blob Geometry ) : BLOB encoded geometry GeoPackage This function will convert a GeoPackage format geometry blob into a SpatiaLite geometry blob.
Will return NULL if any error is encountered
CastAutomagic CastAutomagic( geom Blob ) : BLOB encoded geometry GeoPackage This function will indifferently accept on input:
  • a SpatiaLite own BLOB Geometry
  • a GPKG own BLOB Geometry
then returning a SpatiaLite own BLOB geometry.
Will return NULL if any error is encountered or on invalid / mismatching argument
GPKG_IsAssignable GPKG_IsAssignable( expected_type_name String , actual_type_name String ) : Integer GeoPackage This function will check if expected_type is the same or is a super-type of actual_type; this function is required by the standard implementation of GPKG Geometry validation Triggers.
the return type is Integer, with a return value of 1 for TRUE, 0 for FALSE

SQL functions for MbrCache-based queries

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

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

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

SQL functions supporting XmlBLOB

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

SQL functions supporting SRID inspection

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

SQL functions supporting Topology-Geometry

Function Syntax ISO 13249-3
defined		 required
module		 Summary
GetLastTopologyException GetLastTopologyException( toponame Text ) : Text LWGEOM Will return the most recent exception raised by this Topo-Geo, or NULL if no exception is currently pending.
InitTopoGeo ST_InitTopoGeo( toponame Text ) : Integer X LWGEOM 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 , tolerance Double precision ) : Integer
CreateTopology( toponame Text , srid Integer , tolerance Double precision , has_z Boolean) : Integer		 LWGEOM 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).
  • tolerence: just a placeholder, at least since now. (0.0 by default).
  • has_z: if set to TRUE this Topo-Geo will support 3D - XYZ coordinates (FALSE by default).
DropTopology DropTopology( toponame Text ) : Integer LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 , tolerance Double precision ) : Integer LWGEOM Will attempt to find the ID of a Node located at Point.
Will return the ID of the Node on success; an exception will be raised on failure.
GetEdgeByPoint GetEdgeByPoint( toponame Text , point Geometry , tolerance Double precision ) : Integer LWGEOM Will attempt to find the ID of an Edge intersecting the given Point.
Will return the ID of the Edge on success; an exception will be raised on failure.
GetFaceByPoint GetFaceByPoint( toponame Text , point Geometry , tolerance Double precision ) : Integer LWGEOM Will attempt to find the ID of a Face intersecting the given Point.
Will return the ID of the Face on success; an exception will be raised on failure.
TopoGeo_AddPoint TopoGeo_AddPoint( toponame Text , point Geometry , tolerance Double precision ) : Text LWGEOM Will attempt to add a Point (or even a MultiPoint) to an already existing Topology, possibly splitting existing Edges.
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 , tolerance Double precision ) : Integer LWGEOM Will attempt to add a Linestring (or even a MultiLinestring) to an already existing Topology, possibly splitting existing Edges/Faces.
Will return a comma separated list of all IDs of the corresponding Edges on success; an exception will be raised on failure.
TopoGeo_SubdivideLines TopoGeo_SubdivideLines( input Geometry , line_max_points Integer , line_max_length Double precision ) : MultiLinestring LWGEOM Will attempt to split a Linestring (or even a MultiLinestring, Polygon or Multipolygon) into a collection of shorter LineStrings fully respecting Topology consistency.
  • if argument line_max_points is set to a positive value 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.
  • 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; NULL on failure.
TopoGeo_FromGeoTable TopoGeo_FromGeoTable( toponame Text , db-prefix Text , table-name Text , column-name Text, tolerance Double precision ) : Integer
TopoGeo_FromGeoTable( toponame Text , db-prefix Text , table-name Text , column-name Text, tolerance Double precision , line_max_points Integer , line_max_length Double precision ) : Integer		 LWGEOM 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.
  • 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.
  • The optional arguments line_max_points and line_max_length will be interpreted in the same way adopted by TopoGeo_SubdivideLines() (default for both: -1).
Will return 1 on success; an exception will be raised on failure.
TopoGeo_Clone TopoGeo_Clone( toponame Text , new-toponame Text ) : Integer LWGEOM Will clone an existing Topology into another; the destionation Topology shall not exist and will be automatically created.
Will return 1 on success; an exception will be raised on failure.
TopoGeo_GetEdgeSeed TopoGeo_GetEdgeSeed( toponame Text , edge-id Integer ) : Geometry X LWGEOM 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 LWGEOM 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_UpdateSeeds TopoGeo_UpdateSeeds( toponame Text ) : Integer
TopoGeo_UpdateSeeds( toponame Text , incremental-mode Integer ) : Integer 		X LWGEOM Will update all persistent Edge- and Face-Seeds so to correctly represent the current state of the underlaying 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_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		 LWGEOM 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		 LWGEOM 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		 LWGEOM 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 LWGEOM 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 LWGEOM 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		 LWGEOM 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 LWGEOM Will inserting a single TopoFeature identified by is fid into a GeoTable corresponding to a TopoLayer.
  • the output GeoTable must exist and is expected to be created by a previous call to TopoGeo_ExportTopoLayer().
  • the TopoFeature identified by fid must exist and a corresponding row must not be already inserted into the output GeoTable.
Will return 1 on success; an exception will be raised on failure.

SQL functions supporting Topology-Network

Function Syntax ISO 13249-3
defined		 required
module		 Summary
GetLastNetworkException GetLastNetworkException( netname Text ) : Text LWGEOM 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 LWGEOM 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		 LWGEOM 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).
DropNetwork DropNetwork( netname Text ) : Integer LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 LWGEOM 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 , tolerance Double precision ) : Integer LWGEOM Will attempt to find the ID of a NetNode located at Point.
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 , tolerance Double precision ) : Integer LWGEOM Will attempt to find the ID of a Link intersecting the given Point.
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 LWGEOM 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 LWGEOM 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 LWGEOM 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_UpdateSeeds TopoNet_UpdateSeeds( netname Text ) : Integer
TopoNet_UpdateSeeds( netname Text , incremental-mode Integer ) : Integer 		LWGEOM Will update all persistent Link-Seeds so to correctly represent the current state of the underlaying 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_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		 LWGEOM 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		 LWGEOM 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.

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 into the primary DB.
  • Mandatory arguments:
    • db-prefix corresponding to the origin; the primary DB always corresponds to the main prefix.
    • input_table name of the origin table
    • output_table name of the destination table
    • 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 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 just create the output Table completely avoiding to copy any row betweem the two tables.
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 songle 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		 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 mamy 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 original Primary Key.
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 rows inserted into the output table.
NULL will be returned on invalid arguments.
DropGeoTable 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.
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 ] ] ] ] ] ] ] ] ) : Integer		 Will import an external Shapfile into an internal Table:
  • Mandatory arguments:
    • filename absolute or relative path leading to the Shapefile (omitting any .shp, .shx or .dbf suffix).
    • table name of the table to be created.
    • charset the character encoding adopted by the DBF member, as e.g. UTF-8 or CP1252
  • Optional arguments:
    • srid EPSG SRID value; -1 by default.
    • geom_column name to assigned to the Geometry column; Geometry by default.
    • pk_column name of a DBF column to be used in the Primary Key role; an INTEGER AUTOINCREMENT PK will be created by default.
    • geometry_type one between: AUTO, POINT|Z|M|ZM, LINESTRING|Z|M|ZM, POLYGON|Z|M|ZM, MULTIPOINT|Z|M|ZM, LINESTRING|Z|M|ZM, MULTIPOLYGON|Z|M|ZM; by default AUTO.
    • coerce2D boolean flag: casting to 2D or not; 0 by default.
    • compressed boolean flag; compressed geometries or not; 0 by default.
    • spatial_index boolean flag: immediately building a Spatial Index or not; 0 by default.
    • text_dates boolean flag: interpreting DBF dates as plaintext or not: 0 by default (i.e. as Julian Day).
Will return the total number of imported rows.
NULL will be returned on invalid arguments.
Please note well: this SQL function open the doors 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 effectively enable this function.
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) : 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 MULTUPOINT.
Will return the total number of exported rows.
NULL will be returned on invalid arguments.
Please note well: this SQL function open the doors 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 effectively 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 ] ] ) : 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).
Will return the total number of imported rows.
NULL will be returned on invalid arguments.
Please note well: this SQL function open the doors 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 effectively enable this function.
ExportDBF ExportDBF( table Text , filename Text , charset 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
Will return the total number of exported rows.
NULL will be returned on invalid arguments.
Please note well: this SQL function open the doors 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 effectively 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 open the doors 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 effectively enable this function.
ExportGeoJSON 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 open the doors 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 effectively enable this function.
ImportXLS ImportXLS( filename Text , table Text ) : Integer
ImportXLS( filename Text , table Text [ , worksheet_index Integer [ , first_line_titles Integer ] ] ) : Integer		 Will import an external spreadsheet file (Microsoft .xls format) into an internal Table:
  • Mandatory arguments:
    • filename absolute or relative path leading to the spreadsheet file.
    • table name of the table to be created.
  • Optional arguments:
    • worksheet_index a positive integer selecting the target worksheet in the case of a multi-sheet: 0 by default..
    • first_line_titles boolean flag: the first line of the worksheet contains column names or not: 0 by default.
Will return the total number of imported rows.
NULL will be returned on invalid arguments.
Please note well: this SQL function open the doors 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 effectively enable this function.
ImportWFS ImportWFS( filename_or_url Text , layer_name Text , table Text ) : Integer
ImportXLS( 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 open the doors 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 effectively 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 open the doors 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 effectively 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 open the doors 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 effectively 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 open the doors 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 effectively 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 could be indifferently located on the MAIN database or on any other attached DB, and is fully identified by the input-db-prefix, input-table and input-geometry arguments.
  • The Blade dataset is always expected to declare a Primary Key and must declare a POLYGON or MULTIPOLYGON Geometry type.
    It could be indifferently located on the MAIN database or on 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 share the same identical SRID value.
  • The Output dataset will be always located into the MAIN database.
    The Output Geometry will always preserve the name and SRID declared by input-geom: the Geometry type will be always be one between 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 legitimately declared as NULL, and in this case the MAIN database will be always assumed.
  • and also the input-geom and blade-geom arguments could be legitimately declared as NULL.
    In this case the appropriate Geometry column name will be implicitly determined, but only if the corresponding table declares just a single Geometry column, 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.
back