Exactly, that's what I was running into. On my M2 MacBook, SpatiaLite ends up in what is -- for the moment -- a non-standard location, so even when I passed in the location with --load-extension, I still hit an error on create-spatial-index.

What I learned doing this originally is that SQLite needs to load the extension for each connection, even if all the SpatiaLite stuff is already in the database. So that's why init_spatialite() gets called again.

Here's the code where I hit the error: https://github.com/eyeseast/boston-parcels/blob/main/Makefile#L30 It works using this branch.

I'm not attached to this solution if you can think of something better. And I'm not sure, TBH, my test would actually catch what I'm after here.

This would be a nice feature to have with datasette publish too.

SQLite doesn't have a native DATETIME type. It stores dates internally as strings and then has functions to work with date-like strings. Yes it's weird.

Yes please, and thank you. I realized I was maybe getting myself in trouble using that, but I think it's a good way to standardize JSON handling.

Totally missed that issue. I can close this as a duplicate.

I'll add issues for both and do a documentation PR.

Interesting. I started a version using metadata like I outlined up top, but I realized that there's no documented way for a plugin to access either metadata or canned queries. Or at least, I couldn't find a way.

There is this method: https://github.com/simonw/datasette/blob/main/datasette/app.py#L472 but I don't want to rely on it if it's not documented. Same with this: https://github.com/simonw/datasette/blob/main/datasette/app.py#L544

If those are safe, I'll build on them. I'm also happy to document them, if that greases the wheels.

This probably counts as a case study: https://github.com/eyeseast/spatial-data-cooking-show. Even has video.

Seriously, though, this workflow has become integral to my work with reporters and editors across USA TODAY Network. Very often, I get sent a folder of data in mixed formats, with a vague ask of how we should communicate some part of it to users. Datasette and its constellation of tools makes it easy to get a quick look at that data, run exploratory queries, map it and ask questions to figure out what's important to show. And then I export a version of the data that's exactly what I need for display.

I'm realizing I don't know if a virtual table will ever return a count. Maybe it depends on the implementation. For these three, just checking now, it'll always return zero.

