From df0142678a10eaf51f2acac40bcff6350211b305 Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Thu, 16 Jun 2022 10:44:16 +0200 Subject: [PATCH] improve address ordering with mixes of place and admin areas Resolves a couple of situations where a mixed use of places areas and administrative boundaries would result in a hierarchy that did not properly respect the contains relation. --- lib-sql/functions/placex_triggers.sql | 31 ++++++++--- test/bdd/db/import/addressing.feature | 20 +++---- test/bdd/db/import/rank_computation.feature | 58 +++++++++++++++++++++ 3 files changed, 93 insertions(+), 16 deletions(-) diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql index 50467cf5..a73df59c 100644 --- a/lib-sql/functions/placex_triggers.sql +++ b/lib-sql/functions/placex_triggers.sql @@ -864,11 +864,11 @@ BEGIN IF NEW.rank_address > 9 THEN -- Second check that the boundary is not completely contained in a - -- place area with a higher address rank + -- place area with a equal or 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 rank_address >= NEW.rank_address and geometry && NEW.geometry and geometry ~ NEW.geometry -- needed because ST_Relate does not do bbox cover test and ST_Relate(geometry, NEW.geometry, 'T*T***FF*') -- contains but not equal @@ -877,15 +877,32 @@ BEGIN NEW.rank_address := location.rank_address + 2; END LOOP; END IF; + ELSEIF NEW.class = 'place' + and ST_GeometryType(NEW.geometry) in ('ST_Polygon', 'ST_MultiPolygon') + and NEW.rank_address between 16 and 23 + THEN + -- For place areas make sure they are not completely contained in an area + -- with a equal or higher address rank. + FOR location IN + SELECT rank_address FROM placex + WHERE rank_address < 24 + and rank_address >= NEW.rank_address + and geometry && NEW.geometry + and geometry ~ NEW.geometry -- needed because ST_Relate does not do bbox cover test + 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 + 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). + -- If a place node is contained in an admin or place 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' + WHERE osm_type = 'R' and rank_address = NEW.rank_address and geometry && NEW.centroid and _ST_Covers(geometry, NEW.centroid) LIMIT 1 diff --git a/test/bdd/db/import/addressing.feature b/test/bdd/db/import/addressing.feature index 5ab748d9..1d6ba5bd 100644 --- a/test/bdd/db/import/addressing.feature +++ b/test/bdd/db/import/addressing.feature @@ -74,20 +74,22 @@ Feature: Address computation Scenario: boundary areas are preferred over place nodes in the address Given the grid - | 1 | | | | | | 3 | - | | 5 | | | | | | - | | 6 | | | | | | - | 2 | | | | | | 4 | + | 1 | | | | 10 | | 3 | + | | 5 | | | | | | + | | 6 | | | | | | + | 2 | | | | 11 | | 4 | And the named places - | osm | class | type | admin | geometry | - | N1 | place | square | 15 | 5 | - | N2 | place | city | 15 | 6 | - | R1 | place | city | 8 | (1,2,4,3,1) | + | osm | class | type | admin | geometry | + | N1 | place | square | 15 | 5 | + | N2 | place | city | 15 | 6 | + | R1 | place | city | 8 | (1,2,4,3,1) | + | R2 | boundary | administrative | 9 | (1,10,11,2,1) | When importing Then place_addressline contains | object | address | isaddress | cached_rank_address | | N1 | R1 | True | 16 | - | N1 | N2 | False | 16 | + | N1 | R2 | True | 18 | + | N1 | N2 | False | 18 | Scenario: place nodes outside a smaller ranked area are ignored Given the grid diff --git a/test/bdd/db/import/rank_computation.feature b/test/bdd/db/import/rank_computation.feature index f0dcfe16..1d4e2b82 100644 --- a/test/bdd/db/import/rank_computation.feature +++ b/test/bdd/db/import/rank_computation.feature @@ -197,3 +197,61 @@ Feature: Rank assignment | N20 | R22 | 16 | | N20 | R21 | 18 | + Scenario: Mixes of admin boundaries and place areas I + Given the grid + | 1 | | 10 | | | 2 | + | | 9 | | | | | + | 20| | 21 | | | | + | 4 | | 11 | | | 3 | + And the places + | osm | class | type | admin | name | geometry | + | R1 | boundary | administrative | 5 | Greater London | (1,2,3,4,1) | + | R2 | boundary | administrative | 8 | Kensington | (1,10,11,4,1) | + And the places + | osm | class | type | name | geometry | + | R10 | place | city | London | (1,2,3,4,1) | + | N9 | place | town | Fulham | 9 | + | W1 | highway | residential | Lots Grove | 20,21 | + When importing + Then placex contains + | object | rank_search | rank_address | + | R1 | 10 | 10 | + | R10 | 16 | 16 | + | R2 | 16 | 18 | + | N9 | 18 | 18 | + And place_addressline contains + | object | address | isaddress | cached_rank_address | + | W1 | R1 | True | 10 | + | W1 | R10 | True | 16 | + | W1 | R2 | True | 18 | + | W1 | N9 | False | 18 | + + + Scenario: Mixes of admin boundaries and place areas II + Given the grid + | 1 | | 10 | | 5 | 2 | + | | 9 | | | | | + | 20| | 21 | | | | + | 4 | | 11 | | 6 | 3 | + And the places + | osm | class | type | admin | name | geometry | + | R1 | boundary | administrative | 5 | Greater London | (1,2,3,4,1) | + | R2 | boundary | administrative | 8 | London | (1,5,6,4,1) | + And the places + | osm | class | type | name | geometry | + | R10 | place | city | Westminster | (1,10,11,4,1) | + | N9 | place | town | Fulham | 9 | + | W1 | highway | residential | Lots Grove | 20,21 | + When importing + Then placex contains + | object | rank_search | rank_address | + | R1 | 10 | 10 | + | R2 | 16 | 16 | + | R10 | 16 | 18 | + | N9 | 18 | 18 | + And place_addressline contains + | object | address | isaddress | cached_rank_address | + | W1 | R1 | True | 10 | + | W1 | R10 | True | 18 | + | W1 | R2 | True | 16 | + | W1 | N9 | False | 18 | -- 2.39.5