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.

	Spatial MetaData Tables
2011 January 28

Previous Slide

Table of Contents

Next Slide

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 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]]
...	...	...	...	...	...

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
local_councils	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.

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.