SpatiaLite 3.0.0          SQL functions reference list

back

SQL Version Info functions

Function Syntax Summary
spatialite_version spatialite_version( void ) : String returns the current SpatiaLite version as a text string
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

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
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
Round Round( x Double precision ) : Double precision returns the integer value nearest to x
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 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 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

SQL utility functions [non-standard] for geometric objects

Function Syntax OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
GeomFromExifGpsBlob GeomFromExifGpsBlob( image BLOB ) : Geometry X X X 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
MakePoint MakePoint( x Double precision , y Double precision , [ , SRID Integer] ) : Geometry X X X a Geometry will be returned representing the POINT defined by [x y] coordinates
MakeLine MakeLine( pt1 PointGeometry , pt2 PointGeometry ) : LinestringGeometry X X X 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 X X X 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
BuildMbr BuildMbr( x1 Double precision , y1 Double precision , x2 Double precision , y2 Double precision [ , SRID Integer] ) : Geometry X X X [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 X X X [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 X X X return a geometric object representing the bounding box that encloses a set of input values
aggregate function
MbrMinX MbrMinX( geom Geometry) : Double precision X X X return the x-coordinate for geom MBR's leftmost side as a double precision number
MbrMinY MbrMinY( geom Geometry) : Double precision X X X return the y-coordinate for geom MBR's lowermost side as a double precision number
MbrMaxX MbrMaxX( geom Geometry) : Double precision X X X return the x-coordinate for geom MBR's rightmost side as a double precision number
MbrMaxY MbrMaxY( geom Geometry) : Double precision X X X return the y-coordinate for geom MBR's uppermost side as a double precision number

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

Function Syntax OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
GeomFromText GeomFromText( wkt String [ , SRID Integer] ) : Geometry
ST_GeomFromText( wkt String [ , SRID Integer] ) : Geometry
X X X X construct a geometric object given its Well-known text Representation
PointFromText PointFromText( wktPoint String [ , SRID Integer] ) : Point
ST_PointFromText( wktPoint String [ , SRID Integer] ) : Point
X X X X 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 X X X 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 X X X 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 X X X 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 X X X 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 X X X 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 X X X construct a GeometryCollection
BdPolyFromText BdPolyFromText( wktMultilinestring String [ , SRID Integer] ) : Polygon
ST_BdPolyFromText( wktMultilinestring String [ , SRID Integer] ) : Polygon
X X X X 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 X X X 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 OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
GeomFromWKB GeomFromWKB( wkbGeometry Binary [ , SRID Integer] ) : Geometry
ST_GeomFromWKB( wkbGeometry Binary [ , SRID Integer] ) : Geometry
X X X X construct a geometric object given its Well-known binary Representation
PointFromWKB PointFromWKB( wkbPoint Binary [ , SRID Integer] ) : Point
ST_PointFromWKB( wkbPoint Binary [ , SRID Integer] ) : Point
X X X X 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 X X X 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 X X X 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 X X X 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 X X X 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 X X X 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 X X X construct a GeometryCollection
BdPolyFromWKB BdPolyFromWKB( wkbMultilinestring Binary [ , SRID Integer] ) : Polygon
ST_BdPolyFromWKB( wkbMultilinestring Binary [ , SRID Integer] ) : Polygon
X X X X 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 X X X 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 OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
AsText AsText( geom Geometry ) : String
ST_AsText( geom Geometry ) : String
X X X X returns the Well-known Text representation
AsWKT AsWKT( geom Geometry [ , precision Integer ] ) : String X X X returns the Well-known Text representation
always return strictly conformant 2D WKT
AsBinary AsBinary( geom Geometry ) : Binary
ST_AsBinary( geom Geometry ) : Binary
X X X X returns the Well-known Binary representation

SQL functions supporting exotic geometric formats

Function Syntax OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
AsSVG AsSVG( geom Geometry [ , relative Integer [ , precision Integer ] ] ) : String X X X returns the SVG [Scalable Vector Graphics] representation
AsKml AsKml( geom Geometry [ , precision Integer ] ) : String
AsKml( name String, description String, geom Geometry [ , precision Integer ] ) : String
X X X 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 X X X construct a geometric object given its KML Representation
AsGml AsGml( geom Geometry [ , precision Integer ] ) : String
AsGml( version Integer, geom Geometry [ , precision Integer ] ) : String
X X X 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 X X X construct a geometric object given its GML Representation
AsGeoJSON AsGeoJSON( geom Geometry [ , precision Integer [ , options Integer ] ] ) : String X X X 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 X X X construct a geometric object given its GeoJSON Representation
AsEWKB AsEWKB( geom Geometry ) : String X X X returns the EWKB [Extended Well Known Binary] representation (PostGIS compatibility)
GeomFromEWKB GeomFromEWKB( ewkbGeometry String ) : Geometry X X X construct a geometric object given its EWKB Representation
AsEWKT AsEWKT( geom Geometry ) : String X X X returns the EWKT [Extended Well Known Text] representation (PostGIS compatibility)
GeomFromEWKT GeomFromEWKT( ewktGeometry String ) : Geometry X X X construct a geometric object given its EWKT Representation
AsFGF AsFGF( geom Geometry ) : Binary X X X returns the FGF [FDO Geometry Binary Format] representation
GeomFromFGF GeomFromFGF( fgfGeometry Binary [ , SRID Integer] ) : Geometry X X X construct a geometric object given its FGF binary Representation

SQL functions on type Geometry

Function Syntax OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
Dimension Dimension( geom Geometry ) : Integer
ST_Dimension( geom Geometry ) : Integer
X X X X 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 X X X returns the dimension model used by the geometric object as:
'XY', 'XYZ', 'XYM' or 'XYZM'
GeometryType GeometryType( geom Geometry ) : String
ST_GeometryType( geom Geometry ) : String
X X X X 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 X X X returns the Spatial Reference System ID for this geometric object
SetSRID SetSRID( geom Geometry , SRID Integer ) : Integer X X X 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 X X X 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 X 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
X 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
Boundary Boundary( geom Geometry ) : Geometry
ST_Boundary( geom Geometry ) : Geometry
X X 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 X X X 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)].

