Came here to add this. I might pick it up.

Would also add a utility to create (and update and delete?) a spatial index. It's not much code but I have to look it up every time.

Some proposals I'd add to sqlite-utils:

Some version of this, from geojson-to-sqlite:

def init_spatialite(db, lib):
    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)")

Also a function for creating a spatial index:

db.conn.execute("select CreateSpatialIndex(?, ?)", [table, "geometry"])

I don't know the nuances of updating a spatial index, or checking if one already exists. This could be a CLI method like:

sqlite-utils spatial-index spatial.db table-name column-name

Other thing: If there get to be enough utils, I think it's worth moving all the spatialite stuff into its own file (gis.py or something) just so it's easier to find later.

One more thing I'm going to add: A method to add a geometry column, which I'll need to do to create a spatial index on a table.

@simonw I have a PR here #385 that adds Spatialite helpers on the Python side. Please let me know how it looks.

Taking part of the conversation from #385 here.

Would sqlite-utils add-geometry-column ... be a good CLI enhancement. for example?

Yes. And also sqlite-utils create-spatial-index would be great to have. My plan would be to add those once the Python API is settled.

Documentation: https://sqlite-utils.datasette.io/en/latest/python-api.html#spatialite-helpers

Shipped in 3.23: https://sqlite-utils.datasette.io/en/stable/changelog.html#v3-23