cannot create column with spatialite -> unexpected metadata layout
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
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
.