add migration for new interpolation table layout

[nominatim.git] / lib-sql / functions / interpolation.sql

diff --git a/lib-sql/functions/interpolation.sql b/lib-sql/functions/interpolation.sql
index 55e44dfd646e05f497658ba401207b0fd2194b67..098db26c2a1efc71b6245fd183dd9f484a767a2d 100644 (file)
--- a/lib-sql/functions/interpolation.sql
+++ b/lib-sql/functions/interpolation.sql
@@ -1,3 +1,10 @@
+-- SPDX-License-Identifier: GPL-2.0-only
+--
+-- This file is part of Nominatim. (https://nominatim.org)
+--
+-- Copyright (C) 2022 by the Nominatim developer community.
+-- For a full list of authors see the git log.
+

 -- Functions for address interpolation objects in location_property_osmline.
 
 -- Splits the line at the given point and returns the two parts
 -- Functions for address interpolation objects in location_property_osmline.
 
 -- Splits the line at the given point and returns the two parts


@@ -43,7 +50,7 @@ LANGUAGE plpgsql STABLE;
 
 
 -- find the parent road of the cut road parts
 
 
 -- find the parent road of the cut road parts

-CREATE OR REPLACE FUNCTION get_interpolation_parent(street INTEGER[], place INTEGER[],
+CREATE OR REPLACE FUNCTION get_interpolation_parent(token_info JSONB,

                                                     partition SMALLINT,
                                                     centroid GEOMETRY, geom GEOMETRY)
   RETURNS BIGINT
                                                     partition SMALLINT,
                                                     centroid GEOMETRY, geom GEOMETRY)
   RETURNS BIGINT


@@ -52,7 +59,7 @@ DECLARE
   parent_place_id BIGINT;
   location RECORD;
 BEGIN
   parent_place_id BIGINT;
   location RECORD;
 BEGIN

-  parent_place_id := find_parent_for_address(street, place, partition, centroid);
+  parent_place_id := find_parent_for_address(token_info, partition, centroid);

 
   IF parent_place_id is null THEN
     FOR location IN SELECT place_id FROM placex
 
   IF parent_place_id is null THEN
     FOR location IN SELECT place_id FROM placex


@@ -75,24 +82,38 @@ $$
 LANGUAGE plpgsql STABLE;
 
 
 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
   AS $$
 DECLARE

-  existingline RECORD;
+  existing BIGINT[];

 BEGIN
 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;
 END;
 $$
 LANGUAGE plpgsql;


@@ -155,9 +176,8 @@ BEGIN
   NEW.interpolationtype = NEW.address->'interpolation';
 
   place_centroid := ST_PointOnSurface(NEW.linegeo);
   NEW.interpolationtype = NEW.address->'interpolation';
 
   place_centroid := ST_PointOnSurface(NEW.linegeo);

-  NEW.parent_place_id = get_interpolation_parent(token_addr_street_match_tokens(NEW.token_info),
-                                                 token_addr_place_match_tokens(NEW.token_info),
-                                                 NEW.partition, place_centroid, NEW.linegeo);
+  NEW.parent_place_id = get_interpolation_parent(NEW.token_info, NEW.partition,
+                                                 place_centroid, NEW.linegeo);

 
   interpol_postcode := token_normalized_postcode(NEW.address->'postcode');
 
 
   interpol_postcode := token_normalized_postcode(NEW.address->'postcode');