fix edge cases when linking nodes

[nominatim.git] / sql / functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index 398c2ee62dc4860077a277fbe18d7dde6f5b240a..4b51ed3c84579b93e7e741532dd82fe1ac2284e8 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -1234,6 +1234,13 @@ DECLARE
   result BOOLEAN;
 BEGIN
 
   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;
   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;
 
     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;
 
   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);
       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;
 
       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
 
 -- 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;
 
 
       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
 
 
         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;
 
 
         END LOOP;