X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/80cf5df1cd5c249a547f877c73fd6feba9b763b0..fd3fcea539878b8f96cb9bcad51152459442ab48:/sql/functions.sql?ds=sidebyside

diff --git a/sql/functions.sql b/sql/functions.sql
index 7e9f957b..b248a46d 100644
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -909,18 +909,23 @@ BEGIN
   NEW.place_id := nextval('seq_place');
   NEW.indexed_status := 1; --STATUS_NEW
 
-  NEW.country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
+  IF NEW.rank_search >= 4 THEN
+    NEW.country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
+  ELSE
+    NEW.country_code := NULL;
+  END IF;
+
   NEW.partition := get_partition(NEW.geometry, NEW.country_code);
   NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
 
   -- copy 'name' to or from the default language (if there is a default language)
-  IF NEW.name is not null AND array_upper(%#NEW.name,1) > 1 THEN
+  IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
     default_language := get_country_language_code(NEW.country_code);
     IF default_language IS NOT NULL THEN
       IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
         NEW.name := NEW.name || (('name:'||default_language) => (NEW.name -> 'name'));
       ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
-        NEW.name := NEW.name || ('name' => (NEW.name -> 'name:'||default_language));
+        NEW.name := NEW.name || ('name' => (NEW.name -> ('name:'||default_language)));
       END IF;
     END IF;
   END IF;
@@ -942,6 +947,11 @@ BEGIN
     -- By doing in postgres we have the country available to us - currently only used for postcode
     IF NEW.class in ('place','boundary') AND NEW.type in ('postcode','postal_code') THEN
 
+        IF NEW.postcode IS NULL THEN
+            -- most likely just a part of a multipolygon postcode boundary, throw it away
+            RETURN NULL;
+        END IF;
+
         NEW.name := 'ref'=>NEW.postcode;
 
         IF NEW.country_code = 'gb' THEN
@@ -1029,9 +1039,13 @@ BEGIN
       ELSEIF NEW.type in ('suburb','croft','subdivision') THEN
         NEW.rank_search := 20;
         NEW.rank_address := NEW.rank_search;
-      ELSEIF NEW.type in ('farm','locality','islet','isolated_dwelling') THEN
+      ELSEIF NEW.type in ('farm','locality','islet','isolated_dwelling','mountain_pass') THEN
         NEW.rank_search := 20;
         NEW.rank_address := 0;
+        -- Irish townlands, tagged as place=locality and locality=townland
+        IF (NEW.extratags -> 'locality') = 'townland' THEN
+          NEW.rank_address := 20;
+        END IF;
       ELSEIF NEW.type in ('hall_of_residence','neighbourhood','housing_estate','nature_reserve') THEN
         NEW.rank_search := 22;
         NEW.rank_address := 22;
@@ -1189,6 +1203,9 @@ DECLARE
   location RECORD;
   way RECORD;
   relation RECORD;
+  relation_members TEXT[];
+  relMember RECORD;
+  linkedplacex RECORD;
   search_diameter FLOAT;
   search_prevdiameter FLOAT;
   search_maxrank INTEGER;
@@ -1240,11 +1257,16 @@ BEGIN
       DELETE FROM place_boundingbox where place_id = NEW.place_id;
       result := deleteRoad(NEW.partition, NEW.place_id);
       result := deleteLocationArea(NEW.partition, NEW.place_id);
+      UPDATE placex set linked_place_id = null where linked_place_id = NEW.place_id;
     END IF;
 
     -- reclaculate country and partition (should probably have a country_code and calculated_country_code as seperate fields)
-    SELECT country_code from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO NEW.country_code;
-    NEW.country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
+    IF NEW.rank_search >= 4 THEN
+      SELECT country_code from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO NEW.country_code;
+      NEW.country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
+    ELSE
+      NEW.country_code := NULL;
+    END IF;
     NEW.partition := get_partition(NEW.geometry, NEW.country_code);
     NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
 
@@ -1257,16 +1279,17 @@ BEGIN
     -- Speed up searches - just use the centroid of the feature
     -- cheaper but less acurate
     place_centroid := ST_Centroid(NEW.geometry);
+    NEW.centroid := null;
 
     -- Thought this wasn't needed but when we add new languages to the country_name table
     -- we need to update the existing names
-    IF NEW.name is not null AND array_upper(%#NEW.name,1) > 1 THEN
+    IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
       default_language := get_country_language_code(NEW.country_code);
       IF default_language IS NOT NULL THEN
         IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
           NEW.name := NEW.name || (('name:'||default_language) => (NEW.name -> 'name'));
         ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
-          NEW.name := NEW.name || ('name' => (NEW.name -> 'name:'||default_language));
+          NEW.name := NEW.name || ('name' => (NEW.name -> ('name:'||default_language)));
         END IF;
       END IF;
     END IF;
@@ -1452,6 +1475,95 @@ BEGIN
 
 -- RAISE WARNING '  INDEXING: %',NEW;
 
+    IF NEW.osm_type = 'R' AND NEW.rank_search < 26 THEN
+
+      -- see if we have any special relation members
+      select members from planet_osm_rels where id = NEW.osm_id INTO relation_members;
+
+      FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['label']) as member LOOP
+
+        select * from placex where osm_type = upper(substring(relMember.member,1,1)) 
+          and osm_id = substring(relMember.member,2,10000)::integer order by rank_search desc limit 1 into linkedPlacex;
+
+        -- If we don't already have one use this as the centre point of the geometry
+        IF NEW.centroid IS NULL THEN
+          NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
+        END IF;
+
+        -- merge in the label name, re-init word vector
+        NEW.name := linkedPlacex.name || NEW.name;
+        name_vector := make_keywords(NEW.name);
+
+        -- merge in extra tags
+        NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+
+        -- mark the linked place (excludes from search results)
+        UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
+
+      END LOOP;
+
+      IF NEW.centroid IS NULL THEN
+
+        FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP
+
+          select * from placex where osm_type = upper(substring(relMember.member,1,1)) 
+            and osm_id = substring(relMember.member,2,10000)::integer order by rank_search desc limit 1 into linkedPlacex;
+
+          IF NEW.name->'name' = linkedPlacex.name->'name' AND NEW.rank_search = linkedPlacex.rank_search THEN
+            -- If we don't already have one use this as the centre point of the geometry
+            IF NEW.centroid IS NULL THEN
+              NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
+            END IF;
+
+            -- merge in the name, re-init word vector
+            NEW.name := linkedPlacex.name || NEW.name;
+            name_vector := make_keywords(NEW.name);
+
+            -- merge in extra tags
+            NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+
+            -- mark the linked place (excludes from search results)
+            UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
+          END IF;
+
+        END LOOP;
+
+      END IF;
+
+      -- not found one yet? how about doing a name search
+      IF NEW.centroid IS NULL THEN
+        FOR linkedPlacex IN select placex.* from search_name join placex using (place_id) WHERE
+          search_name.name_vector @> ARRAY[getorcreate_name_id(make_standard_name(NEW.name->'name'))] 
+          AND search_name.search_rank = NEW.rank_search
+          AND search_name.place_id != NEW.place_id
+          AND osm_type = 'N'
+          AND NEW.name->'name' = placex.name->'name'
+          AND st_contains(NEW.geometry, placex.geometry)
+        LOOP
+          -- If we don't already have one use this as the centre point of the geometry
+          IF NEW.centroid IS NULL THEN
+            NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry));
+          END IF;
+
+          -- merge in the name, re-init word vector
+          NEW.name := linkedPlacex.name || NEW.name;
+          name_vector := make_keywords(NEW.name);
+
+          -- merge in extra tags
+          NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+
+          -- mark the linked place (excludes from search results)
+          UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
+
+        END LOOP;
+      END IF;
+
+      IF NEW.centroid IS NOT NULL THEN
+        place_centroid := NEW.centroid;
+      END IF;
+
+    END IF;
+
     NEW.parent_place_id = 0;
     parent_place_id_rank = 0;
 