SQL functions attempting to repair malformed Geometries

Function Syntax OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
SanitizeGeometry SanitizeGeometry( geom Geometry ) : geom Geometry X 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 OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
CompressGeometry CompressGeometry( geom Geometry ) : geom Geometry X 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 X returns an uncompressed Geometry [if a valid Geometry was supplied], or NULL in any other case

SQL Geometry-type casting functions

Function Syntax OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
CastToPoint CastToPoint( geom Geometry ) : geom Geometry X 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 X 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 X 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 X 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 X 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 X 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 X 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
X 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 X 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 OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
CastToXY CastToXY( geom Geometry ) : geom Geometry X returns a Geometry using the [XY] space dimension
CastToXYZ CastToXYZ( geom Geometry ) : geom Geometry X returns a Geometry using the [XYZ] space dimension
CastToXYM CastToXYM( geom Geometry ) : geom Geometry X returns a Geometry using the [XYZM] space dimension
CastToXYZM CastToXYZM( geom Geometry ) : geom Geometry X returns a Geometry using the [XYZM] space dimension

SQL functions on type Point

Function Syntax OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
X X( pt Point ) : Double precision
ST_X( pt Point ) : Double precision
X X X X 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 X X X 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 X X X 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 X X X 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 OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
StartPoint StartPoint( c Curve ) : Point
ST_StartPoint( c Curve ) : Point
X X X X return a Point containing the first Point of c
EndPoint EndPoint( c Curve ) : Point
ST_EndPoint( c Curve ) : Point
X X X X 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 X return the length of c
Geodesic Length GeodesicLength( c Curve ) : Double precision X 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 intrinsecally slow and time consuming task
Great Circle Length GreatCircleLength( c Curve ) : Double precision X 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 then 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 X X X 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 X 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 X return a Point guaranteed to lie on the Surface (or Curve)
Simplify Simplify( c Curve , tolerance Double precision ) : Curve
ST_Generalize( c Curve , tolerance Double precision ) : Curve
X return a geometric object representing a simplified version of c applying the Douglas-Peukert algorithm with given tolerance
SimplifyPreserveTopology SimplifyPreserveTopology( c Curve , tolerance Double precision ) : Curve X return a geometric object representing a simplified version of c applying the Douglas-Peukert algorithm with given tolerance and respecting topology

SQL functions on type LineString

Function Syntax OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
NumPoints NumPoints( line LineString ) : Integer
ST_NumPoints( line LineString ) : Integer
X X X X return the number of Points in the LineString
PointN PointN( line LineString , n Integer ) : Point
ST_PointN( line LineString , n Integer ) : Point
X X X X return a Point containing Point n of line

SQL functions on type Surface [Polygon or Ring]

Function Syntax OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
Centroid Centroid( s Surface ) : Point
ST_Centroid( s Surface ) : Point
X X return the centroid of s, which may lie outside s
Area Area( s Surface ) : Double precision
ST_Area( s Surface ) : Double precision
X X return the area of s

SQL functions on type Polygon

