From: Sarah Hoffmann <lonvia@denofr.de>
Date: Mon, 28 Apr 2014 18:01:44 +0000 (+0200)
Subject: handle duplicate word entries correctly when looking for nearest street
X-Git-Tag: v2.3.0~49
X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/5882a15bac2c47ad0d27b2446f6390feded4f869

handle duplicate word entries correctly when looking for nearest street
---

diff --git a/sql/functions.sql b/sql/functions.sql
index e6ea4e8d..4b0483c3 100644
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -271,6 +271,20 @@ END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
 
+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 IMMUTABLE;
+
 CREATE OR REPLACE FUNCTION array_merge(a INTEGER[], b INTEGER[])
   RETURNS INTEGER[]
   AS $$
@@ -1260,6 +1274,7 @@ DECLARE
   search_maxrank INTEGER;
   address_maxrank INTEGER;
   address_street_word_id INTEGER;
+  address_street_word_ids INTEGER[];
   parent_place_id_rank BIGINT;
   
   isin TEXT[];
@@ -1505,18 +1520,18 @@ BEGIN
 --RAISE WARNING 'x3 %',NEW.parent_place_id;
 
       IF NEW.parent_place_id IS NULL AND NEW.street IS NOT NULL THEN
-        address_street_word_id := get_name_id(make_standard_name(NEW.street));
-        IF address_street_word_id IS NOT NULL THEN
-          FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_id) LOOP
+        address_street_word_ids := get_name_ids(make_standard_name(NEW.street));
+        IF address_street_word_ids IS NOT NULL THEN
+          FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
               NEW.parent_place_id := location.place_id;
           END LOOP;
         END IF;
       END IF;
 
       IF NEW.parent_place_id IS NULL AND NEW.addr_place IS NOT NULL THEN
-        address_street_word_id := get_name_id(make_standard_name(NEW.addr_place));
-        IF address_street_word_id IS NOT NULL THEN
-          FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_id) LOOP
+        address_street_word_ids := get_name_id(make_standard_name(NEW.addr_place));
+        IF address_street_word_ids IS NOT NULL THEN
+          FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
             NEW.parent_place_id := location.place_id;
           END LOOP;
         END IF;
diff --git a/sql/partition-functions.src.sql b/sql/partition-functions.src.sql
index 235e21a2..8857e766 100644
--- a/sql/partition-functions.src.sql
+++ b/sql/partition-functions.src.sql
@@ -111,7 +111,7 @@ END
 $$
 LANGUAGE plpgsql;
 
-create or replace function getNearestNamedRoadFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER) 
+create or replace function getNearestNamedRoadFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER[]) 
   RETURNS setof nearfeature AS $$
 DECLARE
   r nearfeature%rowtype;
@@ -123,7 +123,7 @@ BEGIN
       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 @> ARRAY[isin_token]
+          WHERE name_vector @> isin_token
           AND ST_DWithin(centroid, point, 0.01) 
           AND search_rank between 26 and 27
       ORDER BY distance ASC limit 1
@@ -139,7 +139,7 @@ END
 $$
 LANGUAGE plpgsql;
 
-create or replace function getNearestNamedPlaceFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER) 
+create or replace function getNearestNamedPlaceFeature(in_partition INTEGER, point GEOMETRY, isin_token INTEGER[]) 
   RETURNS setof nearfeature AS $$
 DECLARE
   r nearfeature%rowtype;
@@ -151,7 +151,7 @@ BEGIN
       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 @> ARRAY[isin_token]
+          WHERE name_vector @> isin_token
           AND ST_DWithin(centroid, point, 0.03) 
           AND search_rank between 16 and 22
       ORDER BY distance ASC limit 1