inherit tags from interpolation not parent

Nodes on an interpolation now only get the address tags of
interpolations and then compute their own parent from that. They no
longer inherit the parent directly.

diff --git a/lib-sql/functions/interpolation.sql b/lib-sql/functions/interpolation.sql
index 098db26c2a1efc71b6245fd183dd9f484a767a2d..c018155680039df6b3ae7e44e7e822fb9cc981d0 100644 (file)
--- a/lib-sql/functions/interpolation.sql
+++ b/lib-sql/functions/interpolation.sql
@@ -7,17 +7,6 @@
 
 -- Functions for address interpolation objects in location_property_osmline.
 
 
 -- Functions for address interpolation objects in location_property_osmline.
 

--- 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(ST_Snap(line, point, 0.0005), point);
-END;
-$$
-LANGUAGE plpgsql IMMUTABLE;
-

 
 CREATE OR REPLACE FUNCTION get_interpolation_address(in_address HSTORE, wayid BIGINT)
 RETURNS HSTORE
 
 CREATE OR REPLACE FUNCTION get_interpolation_address(in_address HSTORE, wayid BIGINT)
 RETURNS HSTORE


@@ -128,8 +117,10 @@ BEGIN
 
   IF NEW.indexed_status IS NULL THEN
       IF NEW.address is NULL OR NOT NEW.address ? 'interpolation'
 
   IF NEW.indexed_status IS NULL THEN
       IF NEW.address is NULL OR NOT NEW.address ? 'interpolation'

-         OR NEW.address->'interpolation' NOT IN ('odd', 'even', 'all') THEN
-          -- other interpolation types than odd/even/all (e.g. numeric ones) are not supported
+         OR NOT (NEW.address->'interpolation' in ('odd', 'even', 'all')
+                 or NEW.address->'interpolation' similar to '[1-9]')
+      THEN
+          -- alphabetic interpolation is not supported

           RETURN NULL;
       END IF;
 
           RETURN NULL;
       END IF;
 


@@ -150,18 +141,20 @@ CREATE OR REPLACE FUNCTION osmline_update()
   RETURNS TRIGGER
   AS $$
 DECLARE
   RETURNS TRIGGER
   AS $$
 DECLARE

-  place_centroid GEOMETRY;

   waynodes BIGINT[];
   prevnode RECORD;
   nextnode RECORD;
   startnumber INTEGER;
   endnumber INTEGER;
   waynodes BIGINT[];
   prevnode RECORD;
   nextnode RECORD;
   startnumber INTEGER;
   endnumber INTEGER;

-  housenum INTEGER;
+  newstart INTEGER;
+  newend INTEGER;
+  moddiff SMALLINT;

   linegeo GEOMETRY;
   splitline GEOMETRY;
   sectiongeo GEOMETRY;
   interpol_postcode TEXT;
   postcode TEXT;
   linegeo GEOMETRY;
   splitline GEOMETRY;
   sectiongeo GEOMETRY;
   interpol_postcode TEXT;
   postcode TEXT;

+  stepmod SMALLINT;

 BEGIN
   -- deferred delete
   IF OLD.indexed_status = 100 THEN
 BEGIN
   -- deferred delete
   IF OLD.indexed_status = 100 THEN


@@ -173,107 +166,139 @@ BEGIN
     RETURN NEW;
   END IF;
 
     RETURN NEW;
   END IF;
 

-  NEW.interpolationtype = NEW.address->'interpolation';
-
-  place_centroid := ST_PointOnSurface(NEW.linegeo);
-  NEW.parent_place_id = get_interpolation_parent(NEW.token_info, NEW.partition,
-                                                 place_centroid, NEW.linegeo);
+  NEW.parent_place_id := get_interpolation_parent(NEW.token_info, NEW.partition,
+                                                 ST_PointOnSurface(NEW.linegeo),
+                                                 NEW.linegeo);

 
   interpol_postcode := token_normalized_postcode(NEW.address->'postcode');
 
   NEW.token_info := token_strip_info(NEW.token_info);
   IF NEW.address ? '_inherited' THEN
 
   interpol_postcode := token_normalized_postcode(NEW.address->'postcode');
 
   NEW.token_info := token_strip_info(NEW.token_info);
   IF NEW.address ? '_inherited' THEN

-    NEW.address := hstore('interpolation', NEW.interpolationtype);
+    NEW.address := hstore('interpolation', NEW.address->'interpolation');

   END IF;
 
   END IF;
 

