SpatiaLite Cookbook Topic: Commonly used ingredients

your first Spatial SQL queries

SpatiaLite is a Spatial DBMS, so it's now time to perform some Spatial SQL query.
There isn't absolutely nothing odd in Spatial SQL: it basically is exactly as standard SQL, but it supports the exotic data-type Geometry.
Usually you cannot directly query a Geometry value (as we've already seen they simply are a meaningless BLOB):
you are expected to use some appropriate spatial function to access a Geometry value in a meaningful way.

SELECT
cod_reg AS region_id,
regione AS region_name,
ST_Area(Geometry) AS geom_area
FROM reg2011_s;

The ST_Area(). function is one of such Spatial functions; usually you can easily recognize any Spatial function, simply because all them are ST_ prefixed.
This one is not an absolute rule, anyway: SpatiaLite is able to understand the alias name Area(). to identify the same function.
As the name itself states, this function computes the surface of the corresponding Geometry.

SELECT
cod_reg AS region_id,
regione AS region_name,
(ST_Area(Geometry) / 1000000.0) AS "Surface (sq.Km)"
FROM reg2011_s
ORDER BY 3 DESC;

As you surely noticed, the first query returned very high figures: this is because the current dataset uses meters as length unit, and consequently surfaces are measured in m².
But we simply have to apply an appropriate scale factor to get the most usual km² units.
Please note two SQL features we are introducing for the first time:

SQL isn't constrained to directly return the column value into the result-set: you can freely define any valid arithmetic expression as required.
Referencing a complex expression into some ORDER BY clause isn't too much practical:
but you can easily identify any column using its relative position (first column has index 1, and so on).

SELECT
cod_reg AS region_id,
regione AS region_name,
(ST_Area(Geometry) / 1000000.0) AS "Surface (sq.Km)",
(pop_2011 / (ST_Area(Geometry) / 1000000.0)) AS "Density: people / sq.Km"
FROM reg2011_s
ORDER BY 4 DESC;

And you can perform even more complex calculations in SQL.
This query will compute the population density (measured as people / km²).

All right, you have now acquired a basic SQL / Spatial SQL knowledge.
You are now ready to confront yourself with most complex and powerful queries: but this requires building a serious database.
Do you remember ? for now we where simply using Virtual Shapefiles tables; i.e. the faint imitation of real Spatial tables (internally stored).
So during the next steps we'll first create and populate a well designed DB (not a so trivial task), and then we'll come again to see most complex and sophisticated SQL queries.

more about Spatial SQL: WKT and WKB

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
a RING is a closed LINESTRING
(the first POINT must be the same as the last POINT)

POLYGON
1 exterior-RING (there can only be one)
forms the outer edge of the POLYGON

and a possibly:

1 (or more) interior-RING(s)
each forming a hole inside an exterior-RING
A POLYGON will be invalid when:

any part of an interior-RING is outside the exterior-RING
--> no longer being a hole, but a part of the outer edge

any part of an interior-RING overlaps another interior-RING
--> is no longer 2 holes, but 1 combined hole

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.

Sub-Class	Example
POINT
LINESTRING a RING is a closed LINESTRING (the first POINT must be the same as the last POINT)
POLYGON 1 exterior-RING (there can only be one) forms the outer edge of the POLYGON and a possibly: 1 (or more) interior-RING(s) each forming a hole inside an exterior-RING A POLYGON will be invalid when: any part of an interior-RING is outside the exterior-RING --> no longer being a hole, but a part of the outer edge any part of an interior-RING overlaps another interior-RING --> is no longer 2 holes, but 1 combined hole
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 [with extra formatting, which is otherwise never used]

POINT
1 pair of X/Y-Values (a vertice),
  separated with a 'space' POINT
(
123.45 543.21
)
containing two values

position 1: X-value

position 2: Y-value

separated with a 'space'

LINESTRING
list of POINTs (vertices),
  separated with a 'comma' LINESTRING
(
100.0 200.0, 201.5 102.5, 1234.56 123.89
)
three vertices, each separated with a 'comma'

POLYGON
a RING is a closed LINESTRING
(the first POINT must be the same as the last POINT)
1 (exterior-RING)
(always the first LINESTRING)

and a possibly
1 (or more) interior-RING(s)
(an interior-RING is a hole inside an exterior-RING)

list of RINGSs
separated with a 'comma'
A POLYGON will be invalid when:

missing exterior-RING [POLYGON is empty]

a RING is not closed

any part of an interior-RING is outside the exterior-RING

any part of an interior-RING overlaps another interior-RING

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
list of POINTs (vertices),
  separated with a 'comma' MULTIPOINT
(
1234.56 6543.21,
1 2,
3 4,
65.21 124.78
)
four points

MULTILINESTRING
list of LINESTRINGs
separated with a 'comma'

Note:
each LINESTRING is enclosed in brackets 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
list of POLYGONs
separated with a 'comma'

Note:
each POLYGON is enclosed in brackets 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 contains an interior-RING

GEOMETRYCOLLECTION
list of (MULTI-) POINT, LINESTRING or POLYGONs
separated with a 'comma' GEOMETRYCOLLECTION
(
POINT(1 1),
LINESTRING(4 5, 6 7, 8 9),
POINT(30 30)
)
two POINTs, 1 LINESTRING (which is the second GEOMETRY of the COLLECTION)

Geometry Type	WKT example [with extra formatting, which is otherwise never used]
POINT 1 pair of X/Y-Values (a vertice), separated with a 'space'	POINT ( 123.45 543.21 ) containing two values position 1: X-value position 2: Y-value separated with a 'space'
LINESTRING list of POINTs (vertices), separated with a 'comma'	LINESTRING ( 100.0 200.0, 201.5 102.5, 1234.56 123.89 ) three vertices, each separated with a 'comma'
POLYGON a RING is a closed LINESTRING (the first POINT must be the same as the last POINT) 1 (exterior-RING) (always the first LINESTRING) and a possibly 1 (or more) interior-RING(s) (an interior-RING is a hole inside an exterior-RING) list of RINGSs separated with a 'comma' A POLYGON will be invalid when: missing exterior-RING [POLYGON is empty] a RING is not closed any part of an interior-RING is outside the exterior-RING any part of an interior-RING overlaps another interior-RING	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 list of POINTs (vertices), separated with a 'comma'	MULTIPOINT ( 1234.56 6543.21, 1 2, 3 4, 65.21 124.78 ) four points
MULTILINESTRING list of LINESTRINGs separated with a 'comma' Note: each LINESTRING is enclosed in brackets	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 list of POLYGONs separated with a 'comma' Note: each POLYGON is enclosed in brackets	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 contains an interior-RING
GEOMETRYCOLLECTION list of (MULTI-) POINT, LINESTRING or POLYGONs separated with a 'comma'	GEOMETRYCOLLECTION ( POINT(1 1), LINESTRING(4 5, 6 7, 8 9), POINT(30 30) ) two POINTs, 1 LINESTRING (which is the second GEOMETRY of the COLLECTION)

Dimension: XYZ (3D, height/dem)

Geometry Type WKT example [without any extra formatting]

POINTZ
1 triplet of X/Y/Z-Values (a vertice),
  separated with a 'space' POINTZ(13.21 47.21 0.21)
containing three values

position 1: X-value

position 2: Y-value

position 3: Z-value (height/dem)

separated with a 'space'

LINESTRINGZ
same as LINESTRING,
  but containing POINTZ instead of POINT LINESTRINGZ(15.21 57.58 0.31, 15.81 57.12 0.33)

POLYGONZ
same as POLYGON,
  but containing POINTZ instead of POINT ...

MULTIPOINTZ
same as MULTIPOINT,
  but containing POINTZ instead of POINT MULTIPOINTZ(15.21 57.58 0.31, 15.81 57.12 0.33)

MULTILINESTRINGZ
same as MULTILINESTRING,
  but containing POINTZ instead of POINT ...

MULTIPOLYGONZ
same as MULTIPOLYGON,
  but containing POINTZ instead of POINT ...

GEOMETRYCOLLECTIONZ
same a GEOMETRYCOLLECTION,
  but containing:

(MULTI-) POINTZ instead of (MULTI-) POINT

(MULTI-) MULTILINESTRINGZ instead of (MULTI-) MULTILINESTRING

(MULTI-) POLYGONZ instead of (MULTI-) POLYGON

GEOMETRYCOLLECTIONZ(POINTZ(13.21 47.21 0.21),
LINESTRINGZ(15.21 57.58 0.31, 15.81 57.12 0.33))

Geometry Type	WKT example [without any extra formatting]
POINTZ 1 triplet of X/Y/Z-Values (a vertice), separated with a 'space'	POINTZ(13.21 47.21 0.21) containing three values position 1: X-value position 2: Y-value position 3: Z-value (height/dem) separated with a 'space'
LINESTRINGZ same as LINESTRING, but containing POINTZ instead of POINT	LINESTRINGZ(15.21 57.58 0.31, 15.81 57.12 0.33)
POLYGONZ same as POLYGON, but containing POINTZ instead of POINT	...
MULTIPOINTZ same as MULTIPOINT, but containing POINTZ instead of POINT	MULTIPOINTZ(15.21 57.58 0.31, 15.81 57.12 0.33)
MULTILINESTRINGZ same as MULTILINESTRING, but containing POINTZ instead of POINT	...
MULTIPOLYGONZ same as MULTIPOLYGON, but containing POINTZ instead of POINT	...
GEOMETRYCOLLECTIONZ same a GEOMETRYCOLLECTION, but containing: (MULTI-) POINTZ instead of (MULTI-) POINT (MULTI-) MULTILINESTRINGZ instead of (MULTI-) MULTILINESTRING (MULTI-) POLYGONZ instead of (MULTI-) POLYGON	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

POINTM
1 triplet of X/Y/M-Values (a vertice),
  separated with a 'space' POINTM(13.21 47.21 1000.0)
containing three values

position 1: X-value

position 2: Y-value

position 3: M-value (measure)

separated with a 'space'

LINESTRINGM
same a LINESTRING,
  but containing POINTM instead of POINT LINESTRINGM(15.21 57.58 1000.0, 15.81 57.12 1100.0)

POLYGONM
same as POLYGON,
  but containing POINTM instead of POINT ...

MULTIPOINTM
same a MULTIPOINT,
  but containing POINTM instead of POINT MULTIPOINTM(15.21 57.58 1000.0, 15.81 57.12 1100.0)

MULTILINESTRINGM
same a MULTILINESTRING,
  but containing POINTM instead of POINT ...

MULTIPOLYGONM
same a MULTIPOLYGON,
  but containing POINTM instead of POINT ...

GEOMETRYCOLLECTIONM
same a GEOMETRYCOLLECTION,
  but containing:

(MULTI-) POINTM instead of (MULTI-) POINT

(MULTI-) MULTILINESTRINGM instead of (MULTI-) MULTILINESTRING

(MULTI-) POLYGONM instead of (MULTI-) POLYGON

GEOMETRYCOLLECTIONM(POINTM(13.21 47.21 1000.0),
LINESTRINGM(15.21 57.58 1000.0, 15.81 57.12 1100.0))

Geometry Type	WKT example
POINTM 1 triplet of X/Y/M-Values (a vertice), separated with a 'space'	POINTM(13.21 47.21 1000.0) containing three values position 1: X-value position 2: Y-value position 3: M-value (measure) separated with a 'space'
LINESTRINGM same a LINESTRING, but containing POINTM instead of POINT	LINESTRINGM(15.21 57.58 1000.0, 15.81 57.12 1100.0)
POLYGONM same as POLYGON, but containing POINTM instead of POINT	...
MULTIPOINTM same a MULTIPOINT, but containing POINTM instead of POINT	MULTIPOINTM(15.21 57.58 1000.0, 15.81 57.12 1100.0)
MULTILINESTRINGM same a MULTILINESTRING, but containing POINTM instead of POINT	...
MULTIPOLYGONM same a MULTIPOLYGON, but containing POINTM instead of POINT	...
GEOMETRYCOLLECTIONM same a GEOMETRYCOLLECTION, but containing: (MULTI-) POINTM instead of (MULTI-) POINT (MULTI-) MULTILINESTRINGM instead of (MULTI-) MULTILINESTRING (MULTI-) POLYGONM instead of (MULTI-) POLYGON	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

POINTZM
1 quadruple of X/Y/Z/M-Values (a vertice),
  separated with a 'space' POINTZM(13.21 47.21 0.21 1000.0)
containing four values

position 1: X-value

position 2: Y-value

position 3: Z-value (height/dem)

position 4: M-value (measure)

separated with a 'space'

LINESTRINGZM
same a LINESTRING,
  but containing POINTM instead of POINT LINESTRINGZM(15.21 57.58 0.31 1000.0, 15.81 57.12 0.33 1100.0)

POLYGONZM
same a POLYGON,
  but containing POINTZM instead of POINT ...

MULTIPOINTZM
same a MULTIPOINT,
  but containing POINTM instead of POINT MULTIPOINTZM(15.21 57.58 0.31 1000.0, 15.81 57.12 0.33 1100.0)

MULTILINESTRINGZM
same a MULTILINESTRING,
  but containing POINTM instead of POINT ...

MULTIPOLYGONZM
same a MULTIPOLYGON,
  but containing POINTZM instead of POINT ...

GEOMETRYCOLLECTIONZM
same a GEOMETRYCOLLECTION,
  but containing:

(MULTI-) POINTZM instead of (MULTI-) POINT

(MULTI-) MULTILINESTRINGZM instead of (MULTI-) MULTILINESTRING

(MULTI-) POLYGONZM instead of (MULTI-) POLYGON

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

Geometry Type	WKT example
POINTZM 1 quadruple of X/Y/Z/M-Values (a vertice), separated with a 'space'	POINTZM(13.21 47.21 0.21 1000.0) containing four values position 1: X-value position 2: Y-value position 3: Z-value (height/dem) position 4: M-value (measure) separated with a 'space'
LINESTRINGZM same a LINESTRING, but containing POINTM instead of POINT	LINESTRINGZM(15.21 57.58 0.31 1000.0, 15.81 57.12 0.33 1100.0)
POLYGONZM same a POLYGON, but containing POINTZM instead of POINT	...
MULTIPOINTZM same a MULTIPOINT, but containing POINTM instead of POINT	MULTIPOINTZM(15.21 57.58 0.31 1000.0, 15.81 57.12 0.33 1100.0)
MULTILINESTRINGZM same a MULTILINESTRING, but containing POINTM instead of POINT	...
MULTIPOLYGONZM same a MULTIPOLYGON, but containing POINTZM instead of POINT	...
GEOMETRYCOLLECTIONZM same a GEOMETRYCOLLECTION, but containing: (MULTI-) POINTZM instead of (MULTI-) POINT (MULTI-) MULTILINESTRINGZM instead of (MULTI-) MULTILINESTRING (MULTI-) POLYGONZM instead of (MULTI-) POLYGON	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)

