rewrite address interpolation so that planet_osm_node is not needed

This allows address interpolations to work correctly when flatnode storage
is used for node coordinates.

To fix interpolations in an existing database, follow these steps:

  * invalidate all interpolations (in psql):
    `UPDATE placex SET indexed_status=2 WHERE rank_search = 28`
  * disable updates:
    ./utils/setup.php --create-functions --create-partition-functions
  * reindex the whole lot:
    ./utils/update.php --index --index-instances <number of your cpus>
  * enable updates again:
    ./utils/setup.php --create-functions --enable-diff-updates --create-partition-functions

diff --git a/sql/functions.sql b/sql/functions.sql
index e2bfbbbccf76b6bb29c16b140841e83dd76bbb68..dc69280446a333d4352175049c53c31d69c1f130 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -791,7 +791,7 @@ LANGUAGE plpgsql;
 CREATE OR REPLACE FUNCTION create_interpolation(wayid BIGINT, interpolationtype TEXT) RETURNS INTEGER
   AS $$
 DECLARE
 CREATE OR REPLACE FUNCTION create_interpolation(wayid BIGINT, interpolationtype TEXT) RETURNS INTEGER
   AS $$
 DECLARE

-  
+

   newpoints INTEGER;
   waynodes BIGINT[];
   nodeid BIGINT;
   newpoints INTEGER;
   waynodes BIGINT[];
   nodeid BIGINT;


@@ -804,117 +804,103 @@ DECLARE
   originalnumberrange INTEGER;
   housenum INTEGER;
   linegeo GEOMETRY;
   originalnumberrange INTEGER;
   housenum INTEGER;
   linegeo GEOMETRY;

+  splitline GEOMETRY;
+  sectiongeo GEOMETRY;

   search_place_id BIGINT;
   defpostalcode TEXT;
 
   search_place_id BIGINT;
   defpostalcode TEXT;
 

-  havefirstpoint BOOLEAN;
-  linestr TEXT;

 BEGIN
 BEGIN

-  newpoints := 0;
-  IF interpolationtype = 'odd' OR interpolationtype = 'even' OR interpolationtype = 'all' THEN
-
-    select postcode from placex where osm_type = 'W' and osm_id = wayid INTO defpostalcode;
-    select nodes from planet_osm_ways where id = wayid INTO waynodes;
---RAISE WARNING 'interpolation % % %',wayid,interpolationtype,waynodes;
-    IF array_upper(waynodes, 1) IS NOT NULL THEN
-
-      havefirstpoint := false;
-
-      FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
-
-        -- If there is a place of a type other than place/house, use that because
-        -- it is guaranteed to be the original node. For place/house types use the
-        -- one with the smallest id because the original node was created first.
-        -- Ignore all nodes marked for deletion. (Might happen when the type changes.)
-        select place_id from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT and indexed_status < 100 order by (type = 'house'),place_id limit 1 INTO search_place_id;
-        IF search_place_id IS NULL THEN
-          -- if no such node exists, create a record of the right type
-          select * from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT and type = 'house' limit 1 INTO nextnode;
-          select ST_SetSRID(ST_Point(lon::float/10000000,lat::float/10000000),4326) from planet_osm_nodes where id = waynodes[nodeidpos] INTO nextnode.geometry;
-          IF nextnode.geometry IS NULL THEN
-            -- we don't have any information about this point, most likely
-            -- because an excerpt was updated and the node never imported
-            -- because the interpolation is outside the region of the excerpt.
-            -- Give up.
-            RETURN newpoints;
-          END IF;
-        ELSE
-          select * from placex where place_id = search_place_id INTO nextnode;
-        END IF;
+  IF interpolationtype = 'odd' OR interpolationtype = 'even' THEN
+    stepsize := 2;
+  ELSEIF interpolationtype = 'all' THEN
+    stepsize := 1;
+  ELSEIF interpolationtype ~ '^\d+$' THEN
+    stepsize := interpolationtype::INTEGER;
+  ELSE
+    RETURN 0;
+  END IF;

 
 

---RAISE WARNING 'interpolation node % % % ',nextnode.housenumber,ST_X(nextnode.geometry),ST_Y(nextnode.geometry);
-      
-        IF havefirstpoint THEN
+  select nodes from planet_osm_ways where id = wayid INTO waynodes;

 
 

