X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/6cbef84cad0d08221d4a386cb5c3db19447ddede..1b1ed820c3af58de7281607a8b82f6a5dfe37319:/lib-sql/functions/placex_triggers.sql diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql index 6998224e..dba301cb 100644 --- a/lib-sql/functions/placex_triggers.sql +++ b/lib-sql/functions/placex_triggers.sql @@ -1,5 +1,52 @@ -- Trigger functions for the placex table. +-- Retrieve the data needed by the indexer for updating the place. +-- +-- Return parameters: +-- name list of names +-- address list of address tags, either from the object or a surrounding +-- building +-- country_feature If the place is a country feature, this contains the +-- country code, otherwise it is null. +CREATE OR REPLACE FUNCTION placex_prepare_update(p placex, + OUT name HSTORE, + OUT address HSTORE, + OUT country_feature VARCHAR) + AS $$ +BEGIN + -- For POI nodes, check if the address should be derived from a surrounding + -- building. + IF p.rank_search < 30 OR p.osm_type != 'N' OR p.address is not null THEN + RAISE WARNING 'self address for % %', p.osm_type, p.osm_id; + address := p.address; + ELSE + -- The additional && condition works around the misguided query + -- planner of postgis 3.0. + SELECT placex.address || hstore('_inherited', '') INTO address + FROM placex + WHERE ST_Covers(geometry, p.centroid) + and geometry && p.centroid + and placex.address is not null + and (placex.address ? 'housenumber' or placex.address ? 'street' or placex.address ? 'place') + and rank_search = 30 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') + LIMIT 1; + RAISE WARNING 'other address for % %: % (%)', p.osm_type, p.osm_id, address, p.centroid; + END IF; + + address := address - '_unlisted_place'::TEXT; + name := p.name; + + country_feature := CASE WHEN p.admin_level = 2 + and p.class = 'boundary' and p.type = 'administrative' + and p.osm_type = 'R' + THEN p.country_code + ELSE null + END; +END; +$$ +LANGUAGE plpgsql STABLE; + + -- Find the parent road of a POI. -- -- \returns Place ID of parent object or NULL if none @@ -169,7 +216,7 @@ BEGIN END IF; IF bnd.name ? 'name' THEN - bnd_name := make_standard_name(bnd.name->'name'); + bnd_name := lower(bnd.name->'name'); IF bnd_name = '' THEN bnd_name := NULL; END IF; @@ -180,12 +227,14 @@ BEGIN IF bnd.extratags ? 'place' and bnd_name is not null THEN FOR linked_placex IN SELECT * FROM placex - WHERE make_standard_name(name->'name') = bnd_name + WHERE (position(lower(name->'name') in bnd_name) > 0 + OR position(bnd_name in lower(name->'name')) > 0) AND placex.class = 'place' AND placex.type = bnd.extratags->'place' AND placex.osm_type = 'N' AND placex.linked_place_id is null AND placex.rank_search < 26 -- needed to select the right index - AND _st_covers(bnd.geometry, placex.geometry) + AND placex.type != 'postcode' + AND ST_Covers(bnd.geometry, placex.geometry) LOOP {% if debug %}RAISE WARNING 'Found type-matching place node %', linked_placex.osm_id;{% endif %} RETURN linked_placex; @@ -201,7 +250,7 @@ BEGIN AND placex.linked_place_id is null AND placex.rank_search < 26 AND _st_covers(bnd.geometry, placex.geometry) - ORDER BY make_standard_name(name->'name') = bnd_name desc + ORDER BY lower(name->'name') = bnd_name desc LOOP {% if debug %}RAISE WARNING 'Found wikidata-matching place node %', linked_placex.osm_id;{% endif %} RETURN linked_placex; @@ -213,7 +262,7 @@ BEGIN {% if debug %}RAISE WARNING 'Looking for nodes with matching names';{% endif %} FOR linked_placex IN SELECT placex.* from placex - WHERE make_standard_name(name->'name') = bnd_name + WHERE lower(name->'name') = bnd_name AND ((bnd.rank_address > 0 and bnd.rank_address = (compute_place_rank(placex.country_code, 'N', placex.class, @@ -221,9 +270,11 @@ BEGIN false, placex.postcode)).address_rank) OR (bnd.rank_address = 0 and placex.rank_search = bnd.rank_search)) AND placex.osm_type = 'N' + AND placex.class = 'place' AND placex.linked_place_id is null AND placex.rank_search < 26 -- needed to select the right index - AND _st_covers(bnd.geometry, placex.geometry) + AND placex.type != 'postcode' + AND ST_Covers(bnd.geometry, placex.geometry) LOOP {% if debug %}RAISE WARNING 'Found matching place node %', linked_placex.osm_id;{% endif %} RETURN linked_placex; @@ -393,10 +444,11 @@ BEGIN NEW.place_id := nextval('seq_place'); NEW.indexed_status := 1; --STATUS_NEW - NEW.country_code := lower(get_country_code(NEW.geometry)); + NEW.centroid := ST_PointOnSurface(NEW.geometry); + NEW.country_code := lower(get_country_code(NEW.centroid)); NEW.partition := get_partition(NEW.country_code); - NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry); + NEW.geometry_sector := geometry_sector(NEW.partition, NEW.centroid); IF NEW.osm_type = 'X' THEN -- E'X'ternal records should already be in the right format so do nothing @@ -527,8 +579,6 @@ DECLARE nameaddress_vector INTEGER[]; addr_nameaddress_vector INTEGER[]; - inherited_address HSTORE; - linked_node_id BIGINT; linked_importance FLOAT; linked_wikipedia TEXT; @@ -562,7 +612,6 @@ BEGIN -- update not necessary for osmline, cause linked_place_id does not exist NEW.extratags := NEW.extratags - 'linked_place'::TEXT; - NEW.address := NEW.address - '_unlisted_place'::TEXT; IF NEW.linked_place_id is not null THEN {% if debug %}RAISE WARNING 'place already linked to %', NEW.linked_place_id;{% endif %} @@ -746,27 +795,6 @@ BEGIN {% if debug %}RAISE WARNING 'finding street for % %', NEW.osm_type, NEW.osm_id;{% endif %} NEW.parent_place_id := null; - -- if we have a POI and there is no address information, - -- see if we can get it from a surrounding building - inherited_address := ''::HSTORE; - IF NEW.osm_type = 'N' AND addr_street IS NULL AND addr_place IS NULL - AND NEW.housenumber IS NULL THEN - FOR location IN - -- The additional && condition works around the misguided query - -- planner of postgis 3.0. - SELECT address from placex where ST_Covers(geometry, NEW.centroid) - and geometry && NEW.centroid - and (address ? 'housenumber' or address ? 'street' or address ? 'place') - and rank_search > 28 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') - limit 1 - LOOP - NEW.housenumber := location.address->'housenumber'; - addr_street := location.address->'street'; - addr_place := location.address->'place'; - inherited_address := location.address; - END LOOP; - END IF; - -- We have to find our parent road. NEW.parent_place_id := find_parent_for_poi(NEW.osm_type, NEW.osm_id, NEW.partition, @@ -819,12 +847,12 @@ BEGIN {% if not db.reverse_only %} IF array_length(name_vector, 1) is not NULL - OR inherited_address is not NULL OR NEW.address is not NULL + OR NEW.address is not NULL THEN SELECT * INTO name_vector, nameaddress_vector FROM create_poi_search_terms(NEW.place_id, NEW.partition, NEW.parent_place_id, - inherited_address || NEW.address, + NEW.address, NEW.country_code, NEW.housenumber, name_vector, NEW.centroid); @@ -840,6 +868,16 @@ BEGIN END IF; {% endif %} + -- If the address was inherited from a surrounding building, + -- do not add it permanently to the table. + IF NEW.address ? '_inherited' THEN + IF NEW.address ? '_unlisted_place' THEN + NEW.address := hstore('_unlisted_place', NEW.address->'_unlisted_place'); + ELSE + NEW.address := null; + END IF; + END IF; + RETURN NEW; END IF;