Spatial MetaData Tables

SpatiaLite requires several metadata tables in order to work properly.
There is absolutely nothing strange in such tables; they simply are tables as any other one.
They are collectively as metadata because they are collectively intended to support an extended and complete qualification of Geometries.

Quite any Spatial SQL function strongly relies on such tables: so they are absolutely required for internal management purposes.
Any attempt to hack someway such tables will quite surely result in a severely corrupted (and malfunctioning) database.

There is a unique safe way to interact with metadata tables, i.e. using as far as possible the appropriate Spatial SQL functions.
Directly performing INSERT., UPDATE. or DELETE. on their behalf is a completely unsafe and strongly discouraged practice.

SELECT
InitSpatialMetaData();

The InitSpatialMetaData(). function must be called immediately after creating a new database, and before attempting to call any other Spatial SQL function:

the scope of this function is exactly the one to create (and populate) any metadata table internally required by SpatiaLite.
if any metadata table already exist, this function doesn't apply any action at all:
so, calling more times InitSpatialMetaData(). is useless but completely harmless.
please note: spatialite_gui (and thespatialite command line tool) will automatically perform any required initialization task every time a new database is created:
so (using this tool) there is no need at all to explicitly call this function.

SELECT
*
FROM spatial_ref_sys;

srid auth_name auth_srid ref_sys_name proj4text srs_wkt

