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
| Task | PostGIS 2.0 | SpatiaLite 4.0 | OGC Compliance |
|---|---|---|---|
| Construct POINT from coordinates | ST_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 coordinates | ST_MakePoint(x dp, y dp, z dp) | MakePointZ(x dp, y dp, z dp[, SRID int]) | Non-compliant |
| Construct 3DM POINT from coordinates | ST_MakePointM(x dp, y dp, m dp) | MakePointM(x dp, y dp, m dp[, SRID int]) | Non-compliant |
| Construct 4D POINT from coordinates | ST_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 Binary | ST_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 |
