add step column to tiger data table

This replaces the interpolationtype column.

diff --git a/lib-sql/tables.sql b/lib-sql/tables.sql
index 0c0f78fcba77e518314b1ce8b28a5c54bff9892d..f554c58f54ca4576e1b90f8a84559e59787c2c80 100644 (file)
--- a/lib-sql/tables.sql
+++ b/lib-sql/tables.sql
@@ -80,9 +80,9 @@ CREATE TABLE location_property_tiger (
   parent_place_id BIGINT,
   startnumber INTEGER,
   endnumber INTEGER,
+  step SMALLINT,
   partition SMALLINT,
   linegeo GEOMETRY,
-  interpolationtype TEXT,
   postcode TEXT);
 GRANT SELECT ON location_property_tiger TO "{{config.DATABASE_WEBUSER}}";
 

diff --git a/lib-sql/tiger_import_finish.sql b/lib-sql/tiger_import_finish.sql
index afe69c379494b1cf559a8072ec1d0adbdbf20b0f..c02ce2a3d79cc396c5bb9c33de4cdf38295847b6 100644 (file)
--- a/lib-sql/tiger_import_finish.sql
+++ b/lib-sql/tiger_import_finish.sql
@@ -9,7 +9,7 @@
 CREATE INDEX IF NOT EXISTS idx_location_property_tiger_parent_place_id_imp
   ON location_property_tiger_import (parent_place_id)
 {% if postgres.has_index_non_key_column %}
-  INCLUDE (startnumber, endnumber)
+  INCLUDE (startnumber, endnumber, step)
 {% endif %}
   {{db.tablespace.aux_index}};
 CREATE UNIQUE INDEX IF NOT EXISTS idx_location_property_tiger_place_id_imp

diff --git a/lib-sql/tiger_import_start.sql b/lib-sql/tiger_import_start.sql
index 0ad38fbb32a87329101586aabb8cdaf623980cee..84992dfdc1fec529278d151bc970b23fe30d2831 100644 (file)
--- a/lib-sql/tiger_import_start.sql
+++ b/lib-sql/tiger_import_start.sql
@@ -24,11 +24,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 +51,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 +77,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;