2000 epsg 2000 Anguilla 1957 / British West Indies Grid +proj=tmerc +lat_0=0 +lon_0=-62 +k=0.9995000000000001 +x_0=400000 +y_0=0 +ellps=clrk80 +units=m +no_defs PROJCS["Anguilla 1957 / British West Indies Grid",
GEOGCS["Anguilla 1957",
DATUM["Anguilla_1957",
SPHEROID["Clarke 1880 (RGS)",6378249.145,293.465,
AUTHORITY["EPSG","7012"]],
AUTHORITY["EPSG","6600"]],
PRIMEM["Greenwich",0,
AUTHORITY["EPSG","8901"]],
UNIT["degree",0.01745329251994328,
AUTHORITY["EPSG","9122"]],
AUTHORITY["EPSG","4600"]],
UNIT["metre",1,
AUTHORITY["EPSG","9001"]],
PROJECTION["Transverse_Mercator"],
PARAMETER["latitude_of_origin",0],
PARAMETER["central_meridian",-62],
PARAMETER["scale_factor",0.9995],
PARAMETER["false_easting",400000],
PARAMETER["false_northing",0],
AUTHORITY["EPSG","2000"],
AXIS["Easting",EAST],
AXIS["Northing",NORTH]]

... ... ... ... ... ...