-          -- add point to the line string
-          linestr := linestr||','||ST_X(nextnode.geometry)||' '||ST_Y(nextnode.geometry);
-          endnumber := ('0'||substring(nextnode.housenumber,'[0-9]+'))::integer;
+  IF array_upper(waynodes, 1) IS NULL THEN
+    RETURN 0;
+  END IF;

 
 

-          IF startnumber IS NOT NULL and startnumber > 0 AND endnumber IS NOT NULL and endnumber > 0 AND @(startnumber - endnumber) < 1000 THEN
+  select postcode, geometry from placex where osm_type = 'W' and osm_id = wayid 
+    INTO defpostalcode, linegeo;

 
 

---RAISE WARNING 'interpolation end % % ',nextnode.place_id,endnumber;
+  IF ST_GeometryType(linegeo) != 'ST_LineString' THEN
+    RETURN 0;
+  END IF;

 
 

-            IF startnumber != endnumber THEN
+  startnumber := NULL;
+  newpoints := 0;

 
 

-              linestr := linestr || ')';
---RAISE WARNING 'linestr %',linestr;
-              linegeo := ST_GeomFromText(linestr,4326);
-              linestr := 'LINESTRING('||ST_X(nextnode.geometry)||' '||ST_Y(nextnode.geometry);
-              IF (startnumber > endnumber) THEN
-                housenum := endnumber;
-                endnumber := startnumber;
-                startnumber := housenum;
-                linegeo := ST_Reverse(linegeo);
-              END IF;
-              orginalstartnumber := startnumber;
-              originalnumberrange := endnumber - startnumber;
-
--- Too much broken data worldwide for this test to be worth using
---              IF originalnumberrange > 500 THEN
---                RAISE WARNING 'Number block of % while processing % %', originalnumberrange, prevnode, nextnode;
---              END IF;
-
-              IF (interpolationtype = 'odd' AND startnumber%2 = 0) OR (interpolationtype = 'even' AND startnumber%2 = 1) THEN
-                startnumber := startnumber + 1;
-                stepsize := 2;
-              ELSE
-                IF (interpolationtype = 'odd' OR interpolationtype = 'even') THEN
-                  startnumber := startnumber + 2;
-                  stepsize := 2;
-                ELSE -- everything else assumed to be 'all'
-                  startnumber := startnumber + 1;
-                  stepsize := 1;
-                END IF;
-              END IF;
-              endnumber := endnumber - 1;
-              delete from placex where osm_type = 'N' and osm_id = prevnode.osm_id and type = 'house' and place_id != prevnode.place_id;
-              FOR housenum IN startnumber..endnumber BY stepsize LOOP
-                -- this should really copy postcodes but it puts a huge burdon on the system for no big benefit
-                -- ideally postcodes should move up to the way
-                insert into placex (osm_type, osm_id, class, type, admin_level, housenumber, street, addr_place, isin, postcode,
-                  country_code, parent_place_id, rank_address, rank_search, indexed_status, geometry)
-                  values ('N',prevnode.osm_id, 'place', 'house', prevnode.admin_level, housenum, prevnode.street, prevnode.addr_place, prevnode.isin, coalesce(prevnode.postcode, defpostalcode),
-                  prevnode.country_code, prevnode.parent_place_id, prevnode.rank_address, prevnode.rank_search, 1, ST_LineInterpolatePoint(linegeo, (housenum::float-orginalstartnumber::float)/originalnumberrange::float));
-                newpoints := newpoints + 1;
---RAISE WARNING 'interpolation number % % ',prevnode.place_id,housenum;
-              END LOOP;
-            END IF;
-            havefirstpoint := false;
+  FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
+
+    -- If there is a place of a type other than place/house, use that because
+    -- it is guaranteed to be the original node. For place/house types use the
+    -- one with the smallest id because the original node was created first.
+    -- Ignore all nodes marked for deletion. (Might happen when the type changes.)
+    select place_id from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT and indexed_status < 100 order by (type = 'house'),place_id limit 1 INTO search_place_id;
+    IF search_place_id IS NOT NULL THEN
+      select * from placex where place_id = search_place_id INTO nextnode;
+
+        IF nodeidpos < array_upper(waynodes, 1) THEN
+          -- Make sure that the point is actually on the line. That might
+          -- be a bit paranoid but ensures that the algorithm still works
+          -- should osm2pgsql attempt to repair geometries.
+          splitline := split_line_on_node(linegeo, nextnode.geometry);
+          sectiongeo := ST_GeometryN(splitline, 1);
+          IF ST_GeometryType(ST_GeometryN(splitline, 2)) = 'ST_LineString' THEN
+            linegeo := ST_GeometryN(splitline, 2);

           END IF;
           END IF;

+        ELSE
+          sectiongeo = linegeo;

         END IF;
         END IF;

+        endnumber := substring(nextnode.housenumber,'[0-9]+')::integer;

 
 

-        IF NOT havefirstpoint THEN
-          startnumber := ('0'||substring(nextnode.housenumber,'[0-9]+'))::integer;
-          IF startnumber IS NOT NULL AND startnumber > 0 THEN
-            havefirstpoint := true;
-            linestr := 'LINESTRING('||ST_X(nextnode.geometry)||' '||ST_Y(nextnode.geometry);
-            prevnode := nextnode;
+        IF startnumber IS NOT NULL AND endnumber IS NOT NULL
+           AND @(startnumber - endnumber) < 1000 AND startnumber != endnumber
+           AND ST_GeometryType(linegeo) = 'ST_LineString' THEN
+
+          IF (startnumber > endnumber) THEN
+            housenum := endnumber;
+            endnumber := startnumber;
+            startnumber := housenum;
+            sectiongeo := ST_Reverse(sectiongeo);

           END IF;
           END IF;

---RAISE WARNING 'interpolation start % % ',nextnode.place_id,startnumber;
+          orginalstartnumber := startnumber;
+          originalnumberrange := endnumber - startnumber;
+
+          startnumber := startnumber + stepsize;
+          -- correct for odd/even
+          IF (interpolationtype = 'odd' AND startnumber%2 = 0) OR (interpolationtype = 'even' AND startnumber%2 = 1) THEN
+            startnumber := startnumber - 1;
+          END IF;
+          endnumber := endnumber - 1;
+
+          delete from placex where osm_type = 'N' and osm_id = prevnode.osm_id and type = 'house' and place_id != prevnode.place_id;
+          FOR housenum IN startnumber..endnumber BY stepsize LOOP
+            -- this should really copy postcodes but it puts a huge burden on
+            -- the system for no big benefit ideally postcodes should move up to the way
+            insert into placex (osm_type, osm_id, class, type, admin_level,
+              housenumber, street, addr_place, isin, postcode,
+              country_code, parent_place_id, rank_address, rank_search,
+              indexed_status, geometry)
+              values ('N', prevnode.osm_id, 'place', 'house', prevnode.admin_level,
+              housenum, prevnode.street, prevnode.addr_place, prevnode.isin, coalesce(prevnode.postcode, defpostalcode),
+              prevnode.country_code, prevnode.parent_place_id, prevnode.rank_address, prevnode.rank_search,
+              1, ST_LineInterpolatePoint(sectiongeo, (housenum::float-orginalstartnumber::float)/originalnumberrange::float));
+            newpoints := newpoints + 1;
+--RAISE WARNING 'interpolation number % % ',prevnode.place_id,housenum;
+          END LOOP;

         END IF;
         END IF;

-      END LOOP;
+
+        startnumber := substring(nextnode.housenumber,'[0-9]+')::integer;
+        prevnode := nextnode;

     END IF;
     END IF;

-  END IF;
+  END LOOP;

 
 --RAISE WARNING 'interpolation points % ',newpoints;
 
 
 --RAISE WARNING 'interpolation points % ',newpoints;
 

diff --git a/sql/postgis_15_aux.sql b/sql/postgis_15_aux.sql
new file mode 100644 (file)
index 0000000..548e0fe
--- /dev/null
+++ b/sql/postgis_15_aux.sql
@@ -0,0 +1,14 @@
+-- Splits the line at the given point and returns the two parts
+-- in a multilinestring.
+CREATE OR REPLACE FUNCTION split_line_on_node(line GEOMETRY, point GEOMETRY)
+RETURNS GEOMETRY
+  AS $$
+DECLARE
+  frac FLOAT;
+BEGIN
+  frac := ST_Line_Locate_Point(line, point);
+  RETURN ST_Collect(ST_Line_Substring(line, 0, frac),
+         ST_Line_Substring(line, frac, 1));
+END
+$$
+LANGUAGE plpgsql;

diff --git a/sql/postgis_20_aux.sql b/sql/postgis_20_aux.sql
new file mode 100644 (file)
index 0000000..dc74e09
--- /dev/null
+++ b/sql/postgis_20_aux.sql
@@ -0,0 +1,10 @@
+-- Splits the line at the given point and returns the two parts
+-- in a multilinestring.
+CREATE OR REPLACE FUNCTION split_line_on_node(line GEOMETRY, point GEOMETRY)
+RETURNS GEOMETRY
+  AS $$
+BEGIN
+  RETURN ST_Split(line, ST_ClosestPoint(line, point));
+END;
+$$
+LANGUAGE plpgsql;

diff --git a/tests/features/db/import/interpolation.feature b/tests/features/db/import/interpolation.feature
new file mode 100644 (file)
index 0000000..9a4856f
--- /dev/null
+++ b/tests/features/db/import/interpolation.feature
@@ -0,0 +1,329 @@
+@DB
+Feature: Import of address interpolations
+    Tests that interpolated addresses are added correctly
+
+    Scenario: Simple even two point interpolation
+        Given the place nodes
+          | osm_id | class | type  | housenumber | geometry
+          | 1      | place | house | 2           | 1 1
+          | 2      | place | house | 6           | 1 1.001
+        And the place ways
+          | osm_id | class | type   | housenumber | geometry
+          | 1      | place | houses | even        | 1 1, 1 1.001
+        And the ways
+          | id | nodes
+          | 1  | 1,2
+        When importing
+        Then node 1 expands to housenumbers
+          | housenumber | centroid
+          | 2           | 1,1
+          | 4           | 1,1.0005
+        And node 2 expands to housenumbers
+          | housenumber | centroid
+          | 6           | 1,1.001
+
+    Scenario: Simple even two point interpolation with zero beginning
+        Given the place nodes
+          | osm_id | class | type  | housenumber | geometry
+          | 1      | place | house | 0           | 1 1
+          | 2      | place | house | 8           | 1 1.001
+        And the place ways
+          | osm_id | class | type   | housenumber | geometry
+          | 1      | place | houses | even        | 1 1, 1 1.001
+        And the ways
+          | id | nodes
+          | 1  | 1,2
+        When importing
+        Then node 1 expands to housenumbers
+          | housenumber | centroid
+          | 0           | 1,1
+          | 2           | 1,1.00025
+          | 4           | 1,1.0005
+          | 6           | 1,1.00075
+        And node 2 expands to housenumbers
+          | housenumber | centroid
+          | 8           | 1,1.001
+
+    Scenario: Backwards even two point interpolation
+        Given the place nodes
+          | osm_id | class | type  | housenumber | geometry
+          | 1      | place | house | 2           | 1 1
+          | 2      | place | house | 6           | 1 1.001
+        And the place ways
+          | osm_id | class | type   | housenumber | geometry
+          | 1      | place | houses | even        | 1 1.001, 1 1
+        And the ways
+          | id | nodes
+          | 1  | 2,1
+        When importing
+        Then node 1 expands to housenumbers
+          | housenumber | centroid
+          | 2           | 1,1
+        And node 2 expands to housenumbers
+          | housenumber | centroid
+          | 4           | 1,1.0005
+          | 6           | 1,1.001
+
+    Scenario: Even two point interpolation with odd beginning
+        Given the place nodes
+          | osm_id | class | type  | housenumber | geometry
+          | 1      | place | house | 11          | 1 1
+          | 2      | place | house | 16          | 1 1.001
+        And the place ways
+          | osm_id | class | type   | housenumber | geometry
+          | 1      | place | houses | even        | 1 1, 1 1.001
+        And the ways
+          | id | nodes
+          | 1  | 1,2
+        When importing
+        Then node 1 expands to housenumbers
+          | housenumber | centroid
+          | 11          | 1,1
+          | 12          | 1,1.0002
+          | 14          | 1,1.0006
+        And node 2 expands to housenumbers
+          | housenumber | centroid
+          | 16          | 1,1.001
+
+    Scenario: Even two point interpolation with odd end
+        Given the place nodes
+          | osm_id | class | type  | housenumber | geometry
+          | 1      | place | house | 10          | 1 1
+          | 2      | place | house | 15          | 1 1.001
+        And the place ways
+          | osm_id | class | type   | housenumber | geometry
+          | 1      | place | houses | even        | 1 1, 1 1.001
+        And the ways
+          | id | nodes
+          | 1  | 1,2
+        When importing
+        Then node 1 expands to housenumbers
+          | housenumber | centroid
+          | 10          | 1,1
+          | 12          | 1,1.0004
+          | 14          | 1,1.0008
+        And node 2 expands to housenumbers
+          | housenumber | centroid
+          | 15          | 1,1.001
+
+    Scenario: Reverse even two point interpolation with odd beginning
+        Given the place nodes
+          | osm_id | class | type  | housenumber | geometry
+          | 1      | place | house | 11          | 1 1
+          | 2      | place | house | 16          | 1 1.001
+        And the place ways
+          | osm_id | class | type   | housenumber | geometry
+          | 1      | place | houses | even        | 1 1.001, 1 1
+        And the ways
+          | id | nodes
+          | 1  | 2,1
+        When importing
+        Then node 1 expands to housenumbers
+          | housenumber | centroid
+          | 11          | 1,1
+        And node 2 expands to housenumbers
+          | housenumber | centroid
+          | 12          | 1,1.0002
+          | 14          | 1,1.0006
+          | 16          | 1,1.001
+
+    Scenario: Reverse even two point interpolation with odd end
+        Given the place nodes
+          | osm_id | class | type  | housenumber | geometry
+          | 1      | place | house | 10          | 1 1
+          | 2      | place | house | 15          | 1 1.001
+        And the place ways
+          | osm_id | class | type   | housenumber | geometry
+          | 1      | place | houses | even        | 1 1.001, 1 1
+        And the ways
+          | id | nodes
+          | 1  | 2,1
+        When importing
+        Then node 1 expands to housenumbers
+          | housenumber | centroid
+          | 10          | 1,1
+        And node 2 expands to housenumbers
+          | housenumber | centroid
+          | 12          | 1,1.0004
+          | 14          | 1,1.0008
+          | 15          | 1,1.001
+
+      Scenario: Simple odd two point interpolation
+        Given the place nodes
+          | osm_id | class | type  | housenumber | geometry
+          | 1      | place | house | 1           | 1 1
+          | 2      | place | house | 11          | 1 1.001
+        And the place ways
+          | osm_id | class | type   | housenumber | geometry
+          | 1      | place | houses | odd         | 1 1, 1 1.001
+        And the ways
+          | id | nodes
+          | 1  | 1,2
+        When importing
+        Then node 1 expands to housenumbers
+          | housenumber | centroid
+          | 1           | 1,1
+          | 3           | 1,1.0002
+          | 5           | 1,1.0004
+          | 7           | 1,1.0006
+          | 9           | 1,1.0008
+        And node 2 expands to housenumbers
+          | housenumber | centroid
+          | 11           | 1,1.001
+
+      Scenario: Odd two point interpolation with even beginning
+        Given the place nodes
+          | osm_id | class | type  | housenumber | geometry
+          | 1      | place | house | 2           | 1 1
+          | 2      | place | house | 7           | 1 1.001
+        And the place ways
+          | osm_id | class | type   | housenumber | geometry
+          | 1      | place | houses | odd         | 1 1, 1 1.001
+        And the ways
+          | id | nodes
+          | 1  | 1,2
+        When importing
+        Then node 1 expands to housenumbers
+          | housenumber | centroid
+          | 2           | 1,1
+          | 3           | 1,1.0002
+          | 5           | 1,1.0006
+        And node 2 expands to housenumbers
+          | housenumber | centroid
+          | 7           | 1,1.001
+
+     Scenario: Simple all two point interpolation
+        Given the place nodes
+          | osm_id | class | type  | housenumber | geometry
+          | 1      | place | house | 1           | 1 1
+          | 2      | place | house | 3           | 1 1.001
+        And the place ways
+          | osm_id | class | type   | housenumber | geometry
+          | 1      | place | houses | all         | 1 1, 1 1.001
+        And the ways
+          | id | nodes
+          | 1  | 1,2
+        When importing
+        Then node 1 expands to housenumbers
+          | housenumber | centroid
+          | 1           | 1,1
+          | 2           | 1,1.0005
+        And node 2 expands to housenumbers
+          | housenumber | centroid
+          | 3           | 1,1.001
+
+    Scenario: Simple numbered two point interpolation
+        Given the place nodes
+          | osm_id | class | type  | housenumber | geometry
+          | 1      | place | house | 3           | 1 1
+          | 2      | place | house | 9           | 1 1.001
+        And the place ways
+          | osm_id | class | type   | housenumber | geometry
+          | 1      | place | houses | 3           | 1 1, 1 1.001
+        And the ways
+          | id | nodes
+          | 1  | 1,2
+        When importing
+        Then node 1 expands to housenumbers
+          | housenumber | centroid
+          | 3           | 1,1
+          | 6           | 1,1.0005
+        And node 2 expands to housenumbers
+          | housenumber | centroid
+          | 9           | 1,1.001
+
+    Scenario: Even two point interpolation with intermediate empty node
+        Given the place nodes
+          | osm_id | class | type  | housenumber | geometry
+          | 1      | place | house | 2           | 1 1
+          | 2      | place | house | 10          | 1.001 1.001
+        And the place ways
+          | osm_id | class | type   | housenumber | geometry
+          | 1      | place | houses | even        | 1 1, 1 1.001, 1.001 1.001
+        And the ways
+          | id | nodes
+          | 1  | 1,3,2
+        When importing
+        Then node 1 expands to housenumbers
+          | housenumber | centroid
+          | 2           | 1,1
+          | 4           | 1,1.0005
+          | 6           | 1,1.001
+          | 8           | 1.0005,1.001
+        And node 2 expands to housenumbers
+          | housenumber | centroid
+          | 10          | 1.001,1.001
+
+
+    Scenario: Even two point interpolation with intermediate duplicated empty node
+        Given the place nodes
+          | osm_id | class | type  | housenumber | geometry
+          | 1      | place | house | 2           | 1 1
+          | 2      | place | house | 10          | 1.001 1.001
+        And the place ways
+          | osm_id | class | type   | housenumber | geometry
+          | 1      | place | houses | even        | 1 1, 1 1.001, 1.001 1.001
+        And the ways
+          | id | nodes
+          | 1  | 1,3,3,2
+        When importing
+        Then node 1 expands to housenumbers
+          | housenumber | centroid
+          | 2           | 1,1
+          | 4           | 1,1.0005
+          | 6           | 1,1.001
+          | 8           | 1.0005,1.001
+        And node 2 expands to housenumbers
+          | housenumber | centroid
+          | 10          | 1.001,1.001
+
+    Scenario: Simple even three point interpolation
+        Given the place nodes
+          | osm_id | class | type  | housenumber | geometry
+          | 1      | place | house | 2           | 1 1
+          | 2      | place | house | 8           | 1.001 1.001
+          | 3      | place | house | 4           | 1 1.001
+        And the place ways
+          | osm_id | class | type   | housenumber | geometry
+          | 1      | place | houses | even        | 1 1, 1 1.001, 1.001 1.001
+        And the ways
+          | id | nodes
+          | 1  | 1,3,2
+        When importing
+        Then node 1 expands to housenumbers
+          | housenumber | centroid
+          | 2           | 1,1
+        Then node 3 expands to housenumbers
+          | housenumber | centroid
+          | 4           | 1,1.001
+          | 6           | 1.0005,1.001
+        And node 2 expands to housenumbers
+          | housenumber | centroid
+          | 8           | 1.001,1.001
+
+    Scenario: Even three point interpolation with odd center point
+        Given the place nodes
+          | osm_id | class | type  | housenumber | geometry
+          | 1      | place | house | 2           | 1 1
+          | 2      | place | house | 8           | 1.001 1.001
+          | 3      | place | house | 7           | 1 1.001
+        And the place ways
+          | osm_id | class | type   | housenumber | geometry
+          | 1      | place | houses | even        | 1 1, 1 1.001, 1.001 1.001
+        And the ways
+          | id | nodes
+          | 1  | 1,3,2
+        When importing
+        Then node 1 expands to housenumbers
+          | housenumber | centroid
+          | 2           | 1,1
+          | 4           | 1,1.0004
+          | 6           | 1,1.0008
+        Then node 3 expands to housenumbers
+          | housenumber | centroid
+          | 7           | 1,1.001
+        And node 2 expands to housenumbers
+          | housenumber | centroid
+          | 8           | 1.001,1.001
+
+

diff --git a/tests/steps/db_results.py b/tests/steps/db_results.py
index 9da1ad6b703c905c2775e9fe8f35520454ffd79f..2b44215e7e0aa328f421421559c2b60b186dbf44 100644 (file)
--- a/tests/steps/db_results.py
+++ b/tests/steps/db_results.py
@@ -110,6 +110,27 @@ def check_search_name_content(step):
                 else:
                     raise Exception("Cannot handle field %s in search_name table" % (k, ))
 
                 else:
                     raise Exception("Cannot handle field %s in search_name table" % (k, ))
 

+@step(u'node (\d+) expands to housenumbers')
+def check_interpolated_housenumbers(step, nodeid):
+    """Check that the exact set of housenumbers has been entered in
+       placex for the given source node. Expected are tow columns:
+       housenumber and centroid
+    """
+    numbers = {}
+    for line in step.hashes:
+        assert line["housenumber"] not in numbers
+        numbers[line["housenumber"]] = line["centroid"]
+    cur = world.conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
+    cur.execute("""SELECT DISTINCT housenumber,
+                          ST_X(centroid) as clat, ST_Y(centroid) as clon
+                   FROM placex WHERE osm_type = 'N' and osm_id = %s""",
+                   (int(nodeid),))
+    assert_equals(len(numbers), cur.rowcount)
+    for r in cur:
+        assert_in(r["housenumber"], numbers)
+        world.match_geometry((r['clat'], r['clon']), numbers[r["housenumber"]])
+        del numbers[r["housenumber"]]
+

 
 @step(u'table search_name has no entry for (.*)')
 def check_placex_missing(step, osmid):
 
 @step(u'table search_name has no entry for (.*)')
 def check_placex_missing(step, osmid):

diff --git a/utils/setup.php b/utils/setup.php
index 81d584f6280cd960df5990751939ce6818d2bdea..c07bccefc949664b0cff92a327bbe88b4f05eac6 100755 (executable)
--- a/utils/setup.php
+++ b/utils/setup.php
@@ -89,6 +89,8 @@
        $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
        if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 
        $aDSNInfo = DB::parseDSN(CONST_Database_DSN);
        if (!isset($aDSNInfo['port']) || !$aDSNInfo['port']) $aDSNInfo['port'] = 5432;
 

+       $fPostgisVersion = (float) CONST_Postgis_Version;
+

        if ($aCMDResult['create-db'] || $aCMDResult['all'])
        {
                echo "Create DB\n";
        if ($aCMDResult['create-db'] || $aCMDResult['all'])
        {
                echo "Create DB\n";


@@ -126,7 +128,6 @@
                        pgsqlRunScript('CREATE EXTENSION hstore');
                }
 
                        pgsqlRunScript('CREATE EXTENSION hstore');
                }
 

-               $fPostgisVersion = (float) CONST_Postgis_Version;

                if ($fPostgisVersion < 2.0) {
                        pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/postgis.sql');
                        pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/spatial_ref_sys.sql');
                if ($fPostgisVersion < 2.0) {
                        pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/postgis.sql');
                        pgsqlRunScriptFile(CONST_Path_Postgresql_Postgis.'/spatial_ref_sys.sql');


@@ -215,6 +216,14 @@
                if ($aCMDResult['enable-debug-statements']) $sTemplate = str_replace('--DEBUG:', '', $sTemplate);
                if (CONST_Limit_Reindexing) $sTemplate = str_replace('--LIMIT INDEXING:', '', $sTemplate);
                pgsqlRunScript($sTemplate);
                if ($aCMDResult['enable-debug-statements']) $sTemplate = str_replace('--DEBUG:', '', $sTemplate);
                if (CONST_Limit_Reindexing) $sTemplate = str_replace('--LIMIT INDEXING:', '', $sTemplate);
                pgsqlRunScript($sTemplate);

+               if ($fPostgisVersion < 2.0) {
+                       echo "Helper functions for postgis < 2.0\n";
+                       $sTemplate = file_get_contents(CONST_BasePath.'/sql/postgis_15_aux.sql');
+               } else {
+                       echo "Helper functions for postgis >= 2.0\n";
+                       $sTemplate = file_get_contents(CONST_BasePath.'/sql/postgis_20_aux.sql');
+               }
+               pgsqlRunScript($sTemplate);

        }
 
        if ($aCMDResult['create-minimal-tables'])
        }
 
        if ($aCMDResult['create-minimal-tables'])