SpatiaLite + Leaflet not displaying maps

[zaneselvans]

I think I must be misunderstanding something about how the GeoJSON based maps are meant to interact with SpatiaLite.

In order to get familiar with the spatial possibilities of Datasette, I used ogr2ogr to convert the US Census DP1 state/county/tract level geodatabase into an SQLite database, with the SpatiaLite extensions installed:

ogr2ogr -f SQLite census2010.db census2010.gdb

Then I attempted to serve this SpatiaLite database using Datasette, with the datasette-leaflet-geojson plugin installed:

datasette serve census2010.db --load-extension=spatialite

The geometry column (named shape) shows up as downloadable binaries, rather than maps, but I think this is maybe the expected behavior, as the datasette-leaflet-geojson plugin will only render maps if GeoJSON is found, right? So then I did a query, and used AsGeoJSON(shape) to try and create a GeoJSON column which would trigger map creation by the plugin, but instead all I got was a blank column...

select shape, AsGeoJSON(shape), geoid10, stusps10, name10 from state_2010census_dp1 order by stusps10

I went ahead and published it to GCP using:

datasette publish cloudrun \
    --memory=2Gi \
    --install=datasette-leaflet-geojson \
    --spatialite \
    --name censusdp1-datasette \
    census2010.db

and you can see the results of the query here.

None of this seems to have generated any visible errors in my browser on in the logs.

The behavior I expected was that the AsGeoJSON() call would convert the WKB geometry to a GeoJSON object, and then the datasette-leaflet-geojson plugin would pick it up and render maps of the converted geometries.

[zaneselvans]

Hmm, I tried adding a SpatiaLite layer in qGIS 3.16 by selecting the census2010.db database in and got this error:

Failure getting table metadata. Is /home/zane/code/catalyst/pudl-work/data/local/uscb/census2010/census2010.db really a SpatiaLite database?

[zaneselvans]

However in Python with GeoPandas this seems to work just fine:

import sqlalchemy as sa
import geopandas as gpd
dp1 = sa.create_engine("sqlite:///path/to/census2010.db")
states = gpd.read_postgis(
    """
    SELECT shape AS geom, geoid10, stusps10, name10
    FROM state_2010census_dp1
    ORDER BY stusps10
    """, dp1
)

[chris48s]

You should be able to test if AsGeoJSON() works on your spatialite DB locally. Try this python script:

import sqlite3

conn = sqlite3.connect('/path/to/census2010.db')
conn.enable_load_extension(True)
cur = conn.cursor()
cur.execute("SELECT load_extension('mod_spatialite');")  # this might need to be 'mod_spatialite.dylib' if you use a mac
cur.execute("SELECT AsGeoJSON(shape) FROM state_2010census_dp1 LIMIT 1;")
print(cur.fetchall()[0][0])

If you get the same behaviour locally (empty string or NULL or whatever), then the SQLite DB is probably the issue. If you see GeoJSON output, your DB is probably fine and the issue is with some aspect of datasette or the deployment.