getNearestNamed*Feature functions better return values

diff --git a/sql/functions/aux_property.sql b/sql/functions/aux_property.sql
index b622f78bcb4503a9b0b3d5e624aa575173a81103..841f240a091e24f255181f29f78cdb88fd1eeacb 100644 (file)
--- a/sql/functions/aux_property.sql
+++ b/sql/functions/aux_property.sql
@@ -12,7 +12,7 @@ DECLARE
   out_partition INTEGER;
   out_parent_place_id BIGINT;
   location RECORD;
-  address_street_word_id INTEGER;
+  address_street_word_ids INTEGER[];
   out_postcode TEXT;
 
 BEGIN
@@ -21,11 +21,10 @@ BEGIN
   out_partition := get_partition(in_countrycode);
   out_parent_place_id := null;
 
-  address_street_word_id := get_name_id(make_standard_name(in_street));
-  IF address_street_word_id IS NOT NULL THEN
-    FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, address_street_word_id) LOOP
-      out_parent_place_id := location.place_id;
-    END LOOP;
+  address_street_word_ids := word_ids_from_name(in_street);
+  IF address_street_word_ids IS NOT NULL THEN
+    out_parent_place_id := getNearestNamedRoadFeature(out_partition, place_centroid,
+                                                      address_street_word_ids);
   END IF;
 
   IF out_parent_place_id IS NULL THEN

diff --git a/sql/functions/utils.sql b/sql/functions/utils.sql
index 80eb12c566200ae889e96553dc30d76f6d9b0553..741a3969e37f1732fc248a102a535bd00588c0ec 100644 (file)
--- a/sql/functions/utils.sql
+++ b/sql/functions/utils.sql
@@ -248,7 +248,6 @@ LANGUAGE plpgsql STABLE;
 -- \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)
@@ -263,9 +262,7 @@ BEGIN
     -- 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;
+      parent_place_id := getNearestNamedRoadFeature(partition, centroid, word_ids);
       IF parent_place_id is not null THEN
         --DEBUG: RAISE WARNING 'Get parent form addr:street: %', parent.place_id;
         RETURN parent_place_id;
@@ -277,9 +274,7 @@ BEGIN
   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;
+      parent_place_id := getNearestNamedPlaceFeature(partition, centroid, word_ids);
       IF parent_place_id is not null THEN
         --DEBUG: RAISE WARNING 'Get parent form addr:place: %', parent.place_id;
         RETURN parent_place_id;

diff --git a/sql/partition-functions.src.sql b/sql/partition-functions.src.sql
index f770e83e3d52143bacb0316ecff00bb359376b50..50f50db44919b027368c1a405c5943d4dab56bc5 100644 (file)
--- a/sql/partition-functions.src.sql
+++ b/sql/partition-functions.src.sql
@@ -82,54 +82,51 @@ END
 $$
 LANGUAGE plpgsql;
 
-create or replace function getNearestNamedRoadFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER[]) 
-  RETURNS setof nearfeature AS $$
+CREATE OR REPLACE FUNCTION getNearestNamedRoadFeature(in_partition INTEGER,
+                                                      point GEOMETRY,
+                                                      isin_token INTEGER[])
+  RETURNS BIGINT
+  AS $$
 DECLARE
-  r nearfeature%rowtype;
+  parent BIGINT;
 BEGIN
 
 -- start
   IF in_partition = -partition- THEN
-    FOR r IN 
-      SELECT place_id, name_vector, address_rank, search_rank,
-          ST_Distance(centroid, point) as distance, null as isguess
-          FROM search_name_-partition-
-          WHERE name_vector && isin_token
-          AND centroid && ST_Expand(point, 0.015)
-          AND search_rank between 26 and 27
-      ORDER BY distance ASC limit 1
-    LOOP
-      RETURN NEXT r;
-    END LOOP;
-    RETURN;
+    SELECT place_id FROM search_name_-partition-
+      INTO parent
+      WHERE name_vector && isin_token
+            AND centroid && ST_Expand(point, 0.015)
+            AND search_rank between 26 and 27
+      ORDER BY ST_Distance(centroid, point) ASC limit 1;
+    RETURN parent;
   END IF;
 -- end
 
   RAISE EXCEPTION 'Unknown partition %', in_partition;
 END
 $$
