SpatiaLite logo

More about Spatial SQL: WKT and WKB

2011 January 28

Previous Slide Table of Contents Next Slide

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 point example
LINESTRING linestring example
POLYGON polygon example
MULTIPOINT multipoint example
MULTILINESTRING multilinestring example
MULTIPOLYGON multipolygon example
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.
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.
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.

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)

Previous Slide Table of Contents Next Slide

CC-BY-SA logo Author: Alessandro Furieri a.furieri@lqt.it
This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

GNU logo 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.