That said, I'm not sure there's any downside to having them return zero and caching that. (They're hidden, too.)

It does work, though I think it's probably still worth excluding virtual tables that will always be zero. Here's the same inspection as before, now with --load-extension spatialite:

json { "alltheplaces": { "hash": "0843cfe414439ab903c22d1121b7ddbc643418c35c7f0edbcec82ef1452411df", "size": 963375104, "file": "alltheplaces.db", "tables": { "spatial_ref_sys": { "count": 6215 }, "spatialite_history": { "count": 18 }, "sqlite_sequence": { "count": 2 }, "geometry_columns": { "count": 3 }, "spatial_ref_sys_aux": { "count": 6164 }, "views_geometry_columns": { "count": 0 }, "virts_geometry_columns": { "count": 0 }, "geometry_columns_statistics": { "count": 3 }, "views_geometry_columns_statistics": { "count": 0 }, "virts_geometry_columns_statistics": { "count": 0 }, "geometry_columns_field_infos": { "count": 0 }, "views_geometry_columns_field_infos": { "count": 0 }, "virts_geometry_columns_field_infos": { "count": 0 }, "geometry_columns_time": { "count": 3 }, "geometry_columns_auth": { "count": 3 }, "views_geometry_columns_auth": { "count": 0 }, "virts_geometry_columns_auth": { "count": 0 }, "data_licenses": { "count": 10 }, "sql_statements_log": { "count": 0 }, "states": { "count": 56 }, "counties": { "count": 3234 }, "idx_states_geometry_rowid": { "count": 56 }, "idx_states_geometry_node": { "count": 3 }, "idx_states_geometry_parent": { "count": 2 }, "idx_counties_geometry_rowid": { "count": 3234 }, "idx_counties_geometry_node": { "count": 98 }, "idx_counties_geometry_parent": { "count": 97 }, "idx_places_geometry_rowid": { "count": 1236796 }, "idx_places_geometry_node": { "count": 38163 }, "idx_places_geometry_parent": { "count": 38162 }, "places": { "count": 1332609 }, "SpatialIndex": { "count": 0 }, "ElementaryGeometries": { "count": 0 }, "KNN": { "count": 0 }, "idx_states_geometry": { "count": 56 }, "idx_counties_geometry": { "count": 3234 }, "idx_places_geometry": { "count": 1236796 } } } }

Here's how to get a list of virtual tables: https://stackoverflow.com/questions/46617118/how-to-fetch-names-of-virtual-tables

Yeah, every time I see something cool done with triggers, I remember that I need to start using triggers.

That's reasonable. Canned queries and custom endpoints are certainly going to give more room for specific needs.

This could use a new plugin hook, too. I don't want to complicate your life too much, but for things like GIS, I'd want a way to turn regular JSON into SpatiaLite geometries or combine X/Y coordinates into point geometries and such. Happy to help however I can.

Right. The backup command will copy tables completely, but in the case of conflicting table names, the destination gets overwritten silently. That might not be what you want here.

This might be fairly straightforward using SQLite's backup utility: https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.backup

I just put together a version of this in a plugin: https://github.com/eyeseast/datasette-query-files. Happy to have any feedback.

Just to add a wrinkle here, this loads fine: https://alltheplaces-datasette.fly.dev/alltheplaces/places.geojson?_trace=1

But also, this doesn't add any trace data: https://alltheplaces-datasette.fly.dev/alltheplaces/places.json?_trace=1

What am I missing?

Where is _trace getting injected? And is it something a plugin should be able to handle? (If it is, I guess I should handle it in this case.)

Came here to check if this had been flagged already. Was helping a colleague get something on Cloud Run and had to dig to find --extra-options="--setting sql_time_limit_ms 2500".

If I get some time next week, maybe I'll try to tackle it. Would definitely make things easier to be able to do something like this:

sh datasette publish cloudrun something.db --setting sql_time_limit_ms 2500

Ha! That was your idea (and a good one).

But it's probably worth measuring to see what overhead it adds. It did require both passing in the database and making the whole thing async.

Just timing the queries themselves:

1. Using AsGeoJSON(geometry) as geometry takes 10.235 ms
2. Leaving as binary takes 8.63 ms

Looking at the network panel:

1. Takes about 200 ms for the fetch request
2. Takes about 300 ms

I'm not sure how best to time the GeoJSON generation, but it would be interesting to check. Maybe I'll write a plugin to add query times to response headers.

The other thing to consider with async streaming is that it might be well-suited for a slower response. When I have to get the whole result and send a response in a fixed amount of time, I need the most efficient query possible. If I can hang onto a connection and get things one chunk at a time, maybe it's ok if there's some overhead.

What if you split rendering and streaming into two things:

• render is a function that returns a response
• stream is a function that sends chunks, or yields chunks passed to an ASGI send callback

That way current plugins still work, and streaming is purely additive. A stream function could get a cursor or iterator of rows, instead of a list, so it could more efficiently handle large queries.

I wonder if this overlaps with what I outlined in #1605. You could run something like this:

sh datasette freeze -d exports/ aws s3 cp exports/ s3://my-export-bucket/$(date)

And maybe that does what you need. Of course, that plugin isn't built yet. But that's the idea.

Was looking through old issues and realized a bunch of this got discussed in #1101 (including by me!), so sorry to rehash all this. Happy to help with whatever piece of it I can. Would be very excited to be able to use format plugins with exports.

And just to think this through a little more, here's what stream_geojson might look like:

python async def stream_geojson(datasette, columns, rows, database, stream): db = datasette.get_database(database) for row in rows: feature = await row_to_geojson(row, db) stream.write(feature + "\n") # just assuming newline mode for now

Alternately, that could be an async generator, like this:

python async def stream_geojson(datasette, columns, rows, database): db = datasette.get_database(database) for row in rows: feature = await row_to_geojson(row, db) yield feature

Not sure which makes more sense, but I think this pattern would open up a lot of possibility. If you had your stream_indented_json function, you could do yield from stream_indented_json(rows, 2) and be one your way.

So maybe render_output_render returns something like this:

python @hookimpl def register_output_renderer(datasette): return { "extension": "geojson", "render": render_geojson, "stream": stream_geojson, "can_render": can_render_geojson, }

And stream gets an iterator, instead of a list of rows, so it can efficiently handle large queries. Maybe it also gets passed a destination stream, or it returns an iterator. I'm not sure what makes more sense. Either way, that might cover both CLI exports and streaming responses.

My best thought on how to differentiate them so far is plugins: if Datasette plugins that provide alternative outputs - like .geojson and .yml and suchlike - also work for the datasette query command that would make a lot of sense to me.

That's my thinking, too. It's really the thing I've been wanting since writing datasette-geojson, since I'm always exporting with datasette --get. The workflow I'm always looking for is something like this:

sh cd alltheplaces-datasette datasette query dunkin_in_suffolk -f geojson -o dunkin_in_suffolk.geojson

I think this probably needs either a new plugin hook separate from register_output_renderer or a way to use that without going through the HTTP stack. Or maybe a render mode that writes to a stream instead of a response. Maybe there's a new key in the dictionary that register_output_renderer returns that handles CLI exports.

Coming back to this. I was about to add a utility function to datasette-geojson to convert lat/lng columns to geometries. Thankfully I googled first. There's a SpatiaLite function for this: MakePoint.

sql select MakePoint(longitude, latitude) as geometry from places;

I'm not sure if that would work with conversions, since it needs two columns, but it's an option for tables that already have latitude, longitude columns.

Just ran into this issue last night. I have a big table that's mostly numbers, but also a zip code column in a state where ZIP codes start with 0. Would be great to run something like this:

sh sqlite-utils insert data.db places file.csv --csv --detect-types --type zipcode text

Maybe I'll take a crack at this one.

Good call on adding this to create-table, especially for stored columns. Having the stored/virtual split might make this tricky to implement, but I haven't gone any farther than thinking about what the CLI looks like. I'm going to try making the SQL side work first and figure that'll tell me more about what it needs.

I haven't tried this, but it looks like Pandas has a method for this: https://pandas.pydata.org/docs/reference/api/pandas.read_sql_query.html

Happy to do it and have it in the library. Going to use it a bunch. This whole SpatiaLite toolchain become a huge part of my work in the past year.

@simonw I think this is ready for a look.

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.

Yeah, the CLI experience is probably where any kind of multi-column, configured setup is going to fall apart. Sticking with GIS examples, one way I might think about this is using the fiona CLI:

```sh

assuming a database is already created and has SpatiaLite

fio cat boundary.shp | sqlite-utils insert boundaries --conversion geometry GeometryGeoJSON - ```

Anyway, very interested to see where you land here.

What if you did something like this:

```python

class Conversion: def init(self, args, *kwargs): "Put whatever settings you need here"

def python(self, row, column, value): # not sure on args here
    "Python step to transform value"
    return value

def sql(self, row, column, value):
    "Return the actual sql that goes in the insert/update step, and maybe params"
    # value is the return of self.python()
    return value, []

```

This way, you're always passing an instance, which has methods that do the conversion. (Or you're passing a SQL string, as you would now.) The __init__ could take column names, or SRID, or whatever other setup state you need per row, but the row is getting processed with the python and sql methods (or whatever you want to call them). This is pretty rough, so do what you will with names and args and such.

