Initialize SpatiaLite without KNN virtual table

[simonw]

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 simonw/datasette#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]

I think this is the revelant code:

shapefile-to-sqlite/shapefile_to_sqlite/utils.py

Lines 128 to 141 in 2b2233d

	db.conn.enable_load_extension(True)
	db.conn.load_extension(lib)
	# Initialize SpatiaLite if not yet initialized
	if "spatial_ref_sys" in db.table_names():
	return
	db.conn.execute("select InitSpatialMetadata(1)")
	def ensure_table_has_geometry(db, table, table_srid):
	if "geometry" not in db[table].columns_dict:
	db.conn.execute(
	"SELECT AddGeometryColumn(?, 'geometry', ?, 'GEOMETRY', 2);",
	[table, table_srid],
	)

[simonw]

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]

Maybe the fix is to run DROP TABLE KNN;?

[simonw]

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.