factor out parent search from addr:street/addr:place

diff --git a/sql/functions/interpolation.sql b/sql/functions/interpolation.sql
index 8e6751b4a1cef4e206bd98b6d9c5892e0e740729..a797cad3ac1de74b3500eb53d90bbd2111b826f1 100644 (file)
--- 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 f5ed32e5dd1c9d2eb6e3a93c18c17c732b142166..cf8e63bc83068559c4b1ad6fba9b96cce2dc2f52 100644 (file)
--- 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 842849f2601ccc4ea8520171ad45d1189549ce52..d607635657a219a2a4333851f0f6bddc04d79135 100644 (file)
--- 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 a682931a1485fd61fd1f9e4f201e72eeba3caeeb..80eb12c566200ae889e96553dc30d76f6d9b0553 100644 (file)
--- 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 $$