You'd then use it like this:

```python

subclass might be unneeded here, if methods are present

class LngLatConversion(Conversion): def init(self, x="longitude", y="latitude"): self.x = x self.y = y

def python(self, row, column, value):
    x = row[self.x]
    y = row[self.y]
    return x, y

def sql(self, row, column, value):
    # value is now a tuple, returned above
    s = "GeomFromText(POINT(? ?))"
    return s, value

table.insert_all(rows, conversions={"point": LngLatConversion("lng", "lat"))} ```

I haven't thought through all the implementation details here, and it'll probably break in ways I haven't foreseen, but wanted to get this idea out of my head. Hope it helps.

I've never used it either, but it's interesting, right? Feel like I should try it for something.

I'm trying to get my head around how this conversions feature might work, because I really like the idea of it.

I wonder if there's any overlap with the goals here and the sqlite3 module's concept of adapters and converters: https://docs.python.org/3/library/sqlite3.html#sqlite-and-python-types

I'm not sure that's exactly what we're talking about here, but it might be a parallel with some useful ideas to borrow.

I wonder if there are any interesting non-geospatial canned conversions that it would be worth including?

Off the top of my head:

• Un-nesting JSON objects into columns
• Splitting arrays
• Normalizing dates and times
• URL munging with urlparse
• Converting strings to numbers