@@ -1576,6 +1688,11 @@ BEGIN
 --      INSERT INTO search_name values (NEW.place_id, NEW.rank_search, NEW.rank_search, 0, NEW.country_code, name_vector, nameaddress_vector, place_centroid);
     END IF;
 
+    -- If we've not managed to pick up a better one - default centroid
+    IF NEW.centroid IS NULL THEN
+      NEW.centroid := place_centroid;
+    END IF;
+
   END IF;
 
   RETURN NEW;
@@ -1590,6 +1707,9 @@ DECLARE
   classtable TEXT;
 BEGIN
 
+  update placex set linked_place_id = null where linked_place_id = OLD.place_id;
+  update placex set indexed_status = 2 where linked_place_id = OLD.place_id and indexed_status = 0;
+
   IF OLD.rank_address < 30 THEN
 
     -- mark everything linked to this place for re-indexing
@@ -2423,8 +2543,8 @@ DECLARE
   numberrange INTEGER;
   rangestartnumber INTEGER;
   place_centroid GEOMETRY;
-  partition INTEGER;
-  parent_place_id BIGINT;
+  out_partition INTEGER;
+  out_parent_place_id BIGINT;
   location RECORD;
   address_street_word_id INTEGER;  
 
@@ -2461,32 +2581,32 @@ BEGIN
   END IF;
 
   place_centroid := ST_Centroid(linegeo);
-  partition := get_partition(place_centroid, 'us');
-  parent_place_id := null;
+  out_partition := get_partition(place_centroid, 'us');
+  out_parent_place_id := null;
 
   address_street_word_id := get_name_id(make_standard_name(in_street));
   IF address_street_word_id IS NOT NULL THEN
