From 788505095e9710369537f4b61cb4d40d8e5af580 Mon Sep 17 00:00:00 2001
From: Sarah Hoffmann <lonvia@denofr.de>
Date: Thu, 27 Jan 2022 11:54:12 +0100
Subject: [PATCH] add step column to tiger data table

This replaces the interpolationtype column.
---
 lib-sql/tables.sql              |  2 +-
 lib-sql/tiger_import_finish.sql |  2 +-
 lib-sql/tiger_import_start.sql  | 23 +++++++++++++++--------
 3 files changed, 17 insertions(+), 10 deletions(-)

diff --git a/lib-sql/tables.sql b/lib-sql/tables.sql
index 0c0f78fc..f554c58f 100644
--- 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 afe69c37..c02ce2a3 100644
--- 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 0ad38fbb..84992dfd 100644
--- 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;
-- 
2.39.5