Function Syntax OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
ExteriorRing ExteriorRing( polyg Polygon ) : LineString
ST_ExteriorRing( polyg Polygon ) : LineString
X X X X return the exteriorRing of p
NumInteriorRing
NumInteriorRings
NumInteriorRing( polyg Polygon ) : Integer
NumInteriorRings( polyg Polygon ) : Integer
ST_NumInteriorRing( polyg Polygon ) : Integer
X X X X return the number of interiorRings
InteriorRingN InteriorRingN( polyg Polygon , n Integer ) : LineString
ST_InteriorRingN( polyg Polygon , n Integer ) : LineString
X X X X return the nth interiorRing. The order of Rings is not geometrically significant.

SQL functions on type GeomCollection

Function Syntax OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
NumGeometries NumGeometries( geom GeomCollection ) : Integer
ST_NumGeometries( geom GeomCollection ) : Integer
X X X X return the number of individual Geometries
GeometryN GeometryN( geom GeomCollection , n Integer ) : Geometry
ST_GeometryN( geom GeomCollection , n Integer ) : Geometry
X X X X return the nth geometric object in the collection. The order of the elements in the collection is not geometrically significant.

SQL functions that test approximative spatial relationships via MBRs

Function Syntax OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
MbrEqual MbrEqual( geom1 Geometry , geom2 Geometry ) : Integer X X X 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 X X X 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 X X X 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 X X X 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 X X X 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 X X X 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
MbrContains MbrContains( geom1 Geometry , geom2 Geometry ) : Integer X X X 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 OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
Equals Equals( geom1 Geometry , geom2 Geometry ) : Integer
ST_Equals( geom1 Geometry , geom2 Geometry ) : Integer
X X 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 X 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 X 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 X 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 X 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 X 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 X 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 X 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
X 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
X 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 X 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 OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
Distance Distance( geom1 Geometry , geom2 Geometry ) : Double precision
ST_Distance( geom1 Geometry , geom2 Geometry ) : Double precision
X X return the distance between geom1 and geom2
PtDistWithin PtDistWithin( geom1 Geometry , geom2 Geometry, range Double precision [, use_spheroid Integer ] ) : Integer X return TRUE (1) if the distance between geom1 and geom2 is within the given range.
Usually distances are expressed in the length unit correspondind 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 OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
Intersection Intersection( geom1 Geometry , geom2 Geometry ) : Geometry
ST_Intersection( geom1 Geometry , geom2 Geometry ) : Geometry
X X 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 X 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 X return a geometric object that is the set union of geom1 and geom2
GUnion GUnion( geom Geometry ) : Geometry
ST_Union( geom Geometry ) : Geometry
X X 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 X 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 ) : Geometry
ST_Buffer( geom Geometry , dist Double precision ) : Geometry
X X 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
ConvexHull ConvexHull( geom Geometry ) : Geometry
ST_ConvexHull( geom Geometry ) : Geometry
X X return a geometric object that is the convex hull of geom

SQL functions that implement spatial operators
[GEOS advanced features]

OffestCurve OffsetCurve( geom Curve , radius Double precision , left_or_right Integer ) : Curve
ST_OffsetCurve( geom Curve , radius Double precision , left_or_right Integer ) : Curve
X return a geometric object representing the corresponding left- (or right-sided) 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
X 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
X 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
X 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_Locate_Point Line_Locate_Point( line Curve , point Point ) : Double precision
ST_Line_Locate_Point( line Curve , point Point ) : Double precision
X 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
X 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 ClosesetPoint( geom1 Geometry , geom2 Geometry ) : Point
ST_ClosesetPoint( geom1 Geometry , geom2 Geometry ) : Point
X 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
X 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
X 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
X 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
X 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
X 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
X 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
X Exactly the same as ST_BuildArea, but implemented as an aggregate function.
NULL will be returned if any error is encountered
UnaryUnion UnaryUnion( geom Geometry ) : Geometry
ST_UnaryUnion( geom Geometry ) : Geometry
X 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
X 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
X 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
X 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
CollectionExtract CollectionExtract( geom Geometry , type Integer ) : Geometry
ST_CollectionExtract( geom Geometry , type Integer ) : Geometry
X Given a GEOMETRYCOLLECTION, returns a MULTI* 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)

SQL functions for coordinate transformations

