search

simonw / shapefile-to-sqlite

Load shapefiles into a SQLite (optionally SpatiaLite) database
Apache License 2.0
28 stars 4 forks source link

Initialize SpatiaLite without KNN virtual table #11

Open simonw opened 3 years ago

simonw commented 3 years ago

Now that Homebrew has upgraded SpatiaLite on my laptop to 5.0 this tool seems to be creating databases that aren't compatible with SpatiaLite 4.3.x - which currently breaks datasette publish (see https://github.com/simonw/datasette/issues/1249).

I think the problem is that a KNN virtual table is being created - CREATE VIRTUAL TABLE KNN USING VirtualKNN();

Since the databases created by this tool don't necessarily need KNN it would be better if it didn't create that.

simonw commented 3 years ago

I think this is the revelant code: https://github.com/simonw/shapefile-to-sqlite/blob/2b2233d19e99af78e718ffdfc0e43ee8a078ae0f/shapefile_to_sqlite/utils.py#L128-L141

simonw commented 3 years ago

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=KNN says:

Every new db-file being created with 4.4.0 will always automatically define a KNN virtual table.\ On any earlier version of a SpatiaLite db-file, you can add the KNN support by manually executing the above SQL statement.\ Note: VirtualKNN necessarily requires 4.4.0 binary support, so any attempt to open a db-file including a VirtualKNN table by using some previous version (<= 4.3.0a) will surely raise an error condition.

simonw commented 3 years ago

Maybe the fix is to run DROP TABLE KNN;?

simonw commented 3 years ago

I tried using this against my broken DB file:

spatialite /tmp/counties.db 'drop table KNN';

And it worked - I could then deploy that database using an older version of SpatiaLite.