Add SpatiaLite helpers to CLI

[eyeseast]

Now that #385 is merged, add CLI versions of those methods.

# init spatialite
sqlite-utils init-spatialite database.db

# or maybe/also
sqlite-utils create database.db --enable-wal --spatialite

# add geometry columns
# needs a database, table, geometry column name, type, with optional SRID and not-null
# this needs to create a table if it doesn't already exist
sqlite-utils add-geometry-column database.db table-name geometry --srid 4326 --not-null

# spatial index an existing table/column
sqlite-utils create-spatial-index database.db table-name geometry

Should be mostly straightforward. The one thing worth highlighting in docs is that geometry columns can only be added to existing tables. Trying to add a geometry column to a table that doesn't exist yet might mean you have a schema like {"rowid": int, "geometry": bytes}. Might be worth nudging people to explicitly create a table first, then add geometry columns.

[simonw]

I like these designs a lot. I would suggest sqlite-utils create database.db --init-spatialite there for consistency with the sqlite-utils init-spatialite database.db command.

The other part of this story is how we support actually inserting spatial data from the command-line. I opened an issue about the challenges in doing that for the Python API here - #399 - but we need a good answer for the CLI too.

I don't yet have any good ideas here. The conversions= option in the Python library was designed to cover these kinds of cases but it's pretty clunky and I don't think it's very widely used: https://sqlite-utils.datasette.io/en/stable/python-api.html#converting-column-values-using-sql-functions

[simonw]

The one thing worth highlighting in docs is that geometry columns can only be added to existing tables. Trying to add a geometry column to a table that doesn't exist yet might mean you have a schema like {"rowid": int, "geometry": bytes}. Might be worth nudging people to explicitly create a table first, then add geometry columns.

That's a good call. I'm happy for sqlite-utils add-geometry-column to throw an error if the table doesn't exist yet.

[simonw]

For ingesting geometry data from the command-line maybe GeoJSON would be the best route?

[simonw]

@eyeseast how do you usually insert geometries at the moment?

[eyeseast]

I'm mostly using geojson-to-sqlite at the moment. Even with shapefiles, I'm usually converting to GeoJSON and projecting to EPSG:4326 (with ogr2ogr) first.

I think an open question here is how much you want to leave to external libraries and how much you want here. My thinking has been that adding Spatialite helpers here would make external stuff easier, but it would be nice to have some standard way to insert geometries.

I'm in the middle of adding GeoJSON and Spatialite support to geocode-sqlite, and that will probably use WKT. Since that's all points, I think I can just make the string inline. But for polygons, I'd generally use Shapely, which probably isn't a dependency you want to add to sqlite-utils.

I've also been trying to get some of the approaches here to work, but haven't had any success so far.

[simonw]

Yeah I'd like to avoid adding any geo-dependencies to sqlite-utils if I can avoid it. I'm fine using stuff that's going to be available in SpatiaLite itself (provided it's available as a SQLite module) since then I don't need to add any extra Python dependencies.

[simonw]

Adding some thoughts to:

• Make it easier to insert geometries, with documentation and maybe code #399

[eyeseast]

Been chipping away at this between other things and realized sqlite-utils init-spatialite is probably unnecessary. Any of the other commands requires running db.init_spatialite to have the extension functions available, and that will do everything init-spatialite would do.

I think it's probably worth keeping a SpatiaLite flag on create-database in case you wanted to create all the spatial metadata up front. Otherwise, it's going to get added the first time you run add-geometry-column or create-spatial-index, which is probably fine in most cases.

[simonw]

Documentation:

• https://sqlite-utils.datasette.io/en/latest/cli-reference.html#create-database
• https://sqlite-utils.datasette.io/en/latest/cli-reference.html#add-geometry-column
• https://sqlite-utils.datasette.io/en/latest/cli-reference.html#create-spatial-index
• https://sqlite-utils.datasette.io/en/latest/cli.html#spatialite-helpers