Just a quick blog post on some coastline work I was doing.
For the OSMF Shortbread vector tiles I had to identify when coastlines has changed. The solution I came up isn’t specific to Shortbread, but is useful for anyone using the tiled ocean shapefiles.
I’m going to start by assuming that the old ocean data is in water_polygons
and the new data is in loading.water_polygons
. Other parts of my code already handle this. The shapefiles are loaded into tables that have the columns x int, y int, way geometry
.
To start I want to find any geometries that have changed. For geometries in the new data that aren’t in the old data, I can get this with a LEFT JOIN
. I want a set of geometries that includes any geometries from the new data that aren’t in the old. This set can be made by excluding any geometries in the old data that have identical x, y, and binary identical geometry to a new geometry. It’s possible this set includes extra geometries, but that’s okay.
A RIGHT JOIN
would find geometries in the old data that aren’t in the new. Combining these gives a FULL OUTER JOIN
. If I then collect the geometries in each shapefile tile I can compare them to find the geometries
SELECT ST_SymDifference(ST_Collect(old.way), ST_Collect(new.way)) AS dgeom
FROM water_polygons AS old
FULL OUTER JOIN loading.water_polygons AS new
ON old.way = new.way and old.x = new.x and old.y = new.y
WHERE new.way IS NULL OR old.way IS NULL
GROUP BY COALESCE(old.x, new.x), COALESCE(old.y, new.y)
This gets me the difference in geometries for the entire world in about two minutes. But I need tiles, which is it’s own complication.