-  -- if the line was newly inserted, split the line as necessary
+  -- If the line was newly inserted, split the line as necessary.

   IF OLD.indexed_status = 1 THEN
   IF OLD.indexed_status = 1 THEN

-      select nodes from planet_osm_ways where id = NEW.osm_id INTO waynodes;
+    IF NEW.address->'interpolation' in ('odd', 'even') THEN
+      NEW.step := 2;
+      stepmod := CASE WHEN NEW.address->'interpolation' = 'odd' THEN 1 ELSE 0 END;
+    ELSE
+      NEW.step := CASE WHEN NEW.address->'interpolation' = 'all'
+                       THEN 1
+                       ELSE (NEW.address->'interpolation')::SMALLINT END;
+      stepmod := NULL;
+    END IF;
+
+    SELECT nodes INTO waynodes
+      FROM planet_osm_ways WHERE id = NEW.osm_id;

 
 

-      IF array_upper(waynodes, 1) IS NULL THEN
-        RETURN NEW;
+    IF array_upper(waynodes, 1) IS NULL THEN
+      RETURN NEW;
+    END IF;
+
+    linegeo := null;
+    SELECT null::integer as hnr INTO prevnode;
+
+    -- Go through all nodes on the interpolation line that have a housenumber.
+    FOR nextnode IN
+      SELECT DISTINCT ON (nodeidpos)
+          osm_id, address, geometry,
+          substring(address->'housenumber','[0-9]+')::integer as hnr
+        FROM placex, generate_series(1, array_upper(waynodes, 1)) nodeidpos
+        WHERE osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT
+              and address is not NULL and address ? 'housenumber'
+        ORDER BY nodeidpos
+    LOOP
+      RAISE WARNING 'processing point % (%)', nextnode.hnr, ST_AsText(nextnode.geometry);
+      IF linegeo is null THEN
+        linegeo := NEW.linegeo;
+      ELSE
+        splitline := ST_Split(ST_Snap(linegeo, nextnode.geometry, 0.0005), nextnode.geometry);
+        sectiongeo := ST_GeometryN(splitline, 1);
+        linegeo := ST_GeometryN(splitline, 2);

       END IF;
 
       END IF;
 

-      linegeo := NEW.linegeo;
-      startnumber := NULL;
-
-      FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP
-
-        select osm_id, address, geometry
-          from place where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT
-                           and address is not NULL and address ? 'housenumber' limit 1 INTO nextnode;
-        --RAISE NOTICE 'Nextnode.place_id: %s', nextnode.place_id;
-        IF nextnode.osm_id IS NOT NULL THEN
-          --RAISE NOTICE 'place_id is not null';
-          IF nodeidpos > 1 and 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);
-            linegeo := ST_GeometryN(splitline, 2);
+      IF prevnode.hnr is not null
+         -- Check if there are housenumbers to interpolate between the
+         -- regularly mapped housenumbers.
+         -- (Conveniently also fails if one of the house numbers is not a number.)
+         and abs(prevnode.hnr - nextnode.hnr) > NEW.step
+      THEN
+        IF prevnode.hnr < nextnode.hnr THEN
+          startnumber := prevnode.hnr;
+          endnumber := nextnode.hnr;
+        ELSE
+          startnumber := nextnode.hnr;
+          endnumber := prevnode.hnr;
+          sectiongeo := ST_Reverse(sectiongeo);
+        END IF;
+
+        -- Adjust the interpolation, so that only inner housenumbers
+        -- are taken into account.
+        IF stepmod is null THEN
+          newstart := startnumber + NEW.step;
+        ELSE
+          newstart := startnumber + 1;
+          moddiff := newstart % NEW.step - stepmod;
+          IF moddiff < 0 THEN
+            newstart := newstart + (NEW.step + moddiff);

           ELSE
           ELSE

