SpatiaLite SQL functions reference list

back to main page

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 X X X X construct a geometric object given its Well-known text Representation
PointFromText PointFromText( wktPoint String [ , SRID Integer] ) : Point X X X X construct a Point
LineFromText
LineStringFromText
LineFromText( wktLineString String [ , SRID Integer] ) : Linestring
LineStringFromText( wktLineString String [ , SRID Integer] ) : Linestring
X X X X construct a Linestring
PolyFromText
PolygonFromText
PolyFromText( wktPolygon String [ , SRID Integer] ) : Polygon
PolygonFromText( wktPolygon String [ , SRID Integer] ) : Polygon
X X X X construct a Polygon
MPointFromText
MultiPointFromText
MPointFromText( wktMultiPoint String [ , SRID Integer] ) : MultiPoint
MultiPointFromText( wktMultiPoint String [ , SRID Integer] ) : MultiPoint
X X X X construct a MultiPoint
MLineFromText
MultiLineStringFromText
MLineFromText( wktMultiLineString String [ , SRID Integer] ) : MultiLinestring
MultiLineStringFromText( wktMultiLineString String [ , SRID Integer] ) : MultiLinestring
X X X X construct a MultiLinestring
MPolyFromText
PolygonFromText
MPolyFromText( wktMultiPolygon String [ , SRID Integer] ) : MultiPolygon
MultiPolygonFromText( wktMultiPolygon String [ , SRID Integer] ) : MultiPolygon
X X X X construct a MultiPolygon
GeomCollFromText
GeometryCollectionFromText
GeomCollFromText( wktGeometryCollection String [ , SRID Integer] ) : GeometryCollection
GeometryCollectionFromText( wktGeometryCollection String [ , SRID Integer] ) : GeometryCollection
X X X X construct a GeometryCollection

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 X X X X construct a geometric object given its Well-known binary Representation
PointFromWKB PointFromWKB( wkbPoint Binary [ , SRID Integer] ) : Point X X X X construct a Point
LineFromWKB
LineStringFromWKB
LineFromWKB( wkbLineString Binary [ , SRID Integer] ) : Linestring
LineStringFromText( wkbLineString Binary [ , SRID Integer] ) : Linestring
X X X X construct a Linestring
PolyFromWKB
PolygonFromWKB
PolyFromWKB( wkbPolygon Binary [ , SRID Integer] ) : Polygon
PolygonFromWKB( wkbPolygon Binary [ , SRID Integer] ) : Polygon
X X X X construct a Polygon
MPointFromWKB
MultiPointFromWKB
MPointFromWKB( wkbMultiPoint Binary [ , SRID Integer] ) : MultiPoint
MultiPointFromWKB( wkbMultiPoint Binary [ , SRID Integer] ) : MultiPoint
X X X X construct a MultiPoint
MLineFromWKB
MultiLineStringFromWKB
MLineFromWKB( wkbMultiLineString Binary [ , SRID Integer] ) : MultiLinestring
MultiLineStringFromWKB( wkbMultiLineString Binary [ , SRID Integer] ) : MultiLinestring
X X X X construct a MultiLinestring
MPolyFromWKB
PolygonFromWKB
MPolyFromWKB( wkbMultiPolygon Binary [ , SRID Integer] ) : MultiPolygon
MultiPolygonFromWKB( wkbMultiPolygon Binary [ , SRID Integer] ) : MultiPolygon
X X X X construct a MultiPolygon
GeomCollFromWKB
GeometryCollectionFromWKB
GeomCollFromWKB( wkbGeometryCollection Binary [ , SRID Integer] ) : GeometryCollection
GeometryCollectionFromWKB( wkbGeometryCollection Binary [ , SRID Integer] ) : GeometryCollection
X X X X construct a GeometryCollection

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 X X X X returns the Well-known Text representation
AsBinary AsBinary( geom Geometry ) : Binary X X X X returns the Well-known Binary representation

SQL functions on type Geometry

Function Syntax OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
Dimension 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
GeometryType 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 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 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 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 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 X X returns a geometric object that is the combinatorial boundary of g as defined in the Geometry Model
Envelope 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 on type Point

Function Syntax OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
X X( pt Point ) : Double precision X X X X return the x-coordinate of Point p as a double precision number
Y T( pt Point ) : Double precision X X X X return the y-coordinate of Point p as a double precision number

SQL functions on type Curve [Linestring or Ring]

Function Syntax OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
StartPoint StartPoint( c Curve ) : Point X X X X return a Point containing the first Point of c
EndPoint EndPoint( c Curve ) : Point X X X X return a Point containing the last Point of c
GLength GLength( c Curve ) : Double precision
OpenGis name for this function is Length(), but it conflicts with an SQLite reserved keyword
X X return the length of c
IsClosed 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 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.
Simplify Simplify( 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 Simplify( 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 X X X X return the number of Points in the LineString
PointN 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 X X return the centroid of s, which may lie outside s
PointOnSurface PointOnSurface( s Surface ) : Point X X return a Point guaranteed to lie on the Surface
Area 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 X X X X return the exteriorRing of p
NumInteriorRing
NumInteriorRings
NumInteriorRing( polyg Polygon ) : Integer
NumInteriorRings( polyg Polygon ) : Integer
X X X X return the number of interiorRings
InteriorRingN 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 X X X X return the number of interiorRings
GeometryN 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
BuildMbr BuildMbr( x1 Double precision , y1 Double precision , x2 Double precision , y2 Double precision) : 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) : 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
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
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 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 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 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 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 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 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 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 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
Relate 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 X X return the distance between geom1 and geom2

SQL functions that implement spatial operators

Function Syntax OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
Intersection 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 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
X X return a geometric object that is the set union of geom1 and geom2
SymDifference 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 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 X X return a geometric object that is the convex hull of geom

SQL functions for coordinate transformations

Function Syntax OpenGis
defined
SpatiaLite
base
PROJ
included
GEOS
included
Summary
Transform Transform( geom Geometry , newSRID Integer ) : Geometry X return a geometric object obtained by reprojecting coordinates into the Reference System identified by newSRID
ShiftCoords
ShiftCoordinates
ShiftCoords( geom Geometry , shiftX Double precision , shiftY Double precision ) : Geometry
ShiftCoordinates( geom Geometry , shiftX Double precision , shiftY Double precision ) : Geometry
X X X 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
X X X 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
X X X 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
X X X 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
X X X 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 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
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
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
DisableSpatialIndex DisableSpatialIndex( table String , column String ) : Integer X X X Disables an RTree Spatial Index, removing any related trigger
the return type is Integer, with a return value of 1 for TRUE or 0 for FALSE
back to main page