From: Sarah Hoffmann Date: Thu, 16 Jan 2020 20:17:19 +0000 (+0100) Subject: factor out parent search from addr:street/addr:place X-Git-Tag: v3.5.0~96^2~7 X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/f863040b38a6c7586c06623715f1010c268b1f37 factor out parent search from addr:street/addr:place --- diff --git a/sql/functions/interpolation.sql b/sql/functions/interpolation.sql index 8e6751b4..a797cad3 100644 --- a/sql/functions/interpolation.sql +++ b/sql/functions/interpolation.sql @@ -14,7 +14,7 @@ LANGUAGE plpgsql IMMUTABLE; -- find the parent road of the cut road parts CREATE OR REPLACE FUNCTION get_interpolation_parent(wayid BIGINT, street TEXT, - place TEXT, partition INTEGER, + place TEXT, partition SMALLINT, centroid GEOMETRY, geom GEOMETRY) RETURNS BIGINT AS $$ @@ -22,7 +22,6 @@ DECLARE addr_street TEXT; addr_place TEXT; parent_place_id BIGINT; - address_street_word_ids INTEGER[]; waynodes BIGINT[]; @@ -44,23 +43,8 @@ BEGIN END LOOP; END IF; - IF addr_street IS NOT NULL THEN - address_street_word_ids := get_name_ids(make_standard_name(addr_street)); - IF address_street_word_ids IS NOT NULL THEN - FOR location IN SELECT place_id from getNearestNamedRoadFeature(partition, centroid, address_street_word_ids) LOOP - parent_place_id := location.place_id; - END LOOP; - END IF; - END IF; - - IF parent_place_id IS NULL AND addr_place IS NOT NULL THEN - address_street_word_ids := get_name_ids(make_standard_name(addr_place)); - IF address_street_word_ids IS NOT NULL THEN - FOR location IN SELECT place_id from getNearestNamedPlaceFeature(partition, centroid, address_street_word_ids) LOOP - parent_place_id := location.place_id; - END LOOP; - END IF; - END IF; + parent_place_id := find_parent_for_address(addr_street, addr_place, + partition, centroid); IF parent_place_id is null THEN FOR location IN SELECT place_id FROM placex diff --git a/sql/functions/normalization.sql b/sql/functions/normalization.sql index f5ed32e5..cf8e63bc 100644 --- a/sql/functions/normalization.sql +++ b/sql/functions/normalization.sql @@ -236,23 +236,7 @@ $$ LANGUAGE plpgsql STABLE; -CREATE OR REPLACE FUNCTION get_name_ids(lookup_word TEXT) - RETURNS INTEGER[] - AS $$ -DECLARE - lookup_token TEXT; - return_word_ids INTEGER[]; -BEGIN - lookup_token := ' '||trim(lookup_word); - SELECT array_agg(word_id) FROM word - WHERE word_token = lookup_token and class is null and type is null - INTO return_word_ids; - RETURN return_word_ids; -END; -$$ -LANGUAGE plpgsql STABLE; - - +-- Normalize a string and look up its name ids. CREATE OR REPLACE FUNCTION word_ids_from_name(lookup_word TEXT) RETURNS INTEGER[] AS $$ diff --git a/sql/functions/placex_triggers.sql b/sql/functions/placex_triggers.sql index 842849f2..d6076356 100644 --- a/sql/functions/placex_triggers.sql +++ b/sql/functions/placex_triggers.sql @@ -59,7 +59,6 @@ DECLARE parent_place_id BIGINT DEFAULT NULL; location RECORD; parent RECORD; - word_ids INTEGER[]; BEGIN --DEBUG: RAISE WARNING 'finding street for % %', poi_osm_type, poi_osm_id; @@ -85,29 +84,10 @@ BEGIN END LOOP; END LOOP; - -- Check for addr:street attributes - -- Note that addr:street links can only be indexed, once the street itself is indexed - word_ids := word_ids_from_name(addr_street); - IF word_ids is not null THEN - SELECT place_id - FROM getNearestNamedRoadFeature(poi_partition, near_centroid, word_ids) - INTO parent_place_id; - IF parent_place_id is not null THEN - --DEBUG: RAISE WARNING 'Get parent form addr:street: %', parent.place_id; - RETURN parent_place_id; - END IF; - END IF; - - -- Check for addr:place attributes. - word_ids := word_ids_from_name(addr_place); - IF word_ids is not null THEN - SELECT place_id - FROM getNearestNamedPlaceFeature(poi_partition, near_centroid, word_ids) - INTO parent_place_id; - IF parent_place_id is not null THEN - --DEBUG: RAISE WARNING 'Get parent form addr:place: %', parent.place_id; - RETURN parent_place_id; - END IF; + parent_place_id := find_parent_for_address(addr_street, addr_place, + poi_partition, near_centroid); + IF parent_place_id is not null THEN + RETURN parent_place_id; END IF; IF poi_osm_type = 'N' THEN diff --git a/sql/functions/utils.sql b/sql/functions/utils.sql index a682931a..80eb12c5 100644 --- a/sql/functions/utils.sql +++ b/sql/functions/utils.sql @@ -240,6 +240,58 @@ $$ LANGUAGE plpgsql STABLE; +-- Find the parent of an address with addr:street/addr:place tag. +-- +-- \param street Value of addr:street or NULL if tag is missing. +-- \param place Value of addr:place or NULL if tag is missing. +-- \param partition Partition where to search the parent. +-- \param centroid Location of the address. +-- +-- \return Place ID of the parent if one was found, NULL otherwise. +-- The returned parent is always a street (rank 26/27 and a way). +CREATE OR REPLACE FUNCTION find_parent_for_address(street TEXT, place TEXT, + partition SMALLINT, + centroid GEOMETRY) + RETURNS BIGINT + AS $$ +DECLARE + parent_place_id BIGINT; + word_ids INTEGER[]; +BEGIN + IF street is not null THEN + -- Check for addr:street attributes + -- Note that addr:street links can only be indexed, once the street itself is indexed + word_ids := word_ids_from_name(street); + IF word_ids is not null THEN + SELECT place_id + FROM getNearestNamedRoadFeature(partition, centroid, word_ids) + INTO parent_place_id; + IF parent_place_id is not null THEN + --DEBUG: RAISE WARNING 'Get parent form addr:street: %', parent.place_id; + RETURN parent_place_id; + END IF; + END IF; + END IF; + + -- Check for addr:place attributes. + IF place is not null THEN + word_ids := word_ids_from_name(place); + IF word_ids is not null THEN + SELECT place_id + FROM getNearestNamedPlaceFeature(partition, centroid, word_ids) + INTO parent_place_id; + IF parent_place_id is not null THEN + --DEBUG: RAISE WARNING 'Get parent form addr:place: %', parent.place_id; + RETURN parent_place_id; + END IF; + END IF; + END IF; + + RETURN NULL; +END; +$$ +LANGUAGE plpgsql STABLE; + CREATE OR REPLACE FUNCTION delete_location(OLD_place_id BIGINT) RETURNS BOOLEAN AS $$