improve handling von updates on nodes in interpolations

Use the same update mechanism as for updates on the interpolations
themselves. Updates must solely happen in place_insert as this is
the place where actual changes of the data happen.

diff --git a/lib-sql/functions/interpolation.sql b/lib-sql/functions/interpolation.sql
index a8ef37715f59266f5bbfb57add57e2709fc7f9bc..098db26c2a1efc71b6245fd183dd9f484a767a2d 100644 (file)
--- a/lib-sql/functions/interpolation.sql
+++ b/lib-sql/functions/interpolation.sql
@@ -82,24 +82,38 @@ $$
 LANGUAGE plpgsql STABLE;
 
 
-CREATE OR REPLACE FUNCTION osmline_reinsert(node_id BIGINT, geom GEOMETRY)
-  RETURNS BOOLEAN
+CREATE OR REPLACE FUNCTION reinsert_interpolation(way_id BIGINT, addr HSTORE,
+                                                  geom GEOMETRY)
+  RETURNS INT
   AS $$
 DECLARE
-  existingline RECORD;
+  existing BIGINT[];
 BEGIN
-   SELECT w.id FROM planet_osm_ways w, location_property_osmline p
-     WHERE p.linegeo && geom and p.osm_id = w.id and p.indexed_status = 0
-           and node_id = any(w.nodes) INTO existingline;
-
-   IF existingline.id is not NULL THEN
-       DELETE FROM location_property_osmline WHERE osm_id = existingline.id;
-       INSERT INTO location_property_osmline (osm_id, address, linegeo)
-         SELECT osm_id, address, geometry FROM place
-           WHERE osm_type = 'W' and osm_id = existingline.id;
-   END IF;
-
-   RETURN true;
+  -- Get the existing entry from the interpolation table.
+  SELECT array_agg(place_id) INTO existing
+    FROM location_property_osmline WHERE osm_id = way_id;
+
+  IF existing IS NULL or array_length(existing, 1) = 0 THEN
+    INSERT INTO location_property_osmline (osm_id, address, linegeo)
+      VALUES (way_id, addr, geom);
+  ELSE
+    -- Update the interpolation table:
+    --   The first entry gets the original data, all other entries
+    --   are removed and will be recreated on indexing.
+    --   (An interpolation can be split up, if it has more than 2 address nodes)
+    UPDATE location_property_osmline
+      SET address = addr,
+          linegeo = geom,
+          startnumber = null,
+          indexed_status = 1
+      WHERE place_id = existing[1];
+    IF array_length(existing, 1) > 1 THEN
+      DELETE FROM location_property_osmline
+        WHERE place_id = any(existing[2:]);
+    END IF;
+  END IF;
+
+  RETURN 1;
 END;
 $$
 LANGUAGE plpgsql;

diff --git a/lib-sql/functions/place_triggers.sql b/lib-sql/functions/place_triggers.sql
index 7ea088407a8b95a4dcb25b3bf3c307d7b825a80d..a472d26d898e5b9df70e06014ca55b936e28eb5c 100644 (file)
--- a/lib-sql/functions/place_triggers.sql
+++ b/lib-sql/functions/place_triggers.sql
@@ -14,7 +14,7 @@ DECLARE
   existing RECORD;
   existingplacex RECORD;
   existingline BIGINT[];
-  result BOOLEAN;
+  interpol RECORD;
 BEGIN
   {% if debug %}
     RAISE WARNING 'place_insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,st_area(NEW.geometry);
@@ -57,29 +57,7 @@ BEGIN
   IF NEW.class='place' and NEW.type='houses'
      and NEW.osm_type='W' and ST_GeometryType(NEW.geometry) = 'ST_LineString'
   THEN
-    -- Get the existing entry from the interpolation table.
-    SELECT array_agg(place_id) INTO existingline
-      FROM location_property_osmline WHERE osm_id = NEW.osm_id;
-
-    IF existingline IS NULL or array_length(existingline, 1) = 0 THEN
-      INSERT INTO location_property_osmline (osm_id, address, linegeo)
-        VALUES (NEW.osm_id, NEW.address, NEW.geometry);
-    ELSE
-      -- Update the interpolation table:
-      --   The first entry gets the original data, all other entries
-      --   are removed and will be recreated on indexing.
-      --   (An interpolation can be split up, if it has more than 2 address nodes)
-      UPDATE location_property_osmline
-        SET address = NEW.address,
-            linegeo = NEW.geometry,
-            startnumber = null,
-            indexed_status = 1
-        WHERE place_id = existingline[1];
-      IF array_length(existingline, 1) > 1 THEN
-        DELETE FROM location_property_osmline
-          WHERE place_id = any(existingline[2:]);
-      END IF;
-    END IF;
+    PERFORM reinsert_interpolation(NEW.osm_id, NEW.address, NEW.geometry);
 
     -- Now invalidate all address nodes on the line.
     -- They get their parent from the interpolation.
