SpatiaLite logo

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:

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

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

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.