X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/5882a15bac2c47ad0d27b2446f6390feded4f869..11a6b8a6e23a150d6e9e6d0094ef4613df4650a5:/sql/functions.sql?ds=inline

diff --git a/sql/functions.sql b/sql/functions.sql
index 4b0483c3..9b083609 100644
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -936,6 +936,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;
@@ -1136,7 +1141,11 @@ BEGIN
     ELSEIF NEW.class = 'waterway' AND NEW.name is NULL THEN
       RETURN NULL;
     ELSEIF NEW.class = 'waterway' THEN
-      NEW.rank_search := 17;
+      IF NEW.osm_type = 'R' THEN
+        NEW.rank_search := 16;
+      ELSE
+        NEW.rank_search := 17;
+      END IF;
       NEW.rank_address := 0;
     ELSEIF NEW.class = 'highway' AND NEW.osm_type != 'N' AND NEW.type in ('service','cycleway','path','footway','steps','bridleway','motorway_link','primary_link','trunk_link','secondary_link','tertiary_link') THEN
       NEW.rank_search := 27;
@@ -1366,6 +1375,25 @@ BEGIN
     END IF;
     NEW.geometry_sector := geometry_sector(NEW.partition, place_centroid);
 
+    -- waterway ways are linked when they are part of a relation and have the same class/type
+    IF NEW.osm_type = 'R' and NEW.class = 'waterway' THEN
+        FOR relation IN select * from planet_osm_rels r where r.id = NEW.osm_id and r.parts != array[]::bigint[]
+        LOOP
+            FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP
+                IF relation.members[i+1] in ('', 'main_stream', 'side_stream') AND substring(relation.members[i],1,1) = 'w' THEN
+                  --DEBUG: RAISE WARNING 'waterway parent %, child %/%', NEW.osm_id, i, relation.parts[i];
+                  FOR location IN SELECT * FROM placex
+                    WHERE osm_type = 'W' and osm_id = substring(relation.members[i],2,200)::bigint
+                    and class = NEW.class and type = NEW.type
+                    and ( relation.members[i+1] != 'side_stream' or NEW.name->'name' = name->'name')
+                  LOOP
+                    UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = location.place_id;
+                  END LOOP;
+                END IF;
+            END LOOP;
+        END LOOP;
+    END IF;
+
     -- Adding ourselves to the list simplifies address calculations later
     INSERT INTO place_addressline VALUES (NEW.place_id, NEW.place_id, true, true, 0, NEW.rank_address); 
 
@@ -1422,6 +1450,16 @@ BEGIN
       -- Note that addr:street links can only be indexed once the street itself is indexed
       IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN
 
+        -- if there is no address information, see if we can get it from a surrounding building
+        IF NEW.street IS NULL AND NEW.addr_place IS NULL AND NEW.housenumber IS NULL THEN
+          FOR location IN select * from placex where ST_Covers(geometry, place_centroid) and rank_search > 28 and (housenumber is not null or street is not null or addr_place is not null) AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
+          LOOP
+            NEW.housenumber := location.housenumber;
+            NEW.street := location.street;
+            NEW.addr_place := location.addr_place;
+          END LOOP;
+        END IF;
+
         -- Is this node part of a relation?
         FOR relation IN select * from planet_osm_rels where parts @> ARRAY[NEW.osm_id] and members @> ARRAY['n'||NEW.osm_id]
         LOOP
@@ -1431,7 +1469,7 @@ BEGIN
               IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN
 --RAISE WARNING 'node in relation %',relation;
                 SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint 
-                  and rank_search = 26 INTO NEW.parent_place_id;
+                  and rank_search = 26 and name is not null INTO NEW.parent_place_id;
               END IF;
             END LOOP;
           END IF;
@@ -1460,7 +1498,7 @@ BEGIN
                     IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN
     --RAISE WARNING 'node in way that is in a relation %',relation;
                       SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint 
-                        and rank_search = 26 INTO NEW.parent_place_id;
+                        and rank_search = 26 and name is not null INTO NEW.parent_place_id;
                     END IF;
                   END LOOP;
                 END IF;
@@ -1468,13 +1506,10 @@ BEGIN
           END IF;    
           
           -- If the way contains an explicit name of a street copy it
