PostGIS fun - union some greenways

So I think to myself: Self, why don’t you stick the nearest 5 greenways in your park information? Great question self. After I tried it, I remembered (a) I had tried this before and (b) I didn’t get what I wanted.

1
2
3
4
5
6
7
8
9
SELECT
  trail_name,
  trail_surf
FROM
  greenways
ORDER BY
  geom < -> ST_SetSRID(st_makepoint(1447131, 506181), 2264)
LIMIT
  5
trail_name trail_surf
“Little Sugar Creek Greenway” “Packed Gravel”
“Little Sugar Creek Greenway” “Asphalt”
“Little Sugar Creek Greenway” “Asphalt”
“Little Sugar Creek Greenway” “Wood”
“Little Sugar Creek Greenway” “Asphalt”

Yeah, not great. It turns out the greenways are segmented in to 782 lines. For a good reason - the greenway suface is changing - but mostly it’s just trying to piss me off. I’d like to get the neareast 5 different greenways. I’d also like to retain the trail surface information for the barefoot running weirdos (no judgment). In other words, I want to group by the trail name and aggregate the geometry and surface types.

A little DuckDuckGoing and a few dozen SQL error messages later:

1
2
3
4
5
6
7
8
SELECT
  trail_name,
  string_agg(distinct(trail_surf), ', ') as trail_surf,
  ST_Multi(st_union(geom))::geometry(MultiLineString, 2264) as geom
FROM
  greenways
GROUP BY
  trail_name
trail_name trail_surf geom
“Antiquity Greenway” “Asphalt, Concrete” geom
“Barton Creek Greenway” “Concrete” geom
“Briar Creek Greenway” “Asphalt, Concrete, Packed Gravel, Wood” geom
“Caldwell Station Creek Greenway” “Asphalt, Concrete, Wood” geom
“Campbell Creek Greenway” “Asphalt, Concrete, Packed Gravel, Wood” geom
“Charlotte City Greenway” “Concrete” geom
… … …

782 records to 29! Breaking it down:

  • Aggregate the trail surface values via string_agg, using the distinct clause to eliminate duplicates.
  • Use ST_Union to stitch the lines together. The :: explicit bit sticks the correct information in the geometry_columns view (QGIS was displeased if I didn’t add this).
  • With the other two columns being aggregate functions, GROUP BY can be used on the trail names.

If greenways was a gigantic table, I’d dump the results into its own table for performance. But since it’s tiny, using it as a view doesn’t entail a painful performance hit (~160ms). I called the view greenways_union because naming things is hard. Now when I get the 5 closest greenways:

1
2
3
4
5
6
7
8
9
SELECT
  trail_name,
  trail_surf
FROM
  greenways_union
ORDER BY
  geom <-> ST_SetSRID(st_makepoint(1447131, 506181), 2264)
LIMIT
  5
trail_name trail_surf
“Little Sugar Creek Greenway” “Asphalt, Concrete, Packed Gravel, Wood”
“McMullen Creek Greenway” “Asphalt, Packed Gravel, Wood”
“Briar Creek Greenway” “Asphalt, Concrete, Packed Gravel, Wood”
“Lower McAlpine Creek Greenway” “Asphalt, Concrete, Wood”
“Four Mile Creek Greenway” “Asphalt, Concrete, Packed Gravel, Wood”

Did I mention I love Postgres and PostGIS? I love Postgres and PostGIS.