srid	auth_name	auth_srid	ref_sys_name	proj4text	srs_wkt
2000	epsg	2000	Anguilla 1957 / British West Indies Grid	+proj=tmerc +lat_0=0 +lon_0=-62 +k=0.9995000000000001 +x_0=400000 +y_0=0 +ellps=clrk80 +units=m +no_defs	PROJCS["Anguilla 1957 / British West Indies Grid", GEOGCS["Anguilla 1957", DATUM["Anguilla_1957", SPHEROID["Clarke 1880 (RGS)",6378249.145,293.465, AUTHORITY["EPSG","7012"]], AUTHORITY["EPSG","6600"]], PRIMEM["Greenwich",0, AUTHORITY["EPSG","8901"]], UNIT["degree",0.01745329251994328, AUTHORITY["EPSG","9122"]], AUTHORITY["EPSG","4600"]], UNIT["metre",1, AUTHORITY["EPSG","9001"]], PROJECTION["Transverse_Mercator"], PARAMETER["latitude_of_origin",0], PARAMETER["central_meridian",-62], PARAMETER["scale_factor",0.9995], PARAMETER["false_easting",400000], PARAMETER["false_northing",0], AUTHORITY["EPSG","2000"], AXIS["Easting",EAST], AXIS["Northing",NORTH]]
...	...	...	...	...	...

