X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/b1bd98f07064ed0bf4ed99105ab6038434d48041..2b8a563b527168ea48ea3756a743692ccda18866:/sql/functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index 398c2ee6..4b51ed3c 100644
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -1234,6 +1234,13 @@ DECLARE
   result BOOLEAN;
 BEGIN
 
+  -- deferred delete
+  IF OLD.indexed_status = 100 THEN
+    --DEBUG: RAISE WARNING 'placex_update_delete % %',NEW.osm_type,NEW.osm_id;
+    delete from placex where place_id = OLD.place_id;
+    RETURN NULL;
+  END IF;
+
   IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 OR NEW.linked_place_id is not null THEN
     RETURN NEW;
   END IF;
@@ -1248,13 +1255,6 @@ BEGIN
     RETURN NEW;
   END IF;
 
-  -- deferred delete
-  IF OLD.indexed_status = 100 THEN
-    --DEBUG: RAISE WARNING 'placex_update_delete % %',NEW.osm_type,NEW.osm_id;
-    delete from placex where place_id = OLD.place_id;
-    RETURN NULL;
-  END IF;
-
   IF OLD.indexed_status != 0 THEN
     --DEBUG: RAISE WARNING 'placex_update_0 % %',NEW.osm_type,NEW.osm_id;
 
@@ -1271,9 +1271,6 @@ 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 parent_place_id = null, indexed_status = 2 where parent_place_id = NEW.place_id and indexed_status = 0;
-      UPDATE placex set parent_place_id = null where parent_place_id = NEW.place_id;
-      UPDATE placex set linked_place_id = null, indexed_status = 2 where linked_place_id = NEW.place_id and indexed_status = 0;
       UPDATE placex set linked_place_id = null where linked_place_id = NEW.place_id;
     END IF;
 
@@ -1507,23 +1504,29 @@ BEGIN
 -- RAISE WARNING 'get_osm_rel_members, label';
       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))::char(1) 
-          and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 into linkedPlacex;
+        FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1) 
+          and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 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;
+          -- 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
+          IF NOT linkedPlacex.name IS NULL THEN
+            NEW.name := linkedPlacex.name || NEW.name;
+            name_vector := make_keywords(NEW.name);
+          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
+          IF NOT linkedPlacex.extratags IS NULL THEN
+            NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+          END IF;
 
-        -- 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;
 
-        -- 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 LOOP;
 
@@ -1531,33 +1534,39 @@ BEGIN
 
         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))::char(1) 
-            and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 into linkedPlacex;
+          FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1) 
+            and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP
 
-          -- For an admin centre we also want a name match - still not perfect, for example 'new york, new york'
-          -- But that can be fixed by explicitly setting the label in the data
-          IF make_standard_name(NEW.name->'name') = make_standard_name(linkedPlacex.name->'name') 
-            AND NEW.rank_search = linkedPlacex.rank_search THEN
+            -- For an admin centre we also want a name match - still not perfect, for example 'new york, new york'
+            -- But that can be fixed by explicitly setting the label in the data
+            IF make_standard_name(NEW.name->'name') = make_standard_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;
+              -- 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 the name, re-init word vector
+              IF NOT linkedPlacex.name IS NULL THEN
+                NEW.name := linkedPlacex.name || NEW.name;
+                name_vector := make_keywords(NEW.name);
+              END IF;
 
-            -- merge in extra tags
-            NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+              -- merge in extra tags
+              IF NOT linkedPlacex.extratags IS NULL THEN
+                NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+              END IF;
 
-            -- mark the linked place (excludes from search results)
-            UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
+              -- mark the linked place (excludes from search results)
+              UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id;
 
-            -- keep a note of the node id in case we need it for wikipedia in a bit
-            linked_node_id := linkedPlacex.osm_id;
-          END IF;
+              -- keep a note of the node id in case we need it for wikipedia in a bit
+              linked_node_id := linkedPlacex.osm_id;
+            END IF;
+
+          END LOOP;
 
         END LOOP;