GUnion to combine geometries in SpatiaLite

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:

http://127.0.0.1:8001/amtrak/stations?_size=1000

The table view includes a map of the USA showing all 500 stations as markers

But the track page was less useful, even with the ?_size=1000 parameter:

http://127.0.0.1:8001/amtrak/track?_size=1000

The track page shows the USA map but with only a small number of little black squiggles, not a full track map for the country

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.

Using GUnion

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

The as geometry is required because the mapping plugin looks for a column of that name.

Here's the result:

This time there is a map showing Amtrak track across the whole of the USA

This also works for queries that pull out a subset of the data. Here's the combination of every track in FRAREGIONS 7:

A map just of California showing Amtrak railways there

Using this query:

select GUnion(geometry) as geometry from track where "FRAREGIONS" = :p0

Different colours for different sections

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 the 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

Each region now shows as a different coloured line

Using sqlite-colorbrewer

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

The same map again but with a more pleasant colour scheme

Created 2022-04-12T11:58:17-07:00, updated 2024-01-12T15:41:49-08:00 · History · Edit