Merge pull request #2886 from lonvia/closest-street-in-associated

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

diff --git a/lib-sql/functions/interpolation.sql b/lib-sql/functions/interpolation.sql
index c018155680039df6b3ae7e44e7e822fb9cc981d0..fb822033c0220b400524a341515670d58b137be4 100644 (file)
--- a/lib-sql/functions/interpolation.sql
+++ b/lib-sql/functions/interpolation.sql
@@ -15,7 +15,7 @@ DECLARE
   location RECORD;
   waynodes BIGINT[];
 BEGIN
   location RECORD;
   waynodes BIGINT[];
 BEGIN

-  IF akeys(in_address) != ARRAY['interpolation'] THEN
+  IF in_address ? 'street' or in_address ? 'place' THEN

     RETURN in_address;
   END IF;
 
     RETURN in_address;
   END IF;
 


@@ -52,10 +52,16 @@ 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
-        ORDER BY (ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0))+
+        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))+
                   ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,0.5))+

-                  ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,1))) ASC limit 1
+                  ST_distance(placex.geometry, ST_LineInterpolatePoint(geom,1)))
+                 ELSE ST_distance(placex.geometry, geom) END
+              ASC
+        LIMIT 1

     LOOP
       parent_place_id := location.place_id;
     END LOOP;
     LOOP
       parent_place_id := location.place_id;
     END LOOP;


@@ -152,7 +158,6 @@ DECLARE
   linegeo GEOMETRY;
   splitline GEOMETRY;
   sectiongeo GEOMETRY;
   linegeo GEOMETRY;
   splitline GEOMETRY;
   sectiongeo GEOMETRY;

-  interpol_postcode TEXT;

   postcode TEXT;
   stepmod SMALLINT;
 BEGIN
   postcode TEXT;
   stepmod SMALLINT;
 BEGIN


@@ -170,8 +175,6 @@ BEGIN
                                                  ST_PointOnSurface(NEW.linegeo),
                                                  NEW.linegeo);
 
                                                  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
     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');


@@ -203,13 +206,18 @@ BEGIN
     FOR nextnode IN
       SELECT DISTINCT ON (nodeidpos)
           osm_id, address, geometry,
     FOR nextnode IN
       SELECT DISTINCT ON (nodeidpos)
           osm_id, address, geometry,

+          -- Take the postcode from the node only if it has a housenumber itself.
+          -- Note that there is a corner-case where the node has a wrongly
+          -- 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
         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
           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 debug %}RAISE WARNING 'processing point % (%)', nextnode.hnr, ST_AsText(nextnode.geometry);{% endif %}

       IF linegeo is null THEN
         linegeo := NEW.linegeo;
       ELSE
       IF linegeo is null THEN
         linegeo := NEW.linegeo;
       ELSE


@@ -256,13 +264,10 @@ BEGIN
         endnumber := newend;
 
         -- determine postcode
         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;
+        postcode := coalesce(prevnode.postcode, nextnode.postcode, postcode);
+        IF postcode is NULL and NEW.parent_place_id > 0 THEN
+            SELECT 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 postcode is NULL THEN
             postcode := get_nearest_postcode(NEW.country_code, nextnode.geometry);