Some of this is easy enough with SQL functions, some is easier in Python. Maybe that's where having pre-built classes gets really handy, because it saves you from thinking about which way it's implemented.

All this said, I don't think it's unreasonable to point people to dedicated tools like geojson-to-sqlite. If I'm dealing with a bunch of GeoJSON or Shapefiles, I need to something to read those anyway (or I need to figure out virtual tables). But something like this might make it easier to build those libraries, or standardize the underlying parts.

I like the idea of having stock conversions you could import. I'd actually move them to a dedicated module (call it sqlite_utils.conversions or something), because it's different from other utilities. Maybe they even take configuration, or they're composable.

```python from sqlite_utils.conversions import LongitudeLatitude

db["places"].insert( { "name": "London", "lng": -0.118092, "lat": 51.509865, }, conversions={"point": LongitudeLatitude("lng", "lat")}, ) ```

I would definitely use that for every CSV I get with lat/lng columns where I actually need GeoJSON.

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.

Awesome. Thanks for your help getting it in. Will now look at adding CLI versions of this. It's going to be super helpful on a bunch of my projects.

OK, I moved all the GIS helpers into db.py as methods on Database and Table, and I put find_spatialite back in utils.py. I deleted gis.py, since there's nothing left it. Docs and tests are updated and passing.

I think this is better.

Works for me. I was just looking at how the FTS extensions work and they're just methods, too. So this can be consistent with that.

from sqlite_utils.utils import find_spatialite is part of the documented API already:

https://sqlite-utils.datasette.io/en/3.22.1/python-api.html#finding-spatialite

To avoid needing to bump the major version number to 4 to indicate a backwards incompatible change, we should keep a from .gis import find_spatialite line at the top of utils.py such that any existing code with that documented import continues to work.

This is fixed now. I had to take out the type annotations for Database and Table to avoid a circular import, but that's fine and may be moot if these become class methods.

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.

I thought about adding these as methods on Database and Table, and I'm back and forth on it for the same reasons you are. It's certainly cleaner, and it's clearer what you're operating on. I could go either way.

I do sort of like having all the Spatialite stuff in its own module, just because it's built around an extension you might not have or want, but I don't know if that's a good reason to have a different API.

You could have init_spatialite add methods to Database and Table, so they're only there if you have Spatialite set up. Is that too clever? It feels too clever.

Fixed my spelling. That's a useful thing.

@simonw Not sure if you've seen this, but any chance you can run the tests?

Let me know if you want help prototyping any of this, because I'm thinking about it and trying stuff out. Happy to be a sounding board, if it helps.

Thinking about this more, as well as #1356 and various other tickets related to output formats, I think there's a missing plugin hook for formatting results, separate from register_output_renderer (or maybe part of it, depending on #1101).

Right now, as I understand it, getting output in any format goes through the normal view stack -- a table, a row or a query -- and so by the time register_output_renderer gets it, the results have already been truncated or paginated. What I'd want, I think, is to be able to register ways to format results independent of where those results are sent.

It's possible this could be done using conn.row_factory (maybe in the prepare_connection hook), but I'm not sure that's where it belongs.

