Upgrade Travis-CI from Ubuntu 18 to 20

[nominatim.git] / sql / tiger_import_start.sql

diff --git a/sql/tiger_import_start.sql b/sql/tiger_import_start.sql
index 5acb3e59d6a9e73cdcdf2a894b27ab8bba25b6ac..4b9c33fcbcb5e7650729e1ea34102e056ddf0af4 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, 
 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
   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;
   out_partition INTEGER;
   out_parent_place_id BIGINT;
   location RECORD;

-  address_street_word_id INTEGER;  
+  address_street_word_ids INTEGER[];

 
 BEGIN
 
 
 BEGIN
 


@@ -26,6 +26,11 @@ BEGIN
     endnumber = in_startnumber;
   END IF;
 
     endnumber = in_startnumber;
   END IF;
 

+  IF startnumber < 0 THEN
+    RAISE WARNING 'Negative house number range (% to %) on %, %', startnumber, endnumber, in_street, in_isin;
+    RETURN 0;
+  END IF;
+

   numberrange := endnumber - startnumber;
 
   IF (interpolationtype = 'odd' AND startnumber%2 = 0) OR (interpolationtype = 'even' AND startnumber%2 = 1) THEN
   numberrange := endnumber - startnumber;
 
   IF (interpolationtype = 'odd' AND startnumber%2 = 0) OR (interpolationtype = 'even' AND startnumber%2 = 1) THEN


@@ -51,23 +56,20 @@ BEGIN
   out_partition := get_partition('us');
   out_parent_place_id := null;
 
   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 := getNearestNamedRoadPlaceId(out_partition, place_centroid,
+                                                      address_street_word_ids);

   END IF;
 
   IF out_parent_place_id IS NULL THEN
   END IF;
 
   IF out_parent_place_id IS NULL THEN

-    FOR location IN SELECT place_id FROM getNearestParellelRoadFeature(out_partition, linegeo) LOOP
-      out_parent_place_id := location.place_id;
-    END LOOP;    
+    SELECT getNearestParallelRoadFeature(out_partition, linegeo)
+      INTO out_parent_place_id;

   END IF;
 
   IF out_parent_place_id IS NULL THEN
   END IF;
 
   IF out_parent_place_id IS NULL THEN

-    FOR location IN SELECT place_id FROM getNearestRoadFeature(out_partition, place_centroid) LOOP
-      out_parent_place_id := location.place_id;
-    END LOOP;    
+    SELECT getNearestRoadPlaceId(out_partition, place_centroid)
+      INTO out_parent_place_id;

   END IF;
 
 --insert street(line) into import table
   END IF;
 
 --insert street(line) into import table