compute address parts for rank 30 objects on the fly

Rank 30 objects usually use the address parts of their parent.
When the parent has address parts that are areas but not marked
as isaddress, then the parent might go through multiple administrative
areas. In that case recheck if the right area has been choosen
for the object in question instead of relying on isaddress.
Note that we really only have to do the recomputation in the
case of 'isarea = True and isaddress = False' which hopefully
keeps the number of additional geometric operations we have to do
to a minimum.

There is one more special case to be taken into account here: a
street may go through two administrative areas and a house along
that street is placed in one of the area while the addr:* tags
says it belongs to the other. In that case we must not switch
the isaddress to the one it is situated. To avoid that recheck
the address names against the name of the ara. That is not perfect
but should cover most cases.

Fixes #328.

diff --git a/sql/functions/address_lookup.sql b/sql/functions/address_lookup.sql
index b57ae040eb8931f55c5fa494c3e49a8853700b4b..1194022a7789d5e128814e3e9ec3e3df9f4e1949 100644 (file)
--- a/sql/functions/address_lookup.sql
+++ b/sql/functions/address_lookup.sql
@@ -90,6 +90,7 @@ DECLARE
   place RECORD;
   location RECORD;
   current_rank_address INTEGER;
+  location_isaddress BOOLEAN;
 BEGIN
   -- The place in question might not have a direct entry in place_addressline.
   -- Look for the parent of such places then and save if in for_place_id.
@@ -99,7 +100,8 @@ BEGIN
     SELECT parent_place_id as place_id, country_code,
            in_housenumber::text as housenumber, postcode,
            'place' as class, 'house' as type,
-           null as name, null::hstore as address
+           null as name, null::hstore as address,
+           centroid
       INTO place
       FROM location_property_osmline
       WHERE place_id = in_place_id
@@ -112,7 +114,8 @@ BEGIN
     SELECT parent_place_id as place_id, 'us' as country_code,
            in_housenumber::text as housenumber, postcode,
            'place' as class, 'house' as type,
-           null as name, null::hstore as address
+           null as name, null::hstore as address,
+           ST_Centroid(linegeo) as centroid
       INTO place
       FROM location_property_tiger
       WHERE place_id = in_place_id
@@ -125,7 +128,8 @@ BEGIN
     SELECT parent_place_id as place_id, 'us' as country_code,
            housenumber, postcode,
            'place' as class, 'house' as type,
-           null as name, null::hstore as address
+           null as name, null::hstore as address,
+           centroid
       INTO place
       FROM location_property_aux
       WHERE place_id = in_place_id;
@@ -137,7 +141,8 @@ BEGIN
     SELECT parent_place_id as place_id, country_code,
            null as housenumber, postcode,
            'place' as class, 'postcode' as type,
-           null as name, null::hstore as address
+           null as name, null::hstore as address,
+           null as centroid
       INTO place
       FROM location_postcode
       WHERE place_id = in_place_id;
@@ -148,7 +153,8 @@ BEGIN
     SELECT parent_place_id as place_id, country_code,
            housenumber, postcode,
            class, type,
-           name, address
+           name, address,
+           centroid
       INTO place
       FROM placex
       WHERE place_id = in_place_id and rank_search > 27;
@@ -161,7 +167,8 @@ BEGIN
     select coalesce(linked_place_id, place_id) as place_id,  country_code,
            housenumber, postcode,
            class, type,
-           null as name, address
+           null as name, address,
+           null as centroid
       INTO place
       FROM placex where place_id = in_place_id;
   END IF;
@@ -212,11 +219,17 @@ BEGIN
             AND linked_place_id is null
             AND (placex.country_code IS NULL OR place.country_code IS NULL
                  OR placex.country_code = place.country_code)
-      ORDER BY rank_address desc, (place_addressline.place_id = in_place_id) desc,
+      ORDER BY rank_address desc,
+               (place_addressline.place_id = in_place_id) desc,
+               (fromarea and place.centroid is not null and not isaddress
+                and (place.address is null or avals(name) && avals(place.address))
+                and ST_Contains(geometry, place.centroid)) desc,
                isaddress desc, fromarea desc,
                distance asc, rank_search desc
   LOOP
     -- RAISE WARNING '%',location;
+    location_isaddress := location.rank_address != current_rank_address;
+
     IF place.country_code IS NULL AND location.country_code IS NOT NULL THEN
       place.country_code := location.country_code;
     END IF;
@@ -231,20 +244,18 @@ BEGIN
       THEN
         place.postcode := null; -- remove the less exact postcode
       ELSE
-        location.isaddress := false;
+        location_isaddress := false;
       END IF;
     END IF;
     RETURN NEXT ROW(location.place_id, location.osm_type, location.osm_id,
-                           location.name, location.class, location.type,
-                           location.place_type,
-                           location.admin_level, location.fromarea,
-                           location.isaddress and location.rank_address != current_rank_address,
-                           location.rank_address,
-                           location.distance)::addressline;
-
-    IF location.isaddress THEN
-      current_rank_address := location.rank_address;
-    END IF;
+                    location.name, location.class, location.type,
+                    location.place_type,
+                    location.admin_level, location.fromarea,
+                    location_isaddress,
+                    location.rank_address,
+                    location.distance)::addressline;
+
+    current_rank_address := location.rank_address;
   END LOOP;
 
   -- If no country was included yet, add the name information from country_name.

diff --git a/test/bdd/db/import/addressing.feature b/test/bdd/db/import/addressing.feature
index 9050c19b0923c6cf10d388e7725fd347d0847fa1..5eea3de1dba264e041821eced1d873421c2bba45 100644 (file)
--- a/test/bdd/db/import/addressing.feature
+++ b/test/bdd/db/import/addressing.feature
@@ -397,3 +397,35 @@ Feature: Address computation
         Then results contain
            | osm_type | osm_id | name                    |
            | N        | 1      | Bolder, Wonderway, Left |
+
+    Scenario: POIs can correct address parts on the fly
+        Given the grid
+            | 1 |   |   |   |  2 |   | 5 |
+            |   |   |   | 9 |    | 8 |   |
+            | 4 |   |   |   |  3 |   | 6 |
+        And the places
+            | osm | class    | type           | admin | name  | geometry    |
+            | R1  | boundary | administrative | 8     | Left  | (1,2,3,4,1) |
+            | R2  | boundary | administrative | 8     | Right | (2,3,6,5,2) |
+        And the places
+            | osm | class   | type    | name      | geometry |
+            | W1  | highway | primary | Wonderway | 2,3      |
+            | N1  | amenity | cafe    | Bolder    | 9        |
+            | N2  | amenity | cafe    | Leftside  | 8        |
+        When importing
+        Then place_addressline contains
+           | object | address | isaddress |
+           | W1     | R1      | False     |
+           | W1     | R2      | True      |
+        And place_addressline doesn't contain
+           | object | address |
+           | N1     | R1      |
+           | N2     | R2      |
+        When searching for "Bolder"
+        Then results contain
+           | osm_type | osm_id | name                    |
+           | N        | 1      | Bolder, Wonderway, Left |
+        When searching for "Leftside"
+        Then results contain
+           | osm_type | osm_id | name                       |
+           | N        | 2      | Leftside, Wonderway, Right |