`--spatialite` fails if features have no properties

[simonw]

`--spatialite` fails if features have no properties

[simonw]

Here's why:

geojson-to-sqlite/geojson_to_sqlite/utils.py

Lines 70 to 75 in 17c2d09

	if table not in db.table_names():
	# Create the table, using detected column types
	column_types = sqlite_utils.suggest_column_types(sample_records)
	column_types.pop("geometry")
	db[table].create(column_types, pk=pk)
	ensure_table_has_geometry(db, table)

Since geometry is added later the call to db[table].create(column_types, pk=pk) has an empty column_types dictionary.

[simonw]

Really hard to know what to do about this one - the GeoJSON object here really is just a geometry with nothing else. There's not even an ID that can be used as a primary key.

So we really want to create a SQLite table with just a rowid. Is that even possible?

[simonw]

https://stackoverflow.com/a/36926664/6083 suggests using this:

CREATE TABLE tablename (rowid INTEGER PRIMARY KEY) WITHOUT ROWID;

That makes me a little bit nervous, see https://www.sqlite.org/withoutrowid.html - it's not clear to me if this kind of "fake" rowid table will cause weird problems in the future, especially if I start using tricks like this one: https://til.simonwillison.net/sqlite/track-timestamped-changes-to-a-table

I'm tempted to create a id column that is primary key integer and hence will auto-increment.

[simonw]

I think the safest thing to do here would be to create the table with a _temp column, add the geometry column and then drop that _temp column.

[simonw]

Fixed it! Here's that GeoJSON imported and rendered using https://datasette.io/plugins/datasette-geojson-map

[simonw]

I think I spotted a bug:

geojson-to-sqlite/geojson_to_sqlite/utils.py

Lines 70 to 77 in 23bdb74

	remove_tmp_column = False
	if not column_types:
	remove_tmp_column = False
	column_types["_tmp"] = str
	db[table].create(column_types, pk=pk)
	ensure_table_has_geometry(db, table)
	if remove_tmp_column:
	db[table].transform(drop={"_tmp"})

remove_tmp_column is never switched to True.