I was playing around with datanews/amtrak-geojson, which provides GeoJSON for Amtrak stations and track segments.
I loaded it into SQLite using geojson-to-sqlite like this:
curl "https://raw.githubusercontent.com/datanews/amtrak-geojson/master/amtrak-stations.geojson" | \ geojson-to-sqlite /tmp/amtrak.db stations - --spatialite curl "https://raw.githubusercontent.com/datanews/amtrak-geojson/master/amtrak-track.geojson" | \ geojson-to-sqlite /tmp/amtrak.db track - --spatialite
Then I installed datasette-geojson-map in order to visualize it, and opened it in Datasette:
datasette install datasette-geojson-map datasette /tmp/amtrak.db --load-extension spatialite
The stations table rendered a map just fine. I added
?_size=1000 to the URL to see all of the stations rather than just the first 100:
But the track page was less useful, even with the
This is because there are 10,768 segments of track in the database, so even showing 1,000 at a time results in a very spotty map.
The solution was to combine the track segments together using the SpatiaLite GUnion function. I used the following custom SQL query:
select GUnion(geometry) as geometry from track
as geometry is required because the mapping plugin looks for a column of that name.
Here's the result:
This also works for queries that pull out a subset of the data. Here's the combination of every track in FRAREGIONS 7:
Using this query:
select GUnion(geometry) as geometry from track where "FRAREGIONS" = :p0
Thanks to faceting I noticed there are 8 different FRAREGIONS.
datasette-geojson-map supports styled map features, so I decided to try and show the different regions in different colours.
This query did th trick:
select 'FRA Region ' || FRAREGIONS as title, case FRAREGIONS when 1 then "#dfff00" when 2 then "#ffbf00" when 3 then "#ff7f50" when 4 then "#de3163" when 5 then "#9fe2bf" when 6 then "#40e0d0" when 7 then "#6495ed" when 8 then "#ccccff" else "#000000" end as stroke, GUnion(geometry) as geometry from track group by FRAREGIONS
datasette-geojson-map creator Chris Amico suggested using his sqlite-colorbrewer plugin to generate the colours. Here's the query using that instead:
select 'FRA Region ' || FRAREGIONS as title, colorbrewer('Paired', 9, FRAREGIONS) as stroke, GUnion(geometry) as geometry from track group by FRAREGIONS
Created 2022-04-12T11:58:17-07:00, updated 2022-04-12T12:49:15-07:00 · History · Edit