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.



SpatiaLite logo

Recipe #6:
Creating a new Geometry column

2011 January 28

Previous Slide Table of Contents Next Slide

We'll now examine in deeper detail how to correctly define a Geometry-type column.
SpatiaLite follows an approach very closely related to the one adopted by PostgreSQL/PostGIS;
i.e. creating a Geometry-type at the same time the corresponding table is created isn't allowed.
You always must first create the table, then adding the Geometry-column in a second time and as a separate step.

CREATE TABLE test_geom (
  id INTEGER NOT NULL
    PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  measured_value DOUBLE NOT NULL);

SELECT AddGeometryColumn('test_geom', 'the_geom',
  4326, 'POINT', 'XY');
This is the only supported way to get a completely valid Geometry.
Any different approach will surely produce an incorrect and unreliable Geometry.

SELECT AddGeometryColumn('test_geom', 'the_geom',
  4326, 'POINT', 'XY', 0);

SELECT AddGeometryColumn('test_geom', 'the_geom',
  4326, 'POINT', 'XY', 1);
Although the previous one surely is the most often used form, this one the complete form supported by AddGeometryColumn():

Supported SRIDs: Supported Geometry-types:
Geometry Type Notes
POINT

the commonly used Geometry-types:
corresponding to Shapefile's specs
supported by any desktop GIS apps

LINESTRING
POLYGON
MULTIPOINT
MULTILINESTRING
MULTIPOLYGON
GEOMETRYCOLLECTION Not often used: unsupported by Shapefile and desktop GIS apps
GEOMETRY A generic container supporting any possible geometry-class
Not often used: unsupported by Shapefile and desktop GIS apps

Supported Dimension-models:
Dimension model Alias Notes
XY 2 X and Y coords (simple 2D)
XYZ 3 X, Y and Z coords (3D)
XYM
X and Y coords + measure value M
XYZM
X, Y and Z coords + measure value M


Please note well: this one is a very frequent pitfall.
Many developers, GIS professionals and alike obviously feel to be much smarter than this, so they often tend to invent some highly imaginative alternative way to create their own Geometries.
e.g. bungling someway the geometry_columns table seems to be a very popular practice.

May well be that such creative methods will actually work with some very specific SpatiaLite's version; but for sure some severe incompatibility will raise before or after ...

Be warned: only Geometries created using AddGeometryColumn() are fully legitimate.
Any different approach is completely unsafe (and unsupported ..)

I suppose that directly checking how AddGeometryColumn() affects the database may help you to understand better.

PRAGMA table_info(test_geom);

cid name type notnull dflt_value pk
0 id INTEGER 1 NULL 1
1 name TEXT 1 NULL 0
2 measured_value DOUBLE 1 NULL 0
3 the_geom POINT 0 NULL 0
step 1: a new test_geom column has been added to the corresponding table.

SELECT *
FROM geometry_columns
WHERE f_table_name LIKE 'test_geom';

f_table_name f_geometry_column type coord_dimension srid spatial_index_enabled
test_geom the_geom POINT XY 4326 0
step 2: a corresponding row has been inserted into the geometry_columns metadata table.

SELECT *
FROM sqlite_master
WHERE type = 'trigger'
  AND tbl_name LIKE 'test_geom';

type name tbl_name rootpage sql
trigger ggi_test_geom_the_geom test_geom 0 CREATE TRIGGER "ggi_test_geom_the_geom" BEFORE INSERT ON "test_geom"
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'test_geom.the_geom violates Geometry constraint [geom-type or SRID not allowed]')
WHERE (SELECT type FROM geometry_columns
WHERE f_table_name = 'test_geom' AND f_geometry_column = 'the_geom'
AND GeometryConstraints(NEW."the_geom", type, srid, 'XY') = 1) IS NULL;
END
trigger ggu_test_geom_the_geom test_geom 0 CREATE TRIGGER "ggu_test_geom_the_geom" BEFORE UPDATE ON "test_geom"
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'test_geom.the_geom violates Geometry constraint [geom-type or SRID not allowed]')
WHERE (SELECT type FROM geometry_columns
WHERE f_table_name = 'test_geom' AND f_geometry_column = 'the_geom'
AND GeometryConstraints(NEW."the_geom", type, srid, 'XY') = 1) IS NULL;
END
step 3: the sqlite_master is the main metadata table used by SQLite to store internal objects.
As you can easily notice, each Geometry requires some triggers to be fully supported and well integrated into the DBMS workflow.
Not at all surprisingly, all this has to be defined in a strongly self-consistent way in order to let SpatiaLite work as expected.
If some element is missing or badly defined, the obvious consequence will be a defective and unreliable Spatial DBMS.

SELECT DiscardGeometryColumn('test_geom', 'the_geom');
This will remove any metadata and any trigger related to the given Geometry.
Please note: anyway this will leave any geometry-value stored within the corresponding table absolutely untouched.
Simply, after calling DiscardGeometryColumn(...) they aren't any longer fully qualified geometries, but anonymous and generic BLOB values.

SELECT RecoverGeometryColumn('test_geom', 'the_geom',
  4326, 'POINT', 'XY');
This will attempt to recreate any metadata and any trigger related to the given Geometry.
If the operation successfully completes, then the Geometry column is fully qualified.
In other words, there is absolutely no difference between a Geometry created by AddGeometryColumn() and another created by RecoverGeometryColumn().
Very simply explained:

Compatibility issues between different versions

SpatiaLite isn't eternally immutable.
Like any other human artifact and any other software package SpatiaLite tends to evolve during the time; and SQLite as well evolves during the time.

Solemn commitment: you are absolutely granted that any database-file generated by some previous (older) version can be safely operated using any later (newer) version of both SQLite and SpatiaLite.

Please note well: the opposite isn't necessarily true.
Attempting to operate a database-file generated by a most recent (newer) version using any previous (older) version may easily be impossible at all, or may cause some more or less serious trouble.

Sometimes circumventing version-related issues is inherently impossible: e.g. there is absolutely no way to use 3D geometries on obsolescent versions, because the required support was introduced in more recent times.
But in many other cases such issues are simply caused by some incompatible binary function required by triggers.

Useful hint

To resolve any trigger-related incompatibility you can simply try to:
  • remove first any trigger: the best way you can follow is using DiscardGeometryColumn()
  • and then recreate again the triggers using AddGeometryColumn()
This will ensure that any metadata info and trigger will surely match expectations of your binary library current version.

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.