From a9e3329c39cc1124fa0dba9d97cbb45ea75f4dd5 Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Tue, 22 Feb 2022 23:35:48 +0100 Subject: [PATCH] country_name: use separate columns for names from OSM This allows us to distinguish between base names and imported ones and consiquently removing imported ones if necessary. --- data/country_name.sql | 1 + lib-sql/functions/address_lookup.sql | 14 +++++++++++++- lib-sql/functions/place_triggers.sql | 10 ---------- lib-sql/functions/placex_triggers.sql | 20 +++++++++++++------- 4 files changed, 27 insertions(+), 18 deletions(-) diff --git a/data/country_name.sql b/data/country_name.sql index 4dfdcaa6..c6fbcc92 100644 --- a/data/country_name.sql +++ b/data/country_name.sql @@ -5,6 +5,7 @@ CREATE TABLE public.country_name ( country_code character varying(2), name public.hstore, + derived_name public.hstore, country_default_language_code text, partition integer ); diff --git a/lib-sql/functions/address_lookup.sql b/lib-sql/functions/address_lookup.sql index 8715798e..f18970ba 100644 --- a/lib-sql/functions/address_lookup.sql +++ b/lib-sql/functions/address_lookup.sql @@ -108,6 +108,7 @@ CREATE OR REPLACE FUNCTION get_addressdata(in_place_id BIGINT, in_housenumber IN DECLARE place addressdata_place; location RECORD; + country RECORD; current_rank_address INTEGER; location_isaddress BOOLEAN; BEGIN @@ -198,6 +199,16 @@ BEGIN WHERE place_id = place.place_id LOOP --RAISE WARNING '%',location; + -- mix in default names for countries + IF location.rank_address = 4 and place.country_code is not NULL THEN + FOR country IN + SELECT coalesce(name, ''::hstore) as name FROM country_name + WHERE country_code = place.country_code LIMIT 1 + LOOP + place.name := country.name || place.name; + END LOOP; + END IF; + IF location.rank_address < 4 THEN -- no country locations for ranks higher than country place.country_code := NULL::varchar(2); @@ -272,7 +283,8 @@ BEGIN -- If no country was included yet, add the name information from country_name. IF current_rank_address > 4 THEN FOR location IN - SELECT name FROM country_name WHERE country_code = place.country_code LIMIT 1 + SELECT name || coalesce(derived_name, ''::hstore) as name FROM country_name + WHERE country_code = place.country_code LIMIT 1 LOOP --RAISE WARNING '% % %',current_rank_address,searchcountrycode,countryname; RETURN NEXT ROW(null, null, null, location.name, 'place', 'country', NULL, diff --git a/lib-sql/functions/place_triggers.sql b/lib-sql/functions/place_triggers.sql index 0bbd775e..9b968c3e 100644 --- a/lib-sql/functions/place_triggers.sql +++ b/lib-sql/functions/place_triggers.sql @@ -117,16 +117,6 @@ BEGIN -- ---- All other place types. - -- Patch in additional country names - IF NEW.admin_level = 2 and NEW.type = 'administrative' and NEW.address ? 'country' - THEN - FOR country IN - SELECT name FROM country_name WHERE country_code = lower(NEW.address->'country') - LOOP - NEW.name = country.name || NEW.name; - END LOOP; - END IF; - -- When an area is changed from large to small: log and discard change IF existing.geometry is not null AND ST_IsValid(existing.geometry) AND ST_Area(existing.geometry) > 0.02 diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql index 6ab73a3b..6ab3e84d 100644 --- a/lib-sql/functions/placex_triggers.sql +++ b/lib-sql/functions/placex_triggers.sql @@ -1044,16 +1044,22 @@ BEGIN AND NEW.class = 'boundary' AND NEW.type = 'administrative' AND NEW.country_code IS NOT NULL AND NEW.osm_type = 'R' THEN - -- Update the list of country names. Adding an additional sanity - -- check here: make sure the country does overlap with the area where - -- we expect it to be as per static country grid. + -- Update the list of country names. + -- Only take the name from the largest area for the given country code + -- in the hope that this is the authoritive one. + -- Also replace any old names so that all mapping mistakes can + -- be fixed through regular OSM updates. FOR location IN - SELECT country_code FROM country_osm_grid - WHERE ST_Covers(geometry, NEW.centroid) and country_code = NEW.country_code + SELECT osm_id FROM placex + WHERE rank_search = 4 and osm_type = 'R' + and country_code = NEW.country_code + ORDER BY ST_Area(geometry) desc LIMIT 1 LOOP - {% if debug %}RAISE WARNING 'Updating names for country '%' with: %', NEW.country_code, NEW.name;{% endif %} - UPDATE country_name SET name = name || NEW.name WHERE country_code = NEW.country_code; + IF location.osm_id = NEW.osm_id THEN + {% if debug %}RAISE WARNING 'Updating names for country '%' with: %', NEW.country_code, NEW.name;{% endif %} + UPDATE country_name SET derived_name = NEW.name WHERE country_code = NEW.country_code; + END IF; END LOOP; END IF; -- 2.39.5