-LANGUAGE plpgsql;
+LANGUAGE plpgsql STABLE;
 
-create or replace function getNearestNamedPlaceFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER[]) 
-  RETURNS setof nearfeature AS $$
+CREATE OR REPLACE FUNCTION getNearestNamedPlaceFeature(in_partition INTEGER,
+                                                       point GEOMETRY,
+                                                       isin_token INTEGER[])
+  RETURNS BIGINT
+  AS $$
 DECLARE
-  r nearfeature%rowtype;
+  parent BIGINT;
 BEGIN
 
 -- start
   IF in_partition = -partition- THEN
-    FOR r IN 
-      SELECT place_id, name_vector, address_rank, search_rank,
-          ST_Distance(centroid, point) as distance, null as isguess
-          FROM search_name_-partition-
-          WHERE name_vector && isin_token
-          AND centroid && ST_Expand(point, 0.04)
-          AND search_rank between 16 and 22
-      ORDER BY distance ASC limit 1
-    LOOP
-      RETURN NEXT r;
-    END LOOP;
-    RETURN;
+    SELECT place_id
+      INTO parent
+      FROM search_name_-partition-
+      WHERE name_vector && isin_token
+            AND centroid && ST_Expand(point, 0.04)
+            AND search_rank between 16 and 22
+      ORDER BY ST_Distance(centroid, point) ASC limit 1;
+    RETURN parent;
   END IF;
 -- end
 

diff --git a/sql/tiger_import_start.sql b/sql/tiger_import_start.sql
index ef55e11cc58a4502814c0144778bfbe8cb139e69..c5246854cd740dc8331c0e9d9bda176e694ebfbe 100644 (file)
--- a/sql/tiger_import_start.sql
+++ b/sql/tiger_import_start.sql
@@ -2,7 +2,7 @@ DROP TABLE IF EXISTS location_property_tiger_import;
 CREATE TABLE location_property_tiger_import (linegeo GEOMETRY, place_id BIGINT, partition INTEGER, parent_place_id BIGINT, startnumber INTEGER, endnumber INTEGER, interpolationtype TEXT, postcode TEXT);
 
 CREATE OR REPLACE FUNCTION tiger_line_import(linegeo GEOMETRY, in_startnumber INTEGER, 
-  in_endnumber INTEGER, interpolationtype TEXT, 
+  in_endnumber INTEGER, interpolationtype TEXT,
   in_street TEXT, in_isin TEXT, in_postcode TEXT) RETURNS INTEGER
   AS $$
 DECLARE
@@ -14,7 +14,7 @@ DECLARE
   out_partition INTEGER;
   out_parent_place_id BIGINT;
   location RECORD;
-  address_street_word_id INTEGER;  
+  address_street_word_ids INTEGER[];
 
 BEGIN
 
@@ -56,11 +56,10 @@ BEGIN
   out_partition := get_partition('us');
   out_parent_place_id := null;
 
-  address_street_word_id := get_name_id(make_standard_name(in_street));
-  IF address_street_word_id IS NOT NULL THEN
-    FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, ARRAY[address_street_word_id]) LOOP
-      out_parent_place_id := location.place_id;
-    END LOOP;
+  address_street_word_ids := word_ids_from_name(in_street);
+  IF address_street_word_ids IS NOT NULL THEN
+    out_parent_place_id := getNearestNamedRoadFeature(out_partition, place_centroid,
+                                                      address_street_word_ids);
   END IF;
 
   IF out_parent_place_id IS NULL THEN