More about Spatial SQL: WKT and WKB 2011 January 28

 SpatiaLite supports a Geometry data type conformant to the international standard OGC-SFS (Open Geospatial Consortium - Simple Feature SQL). http://www.opengeospatial.org/standards/sfs Geometry is an abstract data type with seven related concrete sub-classes. You cannot directly instantiate a Geometry (because this one is and abstract class, and doesn't corresponds to any actual implementation): but you can freely instantiate any related sub-class.

 Sub-Class Example POINT LINESTRING POLYGON MULTIPOINT MULTILINESTRING MULTIPOLYGON GEOMETRYCOLLECTION Any arbitrary collection of elementary sub-classes. Please note: for some odd reason this one seems to be the sub-class absolutely beloved by inexperienced beginners: all them are fond of GEOMETRYCOLLECTION: GEOMETRYCOLLECTION isn't supported by the Shapefile format. And this sub-class isn't generally supported by ordinary GIS sw (viewers and so on). So it's very rarely used in the real GIS professional world.

 WKT and WKT notations Geometry is a very complex data type: accordingly to this, OGC-SFS defines two alternative standard notations allowing to represent Geometry values: the WKT (Well Known Text) notation is intended to be user friendly (not really so user friendly after all, but at least human readable). the WKB (Well Known Binary) notation on the other side is more intended for precise and accurate import/export/exchange of Geometries between different platforms.

Dimension: XY (2D) the most oftenly used ...
 Geometry Type WKT example POINT POINT(123.45 543.21) LINESTRING LINESTRING(100.0 200.0, 201.5 102.5, 1234.56 123.89) three vertices POLYGON POLYGON((101.23 171.82, 201.32 101.5, 215.7 201.953, 101.23 171.82)) exterior ring, no interior rings POLYGON((10 10, 20 10, 20 20, 10 20, 10 10), (13 13, 17 13, 17 17, 13 17, 13 13)) exterior ring, one interior ring MULTIPOINT MULTIPOINT(1234.56 6543.21, 1 2, 3 4, 65.21 124.78) three points MULTILINESTRING MULTILINESTRING((1 2, 3 4), (5 6, 7 8, 9 10), (11 12, 13 14)) first and last linestrings have 2 vertices each one; the second linestring has 3 vertices MULTIPOLYGON MULTIPOLYGON(((0 0,10 20,30 40,0 0),(1 1,2 2,3 3,1 1)), ((100 100,110 110,120 120,100 100))) two polygons: the first one has an interior ring GEOMETRYCOLLECTION GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(4 5, 6 7, 8 9), POINT(30 30))

XYZ (3D)
 Geometry Type WKT example POINT POINTZ(13.21 47.21 0.21) LINESTRING LINESTRINGZ(15.21 57.58 0.31, 15.81 57.12 0.33) POLYGON ... MULTIPOINT MULTIPOINTZ(15.21 57.58 0.31, 15.81 57.12 0.33) MULTILINESTRING ... MULTIPOLYGON ... GEOMETRYCOLLECTION GEOMETRYCOLLECTIONZ(POINTZ(13.21 47.21 0.21), LINESTRINGZ(15.21 57.58 0.31, 15.81 57.12 0.33))

Dimension: XYM (2D + Measure)
Please note: this one has nothing to do with 3D.
M is a measure value, not a geometry dimension.
 Geometry Type WKT example POINT POINTM(13.21 47.21 1000.0) LINESTRING LINESTRINGM(15.21 57.58 1000.0, 15.81 57.12 1100.0) POLYGON ... MULTIPOINT MULTIPOINTM(15.21 57.58 1000.0, 15.81 57.12 1100.0) MULTILINESTRING ... MULTIPOLYGON ... GEOMETRYCOLLECTION GEOMETRYCOLLECTIONM(POINTM(13.21 47.21 1000.0), LINESTRINGM(15.21 57.58 1000.0, 15.81 57.12 1100.0))

