--spatialite fails if features have no properties

[simonw]

https://raw.githubusercontent.com/datanews/amtrak-geojson/master/amtrak-combined.geojson

Has a feature like this:

"properties": { }

This fails with an error if you use the --spatialite option:

geojson-to-sqlite /tmp/amtrak.db amtrak /tmp/amtrak-combined.geojson --spatialite                                           
Traceback (most recent call last):
  File "/Users/simon/.local/bin/geojson-to-sqlite", line 8, in <module>
    sys.exit(cli())
  File "/Users/simon/.local/pipx/venvs/geojson-to-sqlite/lib/python3.9/site-packages/click/core.py", line 1134, in __call__
    return self.main(*args, **kwargs)
  File "/Users/simon/.local/pipx/venvs/geojson-to-sqlite/lib/python3.9/site-packages/click/core.py", line 1059, in main
    rv = self.invoke(ctx)
  File "/Users/simon/.local/pipx/venvs/geojson-to-sqlite/lib/python3.9/site-packages/click/core.py", line 1401, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/Users/simon/.local/pipx/venvs/geojson-to-sqlite/lib/python3.9/site-packages/click/core.py", line 767, in invoke
    return __callback(*args, **kwargs)
  File "/Users/simon/.local/pipx/venvs/geojson-to-sqlite/lib/python3.9/site-packages/geojson_to_sqlite/cli.py", line 32, in cli
    utils.import_features(
  File "/Users/simon/.local/pipx/venvs/geojson-to-sqlite/lib/python3.9/site-packages/geojson_to_sqlite/utils.py", line 69, in import_features
    db[table].create(column_types, pk=pk)
  File "/Users/simon/.local/pipx/venvs/geojson-to-sqlite/lib/python3.9/site-packages/sqlite_utils/db.py", line 863, in create
    self.db.create_table(
  File "/Users/simon/.local/pipx/venvs/geojson-to-sqlite/lib/python3.9/site-packages/sqlite_utils/db.py", line 517, in create_table
    self.execute(sql)
  File "/Users/simon/.local/pipx/venvs/geojson-to-sqlite/lib/python3.9/site-packages/sqlite_utils/db.py", line 236, in execute
    return self.conn.execute(sql)
sqlite3.OperationalError: near ")": syntax error

[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.