--spatialite bug: AddGeometryColumn() error: table X does not exist

[simonw]

Try this:

curl -s https://eric.clst.org/assets/wiki/uploads/Stuff/gz_2010_us_040_00_20m.json \
    | geojson-to-sqlite my.db states - --pk GEO_ID --spatialite

It throws this error:

InitSpatiaMetaData() error:"table spatial_ref_sys already exists"

[simonw]

This time it threw this one:

AddGeometryColumn() error: table 'states' does not exist

That's because at the time we attempt to run this code the table may not have been created by a row insertion:

geojson-to-sqlite/geojson_to_sqlite/utils.py

Line 33 in 798c8b8

ensure_table_has_geometry(db, table)

[simonw]

Not sure what to do about the AddGeometryColumn() error. Maybe sqlite-utils needs a mechanism for "after first creating the table, run this additional function"?

[simonw]

A failing test would be good here.

[simonw]

Thinking about this further, I don't think that on_create hook is the right fix.

When creating the table we need to avoid creating a "geometry" column, because we're going to instead create that using the AddGeometryColumn function.

As such, sqlite-util's default of creating the table automatically based on the initial dictionary may not be the right thing to do.

Needs more thought.

[simonw]

I can use this method to detect the column types from the records:

https://github.com/simonw/sqlite-utils/blob/e8b2b7383bd94659d3b7a857a1414328bc48bc19/sqlite_utils/db.py#L823-L845

Then manually create the table minus the "geometry" column, then add that column afterwards.

[simonw]

Got the command from the first comment to work!