Merge pull request #1720 from lonvia/better-linking-of-places

Use wikidata tags for improving linking of places with boundaries

diff --git a/sql/functions/placex_triggers.sql b/sql/functions/placex_triggers.sql
index d23d39d24de69addba9de207b1b99eea701f5cca..5c04ad065a9c58211e0bbb3912ac457f4adc76fa 100644 (file)
--- a/sql/functions/placex_triggers.sql
+++ b/sql/functions/placex_triggers.sql
@@ -220,30 +220,18 @@ BEGIN
     END LOOP;
   END IF;
 
-  -- Search for relation members with role admin_center.
-  IF bnd.osm_type = 'R' and bnd_name is not null
-     and relation_members is not null
-  THEN
-    FOR rel_member IN
-      SELECT get_rel_node_members(relation_members,
-                                ARRAY['admin_center','admin_centre']) as member
+  IF bnd.extratags ? 'wikidata' THEN
+    FOR linked_placex IN
+      SELECT * FROM placex
+      WHERE placex.class = 'place' AND placex.osm_type = 'N'
+        AND placex.extratags ? 'wikidata' -- needed to select right index
+        AND placex.extratags->'wikidata' = bnd.extratags->'wikidata'
+        AND placex.rank_search < 26
+        AND _st_covers(bnd.geometry, placex.geometry)
+      ORDER BY make_standard_name(name->'name') = bnd_name desc
     LOOP
-    --DEBUG: RAISE WARNING 'Found admin_center member %', rel_member.member;
-      FOR linked_placex IN
-        SELECT * from placex
-        WHERE osm_type = 'N' and osm_id = rel_member.member
-          and class = 'place'
-      LOOP
-        -- For an admin centre we also want a name match - still not perfect,
-        -- for example 'new york, new york'
-        -- But that can be fixed by explicitly setting the label in the data
-        IF bnd_name = make_standard_name(linked_placex.name->'name')
-           AND bnd.rank_address = linked_placex.rank_address
-        THEN
-          RETURN linked_placex;
-        END IF;
-          --DEBUG: RAISE WARNING 'Linked admin_center';
-      END LOOP;
+      --DEBUG: RAISE WARNING 'Found wikidata-matching place node %', linked_placex.osm_id;
+      RETURN linked_placex;
     END LOOP;
   END IF;
 
@@ -253,7 +241,8 @@ BEGIN
     FOR linked_placex IN
       SELECT placex.* from placex
       WHERE make_standard_name(name->'name') = bnd_name
-        AND placex.rank_address = bnd.rank_address
+        AND ((bnd.rank_address > 0 and placex.rank_address = bnd.rank_address)
+             OR (bnd.rank_address = 0 and placex.rank_search = bnd.rank_search))
         AND placex.osm_type = 'N'
         AND placex.rank_search < 26 -- needed to select the right index
         AND _st_covers(bnd.geometry, placex.geometry)

diff --git a/sql/tables.sql b/sql/tables.sql
index 28d664be436b0e993f3d75225e06f981bcdb2a7e..cf51cbe68b058ca6b066d2da226bc42271d9a486 100644 (file)
--- a/sql/tables.sql
+++ b/sql/tables.sql
@@ -177,6 +177,7 @@ CREATE INDEX idx_placex_linked_place_id ON placex USING BTREE (linked_place_id)
 CREATE INDEX idx_placex_rank_search ON placex USING BTREE (rank_search, geometry_sector) {ts:address-index};
 CREATE INDEX idx_placex_geometry ON placex USING GIST (geometry) {ts:search-index};
 CREATE INDEX idx_placex_adminname on placex USING BTREE (make_standard_name(name->'name')) {ts:address-index} WHERE osm_type='N' and rank_search < 26;
+CREATE INDEX idx_placex_wikidata on placex USING BTREE ((extratags -> 'wikidata')) {ts:address-index} WHERE extratags ? 'wikidata' and class = 'place' and osm_type = 'N' and rank_search < 26;
 
 DROP SEQUENCE IF EXISTS seq_place;
 CREATE SEQUENCE seq_place start 1;

diff --git a/test/bdd/api/search/queries.feature b/test/bdd/api/search/queries.feature
index 2969844c0720c4bc7834be28068baf90f9e91e06..e0f5846685cadb0d20d5d8c154b5e2dad9dc8a0d 100644 (file)
--- a/test/bdd/api/search/queries.feature
+++ b/test/bdd/api/search/queries.feature
@@ -26,7 +26,6 @@ Feature: Search queries
           | neighbourhood | Auenviertel |
           | suburb        | Eilbek |
           | postcode      | 22089 |
-          | city_district | Wandsbek |
           | city          | Hamburg |
           | country       | Deutschland |
           | country_code  | de |
@@ -42,7 +41,6 @@ Feature: Search queries
           | neighbourhood | Auenviertel |
           | suburb        | Eilbek |
           | postcode      | 22089 |
-          | city_district | Wandsbek |
           | city          | Hamburg |
           | country       | Deutschland |
           | country_code  | de |