Note: these pages are no longer maintained

Never the less, much of the information is still relevant.
Beware, however, that some of the command syntax is from older versions, and thus may no longer work as expected.
Also: external links, from external sources, inside these pages may no longer function.

	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
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)

Previous Slide

Table of Contents

Next Slide

	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.