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.

diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql
index 50467cf53fa5a21de088e29d7ab121897b9d0bdf..a73df59c40324f59768cf51d883715edf0c8a3dc 100644 (file)
--- 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 5ab748d93f23871a6bd51f6f469554c5da987f4c..1d6ba5bd23005b12037b0d9063735e61db99806b 100644 (file)
--- 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 f0dcfe168577cd5b70e6699110e0fa052474c7fb..1d4e2b821bbffe604d446600e7fbb18a68abf9d2 100644 (file)
--- 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                  |