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.

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

the last (optional) arguments actually means: NOT NULL
setting a value ZERO (assumed to be the default value if omitted) then the Geometry column will accept NULL values.
otherwise only NOT NULL geometries will be accepted.

Supported SRIDs:

any possible SRID defined within the spatial_ref_sys metadata table.
or -1 to politely denote any unknown / unspecified SRS.

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:

AddGeometryColumn() is intended to create a new, empty column.
RecoverGeometryColumn() is intended to recover in a second time an already existing (and populated) column.

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

	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.