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:

1
curl "https://raw.githubusercontent.com/datanews/amtrak-geojson/master/amtrak-stations.geojson" | \
2
  geojson-to-sqlite /tmp/amtrak.db stations - --spatialite
3
curl "https://raw.githubusercontent.com/datanews/amtrak-geojson/master/amtrak-track.geojson" | \
4
  geojson-to-sqlite /tmp/amtrak.db track - --spatialite

Then I installed datasette-geojson-map in order to visualize it, and opened it in Datasette:

1
datasette install datasette-geojson-map
2
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:

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

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

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

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

1
select
2
  'FRA Region ' || FRAREGIONS as title,
3
  case
4
    FRAREGIONS
5
    when 1 then "#dfff00"
6
    when 2 then "#ffbf00"
7
    when 3 then "#ff7f50"
8
    when 4 then "#de3163"
9
    when 5 then "#9fe2bf"
10
    when 6 then "#40e0d0"
11
    when 7 then "#6495ed"
12
    when 8 then "#ccccff"
13
    else "#000000"
14
  end as stroke,
15
  GUnion(geometry) as geometry
16
from
17
  track
18
group by
19
  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:

1
select
2
  'FRA Region ' || FRAREGIONS as title,
3
  colorbrewer('Paired', 9, FRAREGIONS) as stroke,
4
  GUnion(geometry) as geometry
5
from
6
  track
7
group by
8
  FRAREGIONS

The same map again but with a more pleasant colour scheme