@@ -166,6 +144,28 @@ BEGIN
     RETURN null;
   END IF;
 
+  -- If an address node is part of a interpolation line and changes or is
+  -- newly inserted (happens when the node already existed but now gets address
+  -- information), then mark the interpolation line for reparenting.
+  -- (Already here, because interpolation lines are reindexed before nodes,
+  --  so in the second call it would be too late.)
+  IF NEW.osm_type='N'
+     and coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore)
+  THEN
+      FOR interpol IN
+        SELECT DISTINCT osm_id, address, geometry FROM place, planet_osm_ways w
+        WHERE NEW.geometry && place.geometry
+              and place.osm_type = 'W'
+              and exists (SELECT * FROM location_property_osmline
+                          WHERE osm_id = place.osm_id
+                                and indexed_status in (0, 2))
+              and w.id = place.osm_id and NEW.osm_id = any (w.nodes)
+      LOOP
+        PERFORM reinsert_interpolation(interpol.osm_id, interpol.address,
+                                       interpol.geometry);
+      END LOOP;
+  END IF;
+
   -- Get the existing placex entry.
   SELECT * INTO existingplacex
     FROM placex
@@ -288,16 +288,6 @@ BEGIN
           geometry = NEW.geometry
       WHERE place_id = existingplacex.place_id;
 
-    -- If an address node which is part of a interpolation line changes
-    -- mark this line for reparenting.
-    -- (Already here, because interpolation lines are reindexed before nodes,
-    --  so in the second call it would be too late.)
-    IF NEW.osm_type='N'
-       and coalesce(existing.address, ''::hstore) != coalesce(NEW.address, ''::hstore)
-    THEN
-        result:= osmline_reinsert(NEW.osm_id, NEW.geometry);
-    END IF;
-
     -- Invalidate linked places: they potentially get a new name and addresses.
     IF existingplacex.linked_place_id is not NULL THEN
       UPDATE placex x

diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql
index 1b96f87e4f079ce094e813d8e4dcddfe798a9f81..8ad8a336f5fbfa7c6a0e262aa95df650747dbb43 100644 (file)
--- a/lib-sql/functions/placex_triggers.sql
+++ b/lib-sql/functions/placex_triggers.sql
@@ -626,10 +626,7 @@ BEGIN
 {% if not disable_diff_updates %}
   -- The following is not needed until doing diff updates, and slows the main index process down
 
-  IF NEW.osm_type = 'N' and NEW.rank_search > 28 THEN
-      -- might be part of an interpolation
-      result := osmline_reinsert(NEW.osm_id, NEW.geometry);
-  ELSEIF NEW.rank_address > 0 THEN
+  IF NEW.rank_address > 0 THEN
     IF (ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(NEW.geometry)) THEN
       -- Performance: We just can't handle re-indexing for country level changes
       IF st_area(NEW.geometry) < 1 THEN

diff --git a/lib-sql/indices.sql b/lib-sql/indices.sql
index bbcaf43da553d43d6a9e7e783392cd4f79ea1b95..fed3452402d1cbde20f7a2143f6cd234cb7eef06 100644 (file)
--- a/lib-sql/indices.sql
+++ b/lib-sql/indices.sql
@@ -48,6 +48,10 @@ CREATE INDEX IF NOT EXISTS idx_postcode_postcode
 
   CREATE UNIQUE INDEX IF NOT EXISTS idx_place_osm_unique
     ON place USING btree(osm_id, osm_type, class, type) {{db.tablespace.address_index}};
+
+  CREATE INDEX IF NOT EXISTS idx_place_interpolations
+    ON place USING gist(geometry) {{db.tablespace.address_index}}
+    WHERE osm_type = 'W' and address ? 'interpolation';
 {% endif %}
 
 -- Indices only needed for search.