Open PeterBrodersen opened 3 weeks ago
Requests using ST_Difference()
seems extremely slow. Also, ST_CoveredBy()
to only get ways not part of any municipality is similarly slow.
-- Places outside?
SELECT name, ST_Difference(st_transform(ST_COLLECT(geom), 4326), (select ST_Union(wkb_geometry) from osmetymology.municipalities))
FROM osmetymology.osm_ways ow
ST_INTERSECTS(st_transform(ow.geom,4326), m.wkb_geometry)
WHERE name IS NOT NULL AND ("name:etymology" IS NOT NULL OR "name:etymology:wikipedia" IS NOT NULL OR "name:etymology:wikidata" is not NULL)
and not ST_CoveredBy(st_transform(geom, 4326), (select ST_Union(wkb_geometry) from osmetymology.municipalities))
and name like '%bro'
GROUP by name, "name:etymology", "name:etymology:wikipedia","name:etymology:wikidata"
As we cut up roads based on municipality polygons we lose roads not in any municipality.
We currently cut up roads in [import/aggregate.sql] using
ST_Intersects()
. This is matched against every municipality geometry. The purpose is to split up roads that span several municipalities, not remove those who are outside every municipality.Example: Christian D. IV's Bro![image](https://github.com/PeterBrodersen/osmetymology/assets/31605591/6b4b69d2-74ab-437f-81a6-4ec16cc0dfa2)
After the grouping and intersection only three tiny blue points appear (those on the boundary).![image](https://github.com/PeterBrodersen/osmetymology/assets/31605591/15d53af7-65ee-47b9-a4e5-16bab1d31f73)