restrict interpolation housenumbers to 0-999999

[nominatim.git] / lib-sql / functions / interpolation.sql

diff --git a/lib-sql/functions/interpolation.sql b/lib-sql/functions/interpolation.sql
index 96a105aecfca42c0fc6acc23f3558ea6567a72b7..b0797df2bddcd574ea05df20f1e83c57d68a7e87 100644 (file)
--- a/lib-sql/functions/interpolation.sql
+++ b/lib-sql/functions/interpolation.sql
@@ -52,7 +52,9 @@ BEGIN
 
   IF parent_place_id is null THEN
     FOR location IN SELECT place_id FROM placex
 
   IF parent_place_id is null THEN
     FOR location IN SELECT place_id FROM placex

-        WHERE ST_DWithin(geom, placex.geometry, 0.001) and placex.rank_search = 26
+        WHERE ST_DWithin(geom, placex.geometry, 0.001)
+              and placex.rank_search = 26
+              and placex.osm_type = 'W' -- needed for index selection

         ORDER BY CASE WHEN ST_GeometryType(geom) = 'ST_Line' THEN
                   (ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0))+
                   ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0.5))+
         ORDER BY CASE WHEN ST_GeometryType(geom) = 'ST_Line' THEN
                   (ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0))+
                   ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0.5))+


@@ -65,10 +67,6 @@ BEGIN
     END LOOP;
   END IF;
 
     END LOOP;
   END IF;
 

-  IF parent_place_id is null THEN
-    RETURN 0;
-  END IF;
-

   RETURN parent_place_id;
 END;
 $$
   RETURN parent_place_id;
 END;
 $$


@@ -82,27 +80,35 @@ CREATE OR REPLACE FUNCTION reinsert_interpolation(way_id BIGINT, addr HSTORE,
 DECLARE
   existing BIGINT[];
 BEGIN
 DECLARE
   existing BIGINT[];
 BEGIN

-  -- Get the existing entry from the interpolation table.
-  SELECT array_agg(place_id) INTO existing
-    FROM location_property_osmline WHERE osm_id = way_id;
-
-  IF existing IS NULL or array_length(existing, 1) = 0 THEN
-    INSERT INTO location_property_osmline (osm_id, address, linegeo)
-      VALUES (way_id, addr, geom);
+  IF addr is NULL OR NOT addr ? 'interpolation'
+         OR NOT (addr->'interpolation' in ('odd', 'even', 'all')
+                 or addr->'interpolation' similar to '[1-9]')
+  THEN
+    -- the new interpolation is illegal, simply remove existing entries
+    DELETE FROM location_property_osmline WHERE osm_id = way_id;

   ELSE
   ELSE

-    -- Update the interpolation table:
-    --   The first entry gets the original data, all other entries
-    --   are removed and will be recreated on indexing.
-    --   (An interpolation can be split up, if it has more than 2 address nodes)
-    UPDATE location_property_osmline
-      SET address = addr,
-          linegeo = geom,
-          startnumber = null,
-          indexed_status = 1
-      WHERE place_id = existing[1];
-    IF array_length(existing, 1) > 1 THEN
-      DELETE FROM location_property_osmline
-        WHERE place_id = any(existing[2:]);
+    -- Get the existing entry from the interpolation table.
+    SELECT array_agg(place_id) INTO existing
+      FROM location_property_osmline WHERE osm_id = way_id;
+
+    IF existing IS NULL or array_length(existing, 1) = 0 THEN
+      INSERT INTO location_property_osmline (osm_id, address, linegeo)
+        VALUES (way_id, addr, geom);
+    ELSE
+      -- Update the interpolation table:
+      --   The first entry gets the original data, all other entries
+      --   are removed and will be recreated on indexing.
+      --   (An interpolation can be split up, if it has more than 2 address nodes)
+      UPDATE location_property_osmline
+        SET address = addr,
+            linegeo = geom,
+            startnumber = null,
+            indexed_status = 1
+        WHERE place_id = existing[1];
+      IF array_length(existing, 1) > 1 THEN
+        DELETE FROM location_property_osmline
+          WHERE place_id = any(existing[2:]);
+      END IF;

     END IF;
   END IF;
 
     END IF;
   END IF;
 


@@ -154,7 +160,7 @@ DECLARE
   newend INTEGER;
   moddiff SMALLINT;
   linegeo GEOMETRY;
   newend INTEGER;
   moddiff SMALLINT;
   linegeo GEOMETRY;

-  splitline GEOMETRY;
+  splitpoint FLOAT;

   sectiongeo GEOMETRY;
   postcode TEXT;
   stepmod SMALLINT;
   sectiongeo GEOMETRY;
   postcode TEXT;
   stepmod SMALLINT;


@@ -173,6 +179,13 @@ BEGIN
                                                  ST_PointOnSurface(NEW.linegeo),
                                                  NEW.linegeo);
 
                                                  ST_PointOnSurface(NEW.linegeo),
                                                  NEW.linegeo);
 

