cannot create column with spatialite -> unexpected

Question

I'm very new with spatialite. I'm running on Max OS Mountain lion and I have installed SQLite version 3.7.17 and libspatialite 4.1.1 (using homebrew)

I can load without error the extension libspatialite in SQLite :

SELECT load_extension('/usr/local/Cellar/libspatialite/4.1.1/lib/libspatialite.dylib');

I can create a simple table:

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

but when I add a Geometry column with spatialite I get the following message:

sqlite> SELECT AddGeometryColumn('test_geom', 'the_geom',4326, 'POINT');
AddGeometryColumn() error: unexpected metadata layout
0

I have no idea to fix this issue. Can somebody help?

Answer 1

This happens when you are using SQLite with Spatialite extension. In that case, you need to initialize spatial metadata tables (do this right after creating the database):

SELECT InitSpatialMetaData();

Another option would be to run Spatialite when creating the database. That will then automatically create the metadata tables.

Documentation: http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/metadata.html

Answer 2

This issue occurs because there is no metadata (spatial_ref_sys table) within the database which happens when you create the database with sqlite3.exe rather than spatialite.exe.

For example; if you create two databases, one with sqlite3 and the other with spatialite and run .table you will see that the db created by spatialite has a suite of other tables including a spatial_ref_sys. The spatial functions require these reference tables in order to work.

Solution: Created a spatialite db and import the old db there or load all the necessary table into the old database. Either or is realtively easy with .dump.