-          IF NEW.street IS NULL AND NEW.addr_place IS NULL AND location.street IS NOT NULL THEN
+          -- Slightly less strict then above because data is copied from any object.
+          IF NEW.street IS NULL AND NEW.addr_place IS NULL THEN
 --RAISE WARNING 'node in way that has a streetname %',location;
             NEW.street := location.street;
-          END IF;
-
-          -- IF the way contains an explicit name of a place copy it
-          IF NEW.addr_place IS NULL AND NEW.street IS NULL AND location.addr_place IS NOT NULL THEN
             NEW.addr_place := location.addr_place;
           END IF;
 
@@ -1510,7 +1545,7 @@ BEGIN
               IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN
 --RAISE WARNING 'way that is in a relation %',relation;
                 SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint
-                  and rank_search = 26 INTO NEW.parent_place_id;
+                  and rank_search = 26 and name is not null INTO NEW.parent_place_id;
               END IF;
             END LOOP;
           END IF;
@@ -1529,7 +1564,7 @@ BEGIN
       END IF;
 
       IF NEW.parent_place_id IS NULL AND NEW.addr_place IS NOT NULL THEN
-        address_street_word_ids := get_name_id(make_standard_name(NEW.addr_place));
+        address_street_word_ids := get_name_ids(make_standard_name(NEW.addr_place));
         IF address_street_word_ids IS NOT NULL THEN
           FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP
             NEW.parent_place_id := location.place_id;
@@ -2032,6 +2067,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
@@ -2222,6 +2262,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, '')
@@ -2460,7 +2506,7 @@ BEGIN
       CASE WHEN class = 'place' and type = 'postcode' THEN hstore('name', postcode) ELSE name END as name,
       class, type, admin_level, true as fromarea, true as isaddress,
       CASE WHEN rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
-      0 as distance, calculated_country_code
+      0 as distance, calculated_country_code, postcode
       from placex
       where place_id = for_place_id 
   LOOP
@@ -2471,6 +2517,9 @@ BEGIN
     IF searchpostcode IS NOT NULL and location.type = 'postcode' THEN
       location.isaddress := FALSE;
     END IF;
+    IF searchpostcode IS NULL and location.postcode IS NOT NULL THEN
+      searchpostcode := location.postcode;
+    END IF;
     IF location.rank_address = 4 AND location.isaddress THEN
       hadcountry := true;
     END IF;
@@ -2491,7 +2540,9 @@ BEGIN
   FOR location IN 
     select placex.place_id, osm_type, osm_id,
       CASE WHEN class = 'place' and type = 'postcode' THEN hstore('name', postcode) ELSE name END as name,
-      class, type, admin_level, fromarea, isaddress,
+      CASE WHEN extratags ? 'place' THEN 'place' ELSE class END as class,
+      CASE WHEN extratags ? 'place' THEN extratags->'place' ELSE type END as type,
+      admin_level, fromarea, isaddress,
       CASE WHEN address_place_id = for_place_id AND rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
       distance,calculated_country_code,postcode
       from place_addressline join placex on (address_place_id = placex.place_id) 
@@ -2922,14 +2973,14 @@ BEGIN
       wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3}).wikipedia.org/wiki/',E'\\2:');
       wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3}).wikipedia.org/w/index.php\\?title=',E'\\2:');
       wiki_article := regexp_replace(wiki_article,E'^(.*?)/([a-z]{2,3})/wiki/',E'\\2:');
-      wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3})[=:]',E'\\2:');
+      --wiki_article := regexp_replace(wiki_article,E'^(.*?)([a-z]{2,3})[=:]',E'\\2:');
       wiki_article := replace(wiki_article,' ','_');
-      wiki_article_title := trim(split_part(wiki_article, ':', 2));
-      IF wiki_article_title IS NULL OR wiki_article_title = '' THEN
+      IF strpos(wiki_article, ':') IN (3,4) THEN
+        wiki_article_language := lower(trim(split_part(wiki_article, ':', 1)));
+        wiki_article_title := trim(substr(wiki_article, strpos(wiki_article, ':')+1));
+      ELSE
         wiki_article_title := trim(wiki_article);
         wiki_article_language := CASE WHEN langs[i] = 'english' THEN 'en' WHEN langs[i] = 'country' THEN get_country_language_code(country_code) ELSE langs[i] END;
-      ELSE
-        wiki_article_language := lower(trim(split_part(wiki_article, ':', 1)));
       END IF;
 
       select wikipedia_article.language,wikipedia_article.title,wikipedia_article.importance