Function Syntax OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
Transform Transform( geom Geometry , newSRID Integer ) : Geometry
ST_Transform( geom Geometry , newSRID Integer ) : Geometry
X return a geometric object obtained by reprojecting coordinates into the Reference System identified by newSRID
SridFromAuthCRS SridFromAuthCRS( auth_name String , auth_SRID Integer ) : Integer 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
return a geometric object obtained by translating coordinates according to shiftX and shiftY values
ScaleCoords
ScaleCoordinates
ScaleCoords( geom Geometry , scaleX Double precision [ , scaleY Double precision ] ) : Geometry
ScaleCoordinates( geom Geometry , scaleX Double precision [ , scaleY Double precision ] ) : Geometry
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
return a geometric object obtained by rotating coordinates according to angleInDegrees value
ReflectCoords
ReflectCoordinates
ReflectCoords( geom Geometry , xAxis Integer , yAxis Integer ) : Geometry
ReflectCoordinates( geom Geometry , xAxis Integer , yAxis Integer ) : Geometry
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
return a geometric object obtained by swapping x- and y-coordinates

SQL functions for Spatial-MetaData and Spatial-Index handling

Function Syntax OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
InitSpatialMetaData InitSpatialMetaData( void ) : Integer X X X 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
direct invocation of these function is discouraged; you have to run the init_spatialite.sql script in order to fully initialize the Spatial MetaData tables
AddGeometryColumn AddGeometryColumn( table String , column String , srid Integer , geom_type String , dimension String [ , not_null Integer ] ) : Integer X X X X 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'
  • 'LINESTRING'
  • 'POLYGON'
  • 'MULTIPOINT'
  • 'MULTILINESTRING'
  • 'MULTIPOLYGON'
  • 'GEOMETRYCOLLECTION'
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': 3D points, identified by X, Y and Z coordinates. Each point stores an M-value (measure) as well

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 X X X 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 X X X 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
CreateSpatialIndex CreateSpatialIndex( table String , column String ) : Integer X X X 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
CreateMbrCache CreateMbrCache( table String , column String ) : Integer X X X 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 X X X 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
CheckSpatialIndex CheckSpatialIndex( void ) : Integer
CheckSpatialIndex( table String , column String ) : Integer
X X X 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 exists
RecoverSpatialIndex RecoverSpatialIndex( [ no_check : Integer ] ) : Integer
RecoverSpatialIndex( table String , column String [ , no_check : Integer ] ) : Integer
X X X 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 anyway

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 exists
UpdateLayerStatistics UpdateLayerStatistics( [ void ) : Integer
UpdateLayerStatistics( table String [ , column String ] ) : Integer
X X X Updates the internal Layer Statistics [Feature Count and Total Extent]
  • if no arguments are passed, then internal statics 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
CreateTopologyTables CreateTopologyTables( SRID Integer , dims : String ) : Integer
CreateTopologyTables( prefix String , SRID Integer , dims : String ) : Integer
X X X Creates a set of Topology tables
  • SRID argument is mandatory
  • dims argument must be 'XY' or 'XYZ':
    2 or 3 are valid aliases
  • the optional argument prefix can be used to support more Topology sets on the same DB:
    if omitted a "topo_" prefix will be assumed by default

the return type is Integer, with a return value of 1 for TRUE (success) or 0 for FALSE (failure)

SQL functions implementing FDO/OGR compatibily

Function Syntax OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
CheckSpatialMetaData CheckSpatialMetaData( void ) : Integer X X X Checks the Spatial Metadata type, then returning:
  • 0 - if geometry_columns and spatial_ref_sys tables does not exists
  • 1 - if both tables exist, and their layout is the one used by SpatiaLite
  • 2 - if both tables exist, and their layout is the one used by FDO/OGR
AutoFDOStart AutoFDOStart( void ) : Integer X X X 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 X X X 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 X X X 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 SpatiaLite's own
AddFDOGeometryColumn AddFDOGeometryColumn( table String , column String , srid Integer , geom_type Integer , dimension Integer, geometry_format String ) : Integer X X X 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 X X X 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 X X X 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 for MbrCache-based queries

Function Syntax OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
FilterMbrWithin FilterMbrWithin( x1 Double precision , y1 Double precision , x2 Double precision , y2 Double precision ) X X X 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 ) X X X 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 ) X X X 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 ) X X X 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 OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
RTreeIntersects RTreeIntersects( x1 Double precision , y1 Double precision , x2 Double precision , y2 Double precision ) X X X 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 ) X X X Deprecated function
Still maintained so to avoid backward compatility issues, but now simply is an alias-name for RTreeIntersects
RTreeContains RTreeContains( x1 Double precision , y1 Double precision , x2 Double precision , y2 Double precision ) X X X Deprecated function
Still maintained so to avoid backward compatility issues, but now simply is an alias-name for RTreeIntersects
RTreeDistWithin RTreeDistWithin( x Double precision , y Double precision , radius Double precision ) X X X Retrieves from an R*Tree Spatial Index any entity whose MBR intersect the square circumscribed on the given circle (x y center, radius)
back