From e26a300c2ff79961a769dec29d1cd0752ffb0f3d Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Sat, 21 Mar 2020 22:43:28 +0100 Subject: [PATCH] use wikidata tag for linking places Having the same wikidata is a strong indicator that the same place is meant. There are some assignment errors where the wikidata does not link to the object itself but to something that mentions the place. To reduce errors there, prefer same name. --- sql/functions/placex_triggers.sql | 15 +++++++++++++++ sql/tables.sql | 1 + 2 files changed, 16 insertions(+) diff --git a/sql/functions/placex_triggers.sql b/sql/functions/placex_triggers.sql index 0fd0c3bc..5c04ad06 100644 --- a/sql/functions/placex_triggers.sql +++ b/sql/functions/placex_triggers.sql @@ -220,6 +220,21 @@ BEGIN END LOOP; END IF; + 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 wikidata-matching place node %', linked_placex.osm_id; + RETURN linked_placex; + END LOOP; + END IF; + -- Name searches can be done for ways as well as relations IF bnd_name is not null THEN --DEBUG: RAISE WARNING 'Looking for nodes with matching names'; diff --git a/sql/tables.sql b/sql/tables.sql index 28d664be..cf51cbe6 100644 --- 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; -- 2.39.5