The spatial_ref_sys. table does actually contains the whole EPSG dataset (Spatial Reference System definitions).

the SRID column is the PRIMARY KEY uniquely identifying each item.
the auth_name.<, auth_srid. and ref_sys_name. columns usually contains a reference to the original EPSG definition (mainly for documentation purposes).
the proj4text. column contains geodesic parameters required by the PROJ.4 library.
- these parameters are absolutely required by the Transform(). function, because any coordinate re-projection will be actually performed invoking the appropriate PROJ.4 functions.
the srs_wkt column contains a complete definition of the corresponding SRS using the (obnoxiously verbose) WKT. format.
- SpatiaLite itself doesn't requires this information to be present: but if this WKT. string is available, then a .PRJ. file will be created when exporting any Shapefile
  (many GIS packages require a .PRJ file to be present for each Shapefile).
- please, don't be confused: this WKT. for SRS has nothing to do with the better known WKT. used to represent geometries.
important notice: altering the original EPSG definitions is unsafe and strongly discouraged, and must be absolutely avoided.
Anyway you are absolutely free to insert further custom definitions by your own:
in this case using SRID values > 32768. is strongly suggested.

SELECT
*
FROM geometry_columns;

f_table_name f_geometry_column type coord_dimension srid spatial_index_enabled

communities geometry MULTIPOLYGON XY 23032 1

populated_places geometry POINT XY 4326 1

f_table_name	f_geometry_column	type	coord_dimension	srid	spatial_index_enabled
communities	geometry	MULTIPOLYGON	XY	23032	1
populated_places	geometry	POINT	XY	4326	1

The geometry_columns table supports each Geometry column defined into the database:

any column not supported by a corresponding entry within this table, for sure cannot be considered as a genuine Geometry.
important notice: any attempt to hack this table by directly performing INSERT., UPDATE. or DELETE. will quite surely end into a major disaster
(i.e. a corrupted and malfunctioning database).
Use the appropriate SQL functions instead: AddGeometryColumn(), RecoverGeometryColumn() and so on.

The geometry_columns table is intended to support ordinary tables.
Anyway two further similar tables exist as well:

the views_geometry_columns. is intended to support Geometry VIEWs.
and the virts_geometry_columns. is intended to support Virtual Shapefiles.

viewing SpatiaLite layers in QGIS

QGIS is a really popular and widespread desktop GIS app: you can download the latest QGIS from:
http://www.qgis.org/
QGIS contains an internal data provider supporting SpatiaLite:
so interacting with any SpatiaLite's DB using a classic desktop GIS is simple and easy.

You simply have to connect the SpatiaLite's DB, then choosing the layer(s) you intend to use.
Please note: accordingly to DBMS terminology you are accustomed to handle tables.
But in the GIS own jargon the term layers is very often used to identify exactly the same thing.

Once you've connected your layers from the SpatiaLite DB you can immediately start using QGIS own tools.
And that's all.

Back to the SpatiaLite home page	Previous Chapter Chapter 01: Kitchen tools and cooking techniques
Back to the Cookbook home page	Next Chapter Chapter 03: Family cooking

Back to the SpatiaLite home page	Previous Chapter Chapter 01: Kitchen tools and cooking techniques
Back to the Cookbook home page	Next Chapter Chapter 03: Family cooking

SpatiaLite Cookbook Chapter 02: Commonly used ingredients

your first Spatial SQL queries

more about Spatial SQL: WKT and WKB

Spatial MetaData Tables

viewing SpatiaLite layers in QGIS