Another option is some kind of registry of serializers, which register_output_renderer and other plugin hooks could use. What I'm trying to avoid here is writing a plugin that also needs plugins for formats I haven't thought of yet.

Right now, I usually have a line in a Makefile like this:

make combined.geojson: project.db pipenv run datasette project.db --get /project/combined.geojson \ --load-extension spatialite \ --setting sql_time_limit_ms 5000 \ --setting max_returned_rows 20000 \ -m metadata.yml > $@

That all assumes I've loaded whatever I need into project.db and created a canned query called combined (and then uses datasette-geojson for geojson output).

It works, but as you can see, it's a lot to manage, a lot of boilerplate, and it wasn't obvious how to get there. If there's an error in the canned query, I get an HTML error page, so that's hard to debug. And it's only one query, so each output needs a line like this. Make isn't ideal, either, for that reason.

The thing I really liked with datafreeze was doing templated filenames. I have a project now where I need to export a bunch of litttle geojson files, based on queries, and it would be awesome to be able to do something like this:

yml databases: project: queries: boundaries: sql: "SELECT * FROM boundaries" filename: "boundaries/{id}.geojson" mode: "item" format: geojson

And then do:

sh datasette freeze -m metadata.yml project.db

For HTML export, maybe there's a template argument, or format: template or something. And that gets you a static site generator, kinda for free.

I think both of these are Spatialite specific. They get generated when you first initialize the extension. KNN is actually deprecated in favor of KNN2, as I understand it.

@simonw I have a PR here https://github.com/simonw/sqlite-utils/pull/385 that adds Spatialite helpers on the Python side. Please let me know how it looks.

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.

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.

Some proposals I'd add to sqlite-utils:

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

python 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:

python 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:

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

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.

I'd use this. Right now, I tend to do touch my.db and then enable-wal or whatever else, but I'm never sure if that's a bad idea.

I've been thinking about this a bit lately, doing a project that involves moving a lot of data in and out of SQLite files, datasette and GeoJSON. This has me leaning toward the idea that something like datasette query would be a better place to do async queries.

I know there's a lot of overlap in sqlite-utils and datasette, and maybe keeping sqlite-utils synchronous would let datasette be entirely async and give a cleaner separation of implementations.

This would really help with this issue: https://github.com/eyeseast/datasette-geojson/issues/7

So, I do things like this a lot, too. I like the idea of piping in from stdin. Something like this would be nice to do in a makefile:

sh cat file.csv | sqlite-utils --csv --table data - 'SELECT * FROM data WHERE col="whatever"' > filtered.csv

If you assumed that you're always piping out the same format you're piping in, the option names don't have to change. Depends how much you want to change formats.

I'm happy to grab some or all of these in this PR, if you want.

(Putting thoughts here to keep the conversation in one place.)

I think using datasette for this use-case is the right approach. I usually have both datasette and sqlite-utils installed in the same project, and that's where I'm trying out queries, so it probably makes the most sense to have datasette also manage the output (and maybe the input, too).

It seems like both --get and --query could work better as subcommands, rather than options, if you're looking at building out a full CLI experience in datasette. It would give a cleaner separation in what you're trying to do and let each have its own dedicated options. So something like this:

```sh

run an arbitrary query

datasette query covid.db "select * from ny_times_us_counties limit 1" --format yaml

run a canned query

datasette get covid.db some-canned-query --format yaml ```

I think HTTPX gets it exactly right, with a clear separation between sync and async clients, each with a basically identical API. (I'm about to switch feed-to-sqlite over to it, from Requests, to eventually make way for async support.)

Mounting ./static at /static seems the simplest way. Saves you the trouble of deciding what else (img for example) gets special treatment.

I've almost suggested this same thing a couple times. I tend to have Makefile (because I'm doing other make stuff anyway to get data prepped), and I end up putting all those CLI options in something like make run. But it would be way easier to just have all those typical options -- plugins, templates, metadata -- be defaults.

This is exactly what I needed. Thank you.