postcodes: strip leading country codes

[nominatim.git] / lib-sql / tiger_import_start.sql

diff --git a/lib-sql/tiger_import_start.sql b/lib-sql/tiger_import_start.sql
index 4b9c33fcbcb5e7650729e1ea34102e056ddf0af4..0ff534368d0929f6c58eb7b2fb6b2b6ab9d5402a 100644 (file)
--- a/lib-sql/tiger_import_start.sql
+++ b/lib-sql/tiger_import_start.sql
@@ -1,9 +1,23 @@
+-- SPDX-License-Identifier: GPL-2.0-only
+--
+-- This file is part of Nominatim. (https://nominatim.org)
+--
+-- Copyright (C) 2022 by the Nominatim developer community.
+-- For a full list of authors see the git log.
 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 TABLE location_property_tiger_import (
+  linegeo GEOMETRY,
+  place_id BIGINT,
+  partition INTEGER,
+  parent_place_id BIGINT,
+  startnumber INTEGER,
+  endnumber INTEGER,
+  step SMALLINT,
+  postcode TEXT);
 
-CREATE OR REPLACE FUNCTION tiger_line_import(linegeo GEOMETRY, in_startnumber INTEGER, 
-  in_endnumber INTEGER, interpolationtype TEXT,
-  in_street TEXT, in_isin TEXT, in_postcode TEXT) RETURNS INTEGER
+CREATE OR REPLACE FUNCTION tiger_line_import(linegeo GEOMETRY, in_startnumber INTEGER,
+                                             in_endnumber INTEGER, interpolationtype TEXT,
+                                             token_info JSONB, in_postcode TEXT) RETURNS INTEGER
   AS $$
 DECLARE
   startnumber INTEGER;
@@ -14,26 +28,26 @@ DECLARE
   out_partition INTEGER;
   out_parent_place_id BIGINT;
   location RECORD;
-  address_street_word_ids INTEGER[];
 
 BEGIN
 
   IF in_endnumber > in_startnumber THEN
-    startnumber = in_startnumber;
-    endnumber = in_endnumber;
+    startnumber := in_startnumber;
+    endnumber := in_endnumber;
   ELSE
-    startnumber = in_endnumber;
-    endnumber = in_startnumber;
+    startnumber := in_endnumber;
+    endnumber := in_startnumber;
+    linegeo := ST_Reverse(linegeo);
   END IF;
 
   IF startnumber < 0 THEN
-    RAISE WARNING 'Negative house number range (% to %) on %, %', startnumber, endnumber, in_street, in_isin;
+    RAISE WARNING 'Negative house number range (% to %)', startnumber, endnumber;
     RETURN 0;
   END IF;
 
   numberrange := endnumber - startnumber;
 
-  IF (interpolationtype = 'odd' AND startnumber%2 = 0) OR (interpolationtype = 'even' AND startnumber%2 = 1) THEN
+  IF (interpolationtype = 'odd' AND startnumber % 2 = 0) OR (interpolationtype = 'even' AND startnumber % 2 = 1) THEN
     startnumber := startnumber + 1;
     stepsize := 2;
   ELSE
@@ -45,22 +59,20 @@ BEGIN
   END IF;
 
   -- Filter out really broken tiger data
-  IF numberrange > 0 AND (numberrange::float/stepsize::float > 500) 
-                    AND ST_length(linegeo)/(numberrange::float/stepsize::float) < 0.000001 THEN
-    RAISE WARNING 'Road too short for number range % to % on %, % (%)',startnumber,endnumber,in_street,in_isin,
-                  ST_length(linegeo)/(numberrange::float/stepsize::float);    
+  IF numberrange > 0
+     and numberrange::float/stepsize::float > 500
+     and ST_length(linegeo)/(numberrange::float/stepsize::float) < 0.000001
+  THEN
+    RAISE WARNING 'Road too short for number range % to % (%)',startnumber,endnumber,
+                  ST_length(linegeo)/(numberrange::float/stepsize::float);
     RETURN 0;
   END IF;
 
   place_centroid := ST_Centroid(linegeo);
   out_partition := get_partition('us');
-  out_parent_place_id := null;
 
-  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;
+  out_parent_place_id := getNearestNamedRoadPlaceId(out_partition, place_centroid,
+                                                    token_info);
 
   IF out_parent_place_id IS NULL THEN
     SELECT getNearestParallelRoadFeature(out_partition, linegeo)
@@ -73,8 +85,12 @@ BEGIN
   END IF;
 
 --insert street(line) into import table
-insert into location_property_tiger_import (linegeo, place_id, partition, parent_place_id, startnumber, endnumber, interpolationtype, postcode)
-values (linegeo, nextval('seq_place'), out_partition, out_parent_place_id, startnumber, endnumber, interpolationtype, in_postcode);
+insert into location_property_tiger_import (linegeo, place_id, partition,
+                                            parent_place_id, startnumber, endnumber,
+                                            step, postcode)
+values (linegeo, nextval('seq_place'), out_partition,
+        out_parent_place_id, startnumber, endnumber,
+        stepsize, in_postcode);
 
   RETURN 1;
 END;