-    FOR location IN SELECT * from getNearestNamedRoadFeature(partition, place_centroid, address_street_word_id) LOOP
-      parent_place_id := location.place_id;
+    FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, address_street_word_id) LOOP
+      out_parent_place_id := location.place_id;
     END LOOP;
   END IF;
 
-  IF parent_place_id IS NULL THEN
-    FOR location IN SELECT place_id FROM getNearestParellelRoadFeature(partition, linegeo) LOOP
-      parent_place_id := location.place_id;
+  IF out_parent_place_id IS NULL THEN
+    FOR location IN SELECT place_id FROM getNearestParellelRoadFeature(out_partition, linegeo) LOOP
+      out_parent_place_id := location.place_id;
     END LOOP;    
   END IF;
 
-  IF parent_place_id IS NULL THEN
-    FOR location IN SELECT place_id FROM getNearestRoadFeature(partition, place_centroid) LOOP
-      parent_place_id := location.place_id;
+  IF out_parent_place_id IS NULL THEN
+    FOR location IN SELECT place_id FROM getNearestRoadFeature(out_partition, place_centroid) LOOP
+      out_parent_place_id := location.place_id;
     END LOOP;    
   END IF;
 
   newpoints := 0;
   FOR housenum IN startnumber..endnumber BY stepsize LOOP
     insert into location_property_tiger (place_id, partition, parent_place_id, housenumber, postcode, centroid)
-    values (nextval('seq_place'), partition, parent_place_id, housenum, in_postcode,
+    values (nextval('seq_place'), out_partition, out_parent_place_id, housenum, in_postcode,
       ST_Line_Interpolate_Point(linegeo, (housenum::float-rangestartnumber::float)/numberrange::float));
     newpoints := newpoints + 1;
   END LOOP;
@@ -2503,7 +2623,7 @@ DECLARE
 
   newpoints INTEGER;
   place_centroid GEOMETRY;
-  partition INTEGER;
+  out_partition INTEGER;
   out_parent_place_id BIGINT;
   location RECORD;
   address_street_word_id INTEGER;  
@@ -2512,18 +2632,18 @@ DECLARE
 BEGIN
 
   place_centroid := ST_Centroid(pointgeo);
-  partition := get_partition(place_centroid, in_countrycode);
+  out_partition := get_partition(place_centroid, in_countrycode);
   out_parent_place_id := null;
 
   address_street_word_id := get_name_id(make_standard_name(in_street));
   IF address_street_word_id IS NOT NULL THEN
-    FOR location IN SELECT * from getNearestNamedRoadFeature(partition, place_centroid, address_street_word_id) LOOP
+    FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, address_street_word_id) LOOP
       out_parent_place_id := location.place_id;
     END LOOP;
   END IF;
 
   IF out_parent_place_id IS NULL THEN
-    FOR location IN SELECT place_id FROM getNearestRoadFeature(partition, place_centroid) LOOP
+    FOR location IN SELECT place_id FROM getNearestRoadFeature(out_partition, place_centroid) LOOP
       out_parent_place_id := location.place_id;
     END LOOP;    
   END IF;
@@ -2533,15 +2653,52 @@ BEGIN
     SELECT postcode from placex where place_id = out_parent_place_id INTO out_postcode;
   END IF;
   IF out_postcode IS NULL THEN
-    out_postcode := getNearestPostcode(partition, place_centroid);
+    out_postcode := getNearestPostcode(out_partition, place_centroid);
   END IF;
 
   newpoints := 0;
   insert into location_property_aux (place_id, partition, parent_place_id, housenumber, postcode, centroid)
-    values (nextval('seq_place'), partition, out_parent_place_id, in_housenumber, out_postcode, place_centroid);
+    values (nextval('seq_place'), out_partition, out_parent_place_id, in_housenumber, out_postcode, place_centroid);
   newpoints := newpoints + 1;
 
   RETURN newpoints;
 END;
 $$
 LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION get_osm_rel_members(members TEXT[], member TEXT) RETURNS TEXT[]
+  AS $$
+DECLARE
+  result TEXT[];
+  i INTEGER;
+BEGIN
+
+  FOR i IN 1..ARRAY_UPPER(members,1) BY 2 LOOP
+    IF members[i+1] = member THEN
+      result := result || members[i];
+    END IF;
+  END LOOP;
+
+  return result;
+END;
+$$
+LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION get_osm_rel_members(members TEXT[], memberLabels TEXT[]) RETURNS SETOF TEXT
+  AS $$
+DECLARE
+  i INTEGER;
+BEGIN
+
+  FOR i IN 1..ARRAY_UPPER(members,1) BY 2 LOOP
+    IF members[i+1] = ANY(memberLabels) THEN
+      RETURN NEXT members[i];
+    END IF;
+  END LOOP;
+
+  RETURN;
+END;
+$$
+LANGUAGE plpgsql;
+
+
