X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/a7d31469675e9bd26cf19fa319da0133de99a822..9c1abc4b75b941f434d6a120bd1c8fc4f1031c30:/sql/functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index 2c415620..821d44a6 100644
--- 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
-  
+
   newpoints INTEGER;
   waynodes BIGINT[];
   nodeid BIGINT;
@@ -804,117 +804,103 @@ DECLARE
   originalnumberrange INTEGER;
   housenum INTEGER;
   linegeo GEOMETRY;
+  splitline GEOMETRY;
+  sectiongeo GEOMETRY;
   search_place_id BIGINT;
   defpostalcode TEXT;
 
-  havefirstpoint BOOLEAN;
-  linestr TEXT;
 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;
+        ELSE
+          sectiongeo = linegeo;
         END IF;
+        endnumber := substring(nextnode.housenumber,'[0-9]+')::integer;
+
+        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;
+          orginalstartnumber := startnumber;
+          originalnumberrange := endnumber - startnumber;
 
-        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;
+          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;
---RAISE WARNING 'interpolation start % % ',nextnode.place_id,startnumber;
+          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 LOOP;
+
+        startnumber := substring(nextnode.housenumber,'[0-9]+')::integer;
+        prevnode := nextnode;
     END IF;
-  END IF;
+  END LOOP;
 
 --RAISE WARNING 'interpolation points % ',newpoints;
 
@@ -936,6 +922,11 @@ DECLARE
 BEGIN
   --DEBUG: RAISE WARNING '% %',NEW.osm_type,NEW.osm_id;
 
+  -- remove operator tag for most places, messes too much with search_name indexes
+  IF NEW.class not in ('amenity', 'shop') THEN
+    NEW.name := delete(NEW.name, 'operator');
+  END IF;
+
   -- just block these
   IF NEW.class in ('landuse','natural') and NEW.name is null THEN
 --    RAISE WARNING 'empty landuse %',NEW.osm_id;
@@ -1018,6 +1009,13 @@ BEGIN
             NEW.rank_address := 5;
           END IF;
 
+        ELSEIF NEW.calculated_country_code = 'sg' THEN
+
+          IF NEW.postcode ~ '^([0-9]{6})$' THEN
+            NEW.rank_search := 25;
+            NEW.rank_address := 11;
+          END IF;
+
         ELSEIF NEW.calculated_country_code = 'de' THEN
 
           IF NEW.postcode ~ '^([0-9]{5})$' THEN
@@ -1114,7 +1112,11 @@ BEGIN
       END IF;
     ELSEIF NEW.class = 'landuse' AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
       NEW.rank_search := 22;
-      NEW.rank_address := NEW.rank_search;
+      IF NEW.type in ('residential', 'farm', 'farmyard', 'industrial', 'commercial', 'allotments', 'retail') THEN
+        NEW.rank_address := NEW.rank_search;
+      ELSE
+        NEW.rank_address := 0;
+      END IF;
     ELSEIF NEW.class = 'natural' and NEW.type in ('peak','volcano','mountain_range') THEN
       NEW.rank_search := 18;
       NEW.rank_address := 0;
@@ -1126,7 +1128,7 @@ BEGIN
     -- any feature more than 5 square miles is probably worth indexing
     ELSEIF ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_Area(NEW.geometry) > 0.1 THEN
       NEW.rank_search := 22;
-      NEW.rank_address := NEW.rank_search;
+      NEW.rank_address := 0;
     ELSEIF NEW.class = 'highway' AND NEW.name is NULL AND 
            NEW.type in ('service','cycleway','path','footway','steps','bridleway','track','byway','motorway_link','primary_link','trunk_link','secondary_link','tertiary_link') THEN
 --      RAISE WARNING 'unnamed minor feature %',NEW.osm_id;
@@ -2062,6 +2064,11 @@ BEGIN
     --DEBUG: RAISE WARNING '%', existingplacex;
   END IF;
 
+  -- remove operator tag for most places, messes too much with search_name indexes
+  IF NEW.class not in ('amenity', 'shop') THEN
+    NEW.name := delete(NEW.name, 'operator');
+  END IF;
+
   -- Just block these - lots and pointless
   IF NEW.class in ('landuse','natural') and NEW.name is null THEN
     -- if the name tag was removed, older versions might still be lurking in the place table
@@ -2252,6 +2259,12 @@ BEGIN
 
   END IF;
 
+  -- refuse to update multiplpoygons with too many objects, too much of a performance hit
+  IF ST_NumGeometries(NEW.geometry) > 2000 THEN
+    RAISE WARNING 'Dropping update of % % because of geometry complexity.', NEW.osm_type, NEW.osm_id;
+    RETURN NULL;
+  END IF;
+
   IF coalesce(existing.name::text, '') != coalesce(NEW.name::text, '')
      OR coalesce(existing.extratags::text, '') != coalesce(NEW.extratags::text, '')
      OR coalesce(existing.housenumber, '') != coalesce(NEW.housenumber, '')
