From 7e9412a044b616c036c632d231b29c18cee7720a Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Wed, 14 Oct 2020 11:33:47 +0200 Subject: [PATCH] demote admin boundaries for place areas Also demote the address rank of an admin boundary when there is a place area of higher rank that completely contains the area. This catches the case where city boundaries do not exactly align with administrative units (see for example Moscow). --- sql/functions/placex_triggers.sql | 19 ++++++++++++++++--- 1 file changed, 16 insertions(+), 3 deletions(-) diff --git a/sql/functions/placex_triggers.sql b/sql/functions/placex_triggers.sql index d8472824..e8e9bdb6 100644 --- a/sql/functions/placex_triggers.sql +++ b/sql/functions/placex_triggers.sql @@ -609,6 +609,7 @@ BEGIN IF NEW.class = 'boundary' and NEW.type = 'administrative' and NEW.osm_type = 'R' and NEW.rank_address > 0 THEN + -- First, check that admin boundaries do not overtake each other rank-wise. parent_address_level := get_parent_address_level(NEW.centroid, NEW.admin_level); IF parent_address_level >= NEW.rank_address THEN IF parent_address_level >= 24 THEN @@ -617,12 +618,24 @@ BEGIN NEW.rank_address := parent_address_level + 2; END IF; END IF; - -- If a place node is contained in a admin boundary with the same address level - -- and has not been linked, then make the node a subpart by increasing the - -- address rank (city level and above). + -- Second check that the boundary is not completely contained in a + -- place area with a higher address rank + FOR location IN + SELECT rank_address FROM placex + WHERE class = 'place' and rank_address < 24 + and rank_address > NEW.rank_address + and geometry && NEW.geometry + and ST_Relate(geometry, NEW.geometry, 'T*T***FF*') -- contains but not equal + ORDER BY rank_address desc LIMIT 1 + LOOP + NEW.rank_address := location.rank_address + 2; + END LOOP; ELSEIF NEW.class = 'place' and NEW.osm_type = 'N' and NEW.rank_address between 16 and 23 THEN + -- If a place node is contained in a admin boundary with the same address level + -- and has not been linked, then make the node a subpart by increasing the + -- address rank (city level and above). FOR location IN SELECT rank_address FROM placex WHERE osm_type = 'R' and class = 'boundary' and type = 'administrative' -- 2.39.5