X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/c3788d765ed4e7ddf00794085af757714bc102cf..399b04596e651f9cbcd8a340b1e13a48aededccb:/lib-sql/tiger_import_start.sql diff --git a/lib-sql/tiger_import_start.sql b/lib-sql/tiger_import_start.sql index 0ad38fbb..0ff53436 100644 --- a/lib-sql/tiger_import_start.sql +++ b/lib-sql/tiger_import_start.sql @@ -5,7 +5,15 @@ -- 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, @@ -24,11 +32,12 @@ DECLARE 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 @@ -50,8 +59,10 @@ 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 + 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; @@ -74,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;