+  -- Cannot find a parent street. We will not be able to display a reliable
+  -- address, so drop entire interpolation.
+  IF NEW.parent_place_id is NULL THEN
+    DELETE FROM location_property_osmline where place_id = OLD.place_id;
+    RETURN NULL;
+  END IF;
+

   NEW.token_info := token_strip_info(NEW.token_info);
   IF NEW.address ? '_inherited' THEN
     NEW.address := hstore('interpolation', NEW.address->'interpolation');
   NEW.token_info := token_strip_info(NEW.token_info);
   IF NEW.address ? '_inherited' THEN
     NEW.address := hstore('interpolation', NEW.address->'interpolation');


@@ -209,19 +222,32 @@ BEGIN
           -- formatted postcode and therefore 'postcode' contains a derived
           -- variant.
           CASE WHEN address ? 'postcode' THEN placex.postcode ELSE NULL::text END as postcode,
           -- formatted postcode and therefore 'postcode' contains a derived
           -- variant.
           CASE WHEN address ? 'postcode' THEN placex.postcode ELSE NULL::text END as postcode,

-          substring(address->'housenumber','[0-9]+')::integer as hnr
+          (address->'housenumber')::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'
         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'

+              and address->'housenumber' ~ '^[0-9]{1,6}$'
+              and ST_Distance(NEW.linegeo, geometry) < 0.0005

         ORDER BY nodeidpos
     LOOP
       {% if debug %}RAISE WARNING 'processing point % (%)', nextnode.hnr, ST_AsText(nextnode.geometry);{% endif %}
       IF linegeo is null THEN
         linegeo := NEW.linegeo;
       ELSE
         ORDER BY nodeidpos
     LOOP
       {% if debug %}RAISE WARNING 'processing point % (%)', nextnode.hnr, ST_AsText(nextnode.geometry);{% endif %}
       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);
+        splitpoint := ST_LineLocatePoint(linegeo, nextnode.geometry);
+        IF splitpoint = 0 THEN
+          -- Corner case where the splitpoint falls on the first point
+          -- and thus would not return a geometry. Skip that section.
+          sectiongeo := NULL;
+        ELSEIF splitpoint = 1 THEN
+          -- Point is at the end of the line.
+          sectiongeo := linegeo;
+          linegeo := NULL;
+        ELSE
+          -- Split the line.
+          sectiongeo := ST_LineSubstring(linegeo, 0, splitpoint);
+          linegeo := ST_LineSubstring(linegeo, splitpoint, 1);
+        END IF;

       END IF;
 
       IF prevnode.hnr is not null
       END IF;
 
       IF prevnode.hnr is not null


@@ -229,6 +255,9 @@ BEGIN
          -- regularly mapped housenumbers.
          -- (Conveniently also fails if one of the house numbers is not a number.)
          and abs(prevnode.hnr - nextnode.hnr) > NEW.step
          -- regularly mapped housenumbers.
          -- (Conveniently also fails if one of the house numbers is not a number.)
          and abs(prevnode.hnr - nextnode.hnr) > NEW.step

+         -- If the interpolation geometry is broken or two nodes are at the
+         -- same place, then splitting might produce a point. Ignore that.
+         and ST_GeometryType(sectiongeo) = 'ST_LineString'

       THEN
         IF prevnode.hnr < nextnode.hnr THEN
           startnumber := prevnode.hnr;
       THEN
         IF prevnode.hnr < nextnode.hnr THEN
           startnumber := prevnode.hnr;


@@ -290,12 +319,12 @@ BEGIN
                   NEW.address, postcode,
                   NEW.country_code, NEW.geometry_sector, 0);
         END IF;
                   NEW.address, postcode,
                   NEW.country_code, NEW.geometry_sector, 0);
         END IF;

+      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;
+      -- early break if we are out of line string,
+      -- might happen when a line string loops back on itself
+      IF linegeo is null or ST_GeometryType(linegeo) != 'ST_LineString' THEN
+          RETURN NEW;

       END IF;
 
       prevnode := nextnode;
       END IF;
 
       prevnode := nextnode;