css.php

Spatial SQL Concordance

The following tables represent an (incomplete) attempt to map some commonly used spatial functions among major open source database and the SQL/MM Spatial specification. I am not attempting to be exhaustive, but merely provide some practical information to assist myself in others in writing implementation-agnostic SQL. If others want to contribute to the concordance with information about other FOSS RDBMSs (particularly MySQL Spatial), please feel free to contact me.

Geometry Constructors - (MULTI)POINTs

TaskPostGIS 2.0SpatiaLite 4.0OGC Compliance
Construct POINT from coordinatesST_Point(x dp, y dp)
ST_MakePoint(x dp, y dp)
ST_Point(x dp, y dp)
MakePoint(x dp, y dp[, SRID int])
ST_Point(...)
--SQL-MM 3: 6.1.2
Construct 3DZ POINT from coordinatesST_MakePoint(x dp, y dp, z dp)MakePointZ(x dp, y dp, z dp[, SRID int])Non-compliant
Construct 3DM POINT from coordinatesST_MakePointM(x dp, y dp, m dp)MakePointM(x dp, y dp, m dp[, SRID int])Non-compliant
Construct 4D POINT from coordinatesST_MakePoint(x dp, y dp, z dp, m dp)MakePointZM(x dp, y dp, z dp, m dp[, SRID int])Non-compliant
--Contrary to the PostGIS documentation, the previous four functions do not require x and y to be longitude and latitude.
--SRID cannot be passed as a parameter (as in SpatiaLite and the SQL/MM specification), and must be assigned with ST_Set_SRID().
--Generally faster and more precise than ST_GeomFromText and ST_PointFromText
Construct POINT from Well-Known Text

Ex: 'POINT(1 2)'
ST_PointFromText(WKT text[, SRID int])
--Slower than ST_GeomFromText()
[ST_]PointFromText(WKT text[, SRID int])
ST_PointFromText(...)
--SQL-MM 3: 6.1.8
Construct (MULTI)POINT from Well-Known Text

Ex: 'MULTIPOINT(1 2, 3 4)'
ST_MPointFromText(WKT text[, SRID int])
--Slower than ST_GeomFromText()
[ST_]M[ulti]PointFromText(WKT text[, SRID int])ST_MPointFromText(...)
--SQL-MM 3: 9.2.4
Construct POINT from Well-Known BinaryST_PointFromWKB(WKB bytea[, SRID int])
[ST_]PointFromWKB(WKB binary[, SRID int])
ST_PointFromWKB(...)
--SQL-MM 3: 6.1.9
Construct MULTIPOINT from Well-Known Binary?[ST_]M[ulti]PointFromWKB(WKB binary[, SRID int])ST_MPointFromWKB(...)
--SQL-MM 3: 9.2.5