XYZM (3D + Measure)
Please note: M is a measure value, not a geometry dimension.
 Geometry Type WKT example POINT POINTZM(13.21 47.21 0.21 1000.0) LINESTRING LINESTRINGZM(15.21 57.58 0.31 1000.0, 15.81 57.12 0.33 1100.0) POLYGON ... MULTIPOINT MULTIPOINTZM(15.21 57.58 0.31 1000.0, 15.81 57.12 0.33 1100.0) MULTILINESTRING ... MULTIPOLYGON ... GEOMETRYCOLLECTION GEOMETRYCOLLECTIONZM(POINTZM(13.21 47.21 0.21 1000.0), LINESTRINGZM(15.21 57.58 0.31 1000.0, 15.81 57.12 0.33 1100.0))

 There are several Spatial SQL functions supporting WKT and WKB handling; examining all them one by one will surely be absolutely boring (and not really useful for the average user). So we'll briefly explore just the main (and most often used) ones.

 SELECT Hex(ST_GeomFromText('POINT(1.2345 2.3456)')); 0001FFFFFFFF8D976E1283C0F33F16FBCBEEC9C302408D976E1283C0F33F16FBCBEEC9C302407C010000008D976E1283C0F33F16FBCBEEC9C30240FE SELECT ST_AsText(x'0001FFFFFFFF8D976E1283C0F33F16FBCBEEC9C302408D976E1283C0F33F16FBCBEEC9C302407C010000008D976E1283C0F33F16FBCBEEC9C30240FE'); POINT(1.2345 2.3456) SELECT Hex(ST_AsBinary(x'0001FFFFFFFF8D976E1283C0F33F16FBCBEEC9C302408D976E1283C0F33F16FBCBEEC9C302407C010000008D976E1283C0F33F16FBCBEEC9C30240FE')); 01010000008D976E1283C0F33F16FBCBEEC9C30240 SELECT Hex(ST_AsBinary(ST_GeomFromText('POINT(1.2345 2.3456)'))); 01010000008D976E1283C0F33F16FBCBEEC9C30240 SELECT ST_AsText(ST_GeomFromWKB(x'01010000008D976E1283C0F33F16FBCBEEC9C30240')); POINT(1.2345 2.3456)

Please note well: both WKT and WKB notations are intended to support standard data exchange (import/export);
anyway the actual format internally used by SpatiaLite is a different one, i.e. BLOB Geometry.
You must never be concerned about such internal format:
you simply have to use the appropriate conversion functions so to convert back and forth in standard WKT or WKB.
• the Hex() function is a standard SQL function allowing to represent binary values as hexadecimal encoded text strings.
• the Spatial SQL function ST_GeomFromText() converts any valid WKT expression into an internal BLOB Geometry value.
• ST_GeomFromWKB() converts any valid WKB expression into an internal BLOB Geometry value.
• ST_AsText() converts an internal BLOB Geometry value into the corresponding WKT expression.
• ST_AsBinary() converts an internal BLOB Geometry value into the corresponding WKB expression.

 SELECT ST_GeometryType(ST_GeomFromText('POINT(1.2345 2.3456)')); POINT SELECT ST_GeometryType(ST_GeomFromText('POINTZ(1.2345 2.3456 10)')); POINT Z SELECT ST_GeometryType(ST_GeomFromText('POINT ZM(1.2345 2.3456 10 20)')); POINT ZM
ST_GeometryType() will return the Geometry Type from the given internal BLOB Geometry value.
• Please note: when using not-2D dimensions, declaring e.g. 'POINTZ' or 'POINT Z' is absolutely the same: SpatiaLite understands both notations indifferently.

 SELECT ST_Srid(ST_GeomFromText('POINT(1.2345 2.3456)')); -1 SELECT ST_Srid(ST_GeomFromText('POINT(1.2345 2.3456)', 4326)); 4326
ST_Srid() will return the SRID from the given internal BLOB Geometry> value.
• Please note: both ST_GeomFromText() and ST_GeomFromWKB() accept an optional SRID argument.
If the SRID is unspecified (not at all a good practice), then -1 is assumed.

 Common pitfalls "I've declared a MULTIPOINT-type Geometry column; now I absolutely have to insert a simple POINT into this table, but I get a constraint failed error ..." Any MULTIxxxxx type can store a single elementary item: you simply have to use the appropriate WKT syntax. And anyway several useful type casting functions exist.

 SELECT ST_GeometryType(ST_GeomFromText('MULTIPOINT(1.2345 2.3456)')); MULTIPOINT SELECT ST_AsText(CastToMultiLineString(ST_GeomFromText('LINESTRING(1.2345 2.3456, 12.3456 23.4567)'))); MULTILINESTRING((1.2345 2.3456, 12.3456 23.4567)) SELECT ST_AsText(CastToXYZM(ST_GeomFromText('POINT(1.2345 2.3456)'))); POINT ZM(1.2345 2.3456 0 0) SELECT ST_AsText(CastToXY(ST_GeomFromText('POINT ZM(1.2345 2.3456 10 20)'))); POINT(1.2345 2.3456)

 Author: Alessandro Furieri a.furieri@lqt.it This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license. Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.