-            sectiongeo = linegeo;
-          END IF;
-          endnumber := substring(nextnode.address->'housenumber','[0-9]+')::integer;
-
-          IF startnumber IS NOT NULL AND endnumber IS NOT NULL
-             AND startnumber != endnumber
-             AND ST_GeometryType(sectiongeo) = 'ST_LineString' THEN
-
-            IF (startnumber > endnumber) THEN
-              housenum := endnumber;
-              endnumber := startnumber;
-              startnumber := housenum;
-              sectiongeo := ST_Reverse(sectiongeo);
-            END IF;
-
-            -- determine postcode
-            postcode := coalesce(interpol_postcode,
-                                 token_normalized_postcode(prevnode.address->'postcode'),
-                                 token_normalized_postcode(nextnode.address->'postcode'),
-                                 postcode);
-
-            IF postcode is NULL THEN
-                SELECT token_normalized_postcode(placex.postcode)
-                  FROM placex WHERE place_id = NEW.parent_place_id INTO postcode;
-            END IF;
-            IF postcode is NULL THEN
-                postcode := get_nearest_postcode(NEW.country_code, nextnode.geometry);
-            END IF;
-
-            IF NEW.startnumber IS NULL THEN
-                NEW.startnumber := startnumber;
-                NEW.endnumber := endnumber;
-                NEW.linegeo := sectiongeo;
-                NEW.postcode := postcode;
-             ELSE
-              insert into location_property_osmline
-                     (linegeo, partition, osm_id, parent_place_id,
-                      startnumber, endnumber, interpolationtype,
-                      address, postcode, country_code,
-                      geometry_sector, indexed_status)
-              values (sectiongeo, NEW.partition, NEW.osm_id, NEW.parent_place_id,
-                      startnumber, endnumber, NEW.interpolationtype,
-                      NEW.address, postcode,
-                      NEW.country_code, NEW.geometry_sector, 0);
-             END IF;
+            newstart := newstart + moddiff;

           END IF;
           END IF;

+        END IF;
+        newend := newstart + ((endnumber - 1 - newstart) / NEW.step) * NEW.step;
+
+        -- If newstart and newend are the same, then this returns a point.
+        sectiongeo := ST_LineSubstring(sectiongeo,
+                              (newstart - startnumber)::float / (endnumber - startnumber)::float,
+                              (newend - startnumber)::float / (endnumber - startnumber)::float);
+        startnumber := newstart;
+        endnumber := newend;
+
+        -- determine postcode
+        postcode := coalesce(interpol_postcode,
+                             token_normalized_postcode(prevnode.address->'postcode'),
+                             token_normalized_postcode(nextnode.address->'postcode'),
+                             postcode);
+        IF postcode is NULL THEN
+            SELECT token_normalized_postcode(placex.postcode)
+              FROM placex WHERE place_id = NEW.parent_place_id INTO postcode;
+        END IF;
+        IF postcode is NULL THEN
+            postcode := get_nearest_postcode(NEW.country_code, nextnode.geometry);
+        END IF;

 
 

-          -- early break if we are out of line string,
-          -- might happen when a line string loops back on itself
-          IF ST_GeometryType(linegeo) != 'ST_LineString' THEN
-              RETURN NEW;
-          END IF;
+        -- Add the interpolation. If this is the first segment, just modify
+        -- the interpolation to be inserted, otherwise add an additional one
+        -- (marking it indexed already).
+        IF NEW.startnumber IS NULL THEN
+            NEW.startnumber := startnumber;
+            NEW.endnumber := endnumber;
+            NEW.linegeo := sectiongeo;
+            NEW.postcode := postcode;
+        ELSE
+          INSERT INTO location_property_osmline
+                 (linegeo, partition, osm_id, parent_place_id,
+                  startnumber, endnumber, step,
+                  address, postcode, country_code,
+                  geometry_sector, indexed_status)
+          VALUES (sectiongeo, NEW.partition, NEW.osm_id, NEW.parent_place_id,
+                  startnumber, endnumber, NEW.step,
+                  NEW.address, postcode,
+                  NEW.country_code, NEW.geometry_sector, 0);
+        END IF;

 
 

-          startnumber := substring(nextnode.address->'housenumber','[0-9]+')::integer;
-          prevnode := nextnode;
+        -- early break if we are out of line string,
+        -- might happen when a line string loops back on itself
+        IF ST_GeometryType(linegeo) != 'ST_LineString' THEN
+            RETURN NEW;

         END IF;
         END IF;

-      END LOOP;
+      END IF;
+
+      prevnode := nextnode;
+    END LOOP;

   END IF;
 
   END IF;
 

-  -- marking descendants for reparenting is not needed, because there are
-  -- actually no descendants for interpolation lines

   RETURN NEW;
 END;
 $$
   RETURN NEW;
 END;
 $$

diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql
index 5db76b7bce1c4eef89ba9d75857eada35fd16ba5..6ab73a3b4143dea539d7304ce8cc464d2109e7a5 100644 (file)
--- a/lib-sql/functions/placex_triggers.sql
+++ b/lib-sql/functions/placex_triggers.sql
@@ -29,9 +29,9 @@ DECLARE
 BEGIN
   -- For POI nodes, check if the address should be derived from a surrounding
   -- building.
 BEGIN
   -- For POI nodes, check if the address should be derived from a surrounding
   -- building.

-  IF p.rank_search < 30 OR p.osm_type != 'N' OR p.address is not null THEN
+  IF p.rank_search < 30 OR p.osm_type != 'N' THEN

     result.address := p.address;
     result.address := p.address;

-  ELSE
+  ELSEIF p.address is null THEN

     -- The additional && condition works around the misguided query
     -- planner of postgis 3.0.
     SELECT placex.address || hstore('_inherited', '') INTO result.address
     -- The additional && condition works around the misguided query
     -- planner of postgis 3.0.
     SELECT placex.address || hstore('_inherited', '') INTO result.address


@@ -42,6 +42,20 @@ BEGIN
            and (placex.address ? 'housenumber' or placex.address ? 'street' or placex.address ? 'place')
            and rank_search = 30 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
      LIMIT 1;
            and (placex.address ? 'housenumber' or placex.address ? 'street' or placex.address ? 'place')
            and rank_search = 30 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
      LIMIT 1;

+  ELSE
+    result.address := p.address;
+    -- See if we can inherit addtional address tags from an interpolation.
+    -- These will become permanent.
+    FOR location IN
+      SELECT (address - 'interpolation'::text - 'housenumber'::text) as address
+        FROM place, planet_osm_ways w
+        WHERE place.osm_type = 'W' and place.address ? 'interpolation'
+              and place.geometry && p.geometry
+              and place.osm_id = w.id
+              and p.osm_id = any(w.nodes)
+    LOOP
+      result.address := location.address || result.address;
+    END LOOP;

   END IF;
 
   result.address := result.address - '_unlisted_place'::TEXT;
   END IF;
 
   result.address := result.address - '_unlisted_place'::TEXT;


@@ -131,17 +145,6 @@ BEGIN
   END IF;
 
   IF parent_place_id is null and poi_osm_type = 'N' THEN
   END IF;
 
   IF parent_place_id is null and poi_osm_type = 'N' THEN

-    -- Is this node part of an interpolation?
-    FOR location IN
-      SELECT q.parent_place_id
-        FROM location_property_osmline q, planet_osm_ways x
-       WHERE q.linegeo && bbox and startnumber is not null
-             and x.id = q.osm_id and poi_osm_id = any(x.nodes)
-    LOOP
-      {% if debug %}RAISE WARNING 'Get parent from interpolation: %', location.parent_place_id;{% endif %}
-      RETURN location.parent_place_id;
-    END LOOP;
-

     FOR location IN
       SELECT p.place_id, p.osm_id, p.rank_search, p.address,
              coalesce(p.centroid, ST_Centroid(p.geometry)) as centroid
     FOR location IN
       SELECT p.place_id, p.osm_id, p.rank_search, p.address,
              coalesce(p.centroid, ST_Centroid(p.geometry)) as centroid

diff --git a/lib-sql/tables.sql b/lib-sql/tables.sql
index 107136617c3a90439aa78127c84137c650f4220c..0c0f78fcba77e518314b1ce8b28a5c54bff9892d 100644 (file)
--- a/lib-sql/tables.sql
+++ b/lib-sql/tables.sql
@@ -95,10 +95,10 @@ CREATE TABLE location_property_osmline (
     indexed_date TIMESTAMP,
     startnumber INTEGER,
     endnumber INTEGER,
     indexed_date TIMESTAMP,
     startnumber INTEGER,
     endnumber INTEGER,

+    step SMALLINT,

     partition SMALLINT,
     indexed_status SMALLINT,
     linegeo GEOMETRY,
     partition SMALLINT,
     indexed_status SMALLINT,
     linegeo GEOMETRY,

-    interpolationtype TEXT,

     address HSTORE,
     token_info JSONB, -- custom column for tokenizer use only
     postcode TEXT,
     address HSTORE,
     token_info JSONB, -- custom column for tokenizer use only
     postcode TEXT,