Merge pull request #2818 from lonvia/better-geometry-index

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

diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql
index 92c0c4ec250013779d240848b5c8d0b0ae61b264..6c23fd6799a569c5d8ac4d6728fe30abfef3b781 100644 (file)
--- a/lib-sql/functions/placex_triggers.sql
+++ b/lib-sql/functions/placex_triggers.sql
@@ -16,7 +16,9 @@ CREATE TYPE prepare_update_info AS (
   country_code TEXT,
   class TEXT,
   type TEXT,
   country_code TEXT,
   class TEXT,
   type TEXT,

-  linked_place_id BIGINT
+  linked_place_id BIGINT,
+  centroid_x float,
+  centroid_y float

 );
 
 -- Retrieve the data needed by the indexer for updating the place.
 );
 
 -- Retrieve the data needed by the indexer for updating the place.


@@ -47,7 +49,7 @@ BEGIN
                and rank_search = 30 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
          LIMIT 1;
     ELSE
                and rank_search = 30 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
          LIMIT 1;
     ELSE

-      -- See if we can inherit addtional address tags from an interpolation.
+      -- See if we can inherit additional address tags from an interpolation.

       -- These will become permanent.
       FOR location IN
         SELECT (address - 'interpolation'::text - 'housenumber'::text) as address
       -- These will become permanent.
       FOR location IN
         SELECT (address - 'interpolation'::text - 'housenumber'::text) as address


@@ -71,6 +73,8 @@ BEGIN
   result.type := p.type;
   result.country_code := p.country_code;
   result.rank_address := p.rank_address;
   result.type := p.type;
   result.country_code := p.country_code;
   result.rank_address := p.rank_address;

+  result.centroid_x := ST_X(p.centroid);
+  result.centroid_y := ST_Y(p.centroid);

 
   -- Names of linked places need to be merged in, so search for a linkable
   -- place already here.
 
   -- Names of linked places need to be merged in, so search for a linkable
   -- place already here.


@@ -773,12 +777,6 @@ BEGIN
           and (linked_place is null or linked_place_id != linked_place);
   -- update not necessary for osmline, cause linked_place_id does not exist
 
           and (linked_place is null or linked_place_id != linked_place);
   -- update not necessary for osmline, cause linked_place_id does not exist
 

-  IF NEW.linked_place_id is not null THEN
-    NEW.token_info := null;
-    {% if debug %}RAISE WARNING 'place already linked to %', OLD.linked_place_id;{% endif %}
-    RETURN NEW;
-  END IF;
-

   -- Postcodes are just here to compute the centroids. They are not searchable
   -- unless they are a boundary=postal_code.
   -- There was an error in the style so that boundary=postal_code used to be
   -- Postcodes are just here to compute the centroids. They are not searchable
   -- unless they are a boundary=postal_code.
   -- There was an error in the style so that boundary=postal_code used to be


@@ -823,6 +821,16 @@ BEGIN
                             NEW.class, NEW.type, NEW.admin_level,
                             (NEW.extratags->'capital') = 'yes',
                             NEW.address->'postcode');
                             NEW.class, NEW.type, NEW.admin_level,
                             (NEW.extratags->'capital') = 'yes',
                             NEW.address->'postcode');

+
+  -- Short-cut out for linked places. Note that this must happen after the
+  -- address rank has been recomputed. The linking might nullify a shift in
+  -- address rank.
+  IF NEW.linked_place_id is not null THEN
+    NEW.token_info := null;
+    {% if debug %}RAISE WARNING 'place already linked to %', OLD.linked_place_id;{% endif %}
+    RETURN NEW;
+  END IF;
+

   -- We must always increase the address level relative to the admin boundary.
   IF NEW.class = 'boundary' and NEW.type = 'administrative'
      and NEW.osm_type = 'R' and NEW.rank_address > 0
   -- We must always increase the address level relative to the admin boundary.
   IF NEW.class = 'boundary' and NEW.type = 'administrative'
      and NEW.osm_type = 'R' and NEW.rank_address > 0


@@ -860,29 +868,61 @@ BEGIN
 
     IF NEW.rank_address > 9 THEN
         -- Second check that the boundary is not completely contained in a
 
     IF NEW.rank_address > 9 THEN
         -- Second check that the boundary is not completely contained in a

-        -- place area with a higher address rank
+        -- place area with a equal or higher address rank.

         FOR location IN
         FOR location IN

-          SELECT rank_address FROM placex
-          WHERE class = 'place' and rank_address < 24
-                and rank_address > NEW.rank_address
+          SELECT rank_address
+          FROM placex,
+               LATERAL compute_place_rank(country_code, 'A', class, type,
+                                          admin_level, False, null) prank
+          WHERE class = 'place' and rank_address between 1 and 23
+                and prank.address_rank >= NEW.rank_address
+                and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') -- select right index

                 and geometry && NEW.geometry
                 and geometry ~ NEW.geometry -- needed because ST_Relate does not do bbox cover test
                 and ST_Relate(geometry, NEW.geometry, 'T*T***FF*') -- contains but not equal
                 and geometry && NEW.geometry
                 and geometry ~ NEW.geometry -- needed because ST_Relate does not do bbox cover test
                 and ST_Relate(geometry, NEW.geometry, 'T*T***FF*') -- contains but not equal

-          ORDER BY rank_address desc LIMIT 1
+          ORDER BY prank.address_rank desc LIMIT 1

         LOOP
           NEW.rank_address := location.rank_address + 2;
         END LOOP;
     END IF;
         LOOP
           NEW.rank_address := location.rank_address + 2;
         END LOOP;
     END IF;

+  ELSEIF NEW.class = 'place'
+         and ST_GeometryType(NEW.geometry) in ('ST_Polygon', 'ST_MultiPolygon')
+         and NEW.rank_address between 16 and 23
+  THEN
+    -- For place areas make sure they are not completely contained in an area
+    -- with a equal or higher address rank.
+    FOR location IN
+          SELECT rank_address
+          FROM placex,
+               LATERAL compute_place_rank(country_code, 'A', class, type,
+                                          admin_level, False, null) prank
+          WHERE prank.address_rank < 24
+                and rank_address between 1 and 25 -- select right index
+                and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') -- select right index
+                and prank.address_rank >= NEW.rank_address
+                and geometry && NEW.geometry
+                and geometry ~ NEW.geometry -- needed because ST_Relate does not do bbox cover test
+                and ST_Relate(geometry, NEW.geometry, 'T*T***FF*') -- contains but not equal
+          ORDER BY prank.address_rank desc LIMIT 1
+        LOOP
+          NEW.rank_address := location.rank_address + 2;
+        END LOOP;

   ELSEIF NEW.class = 'place' and NEW.osm_type = 'N'
   ELSEIF NEW.class = 'place' and NEW.osm_type = 'N'

-     and NEW.rank_address between 16 and 23
+         and NEW.rank_address between 16 and 23

   THEN
   THEN

-    -- If a place node is contained in a admin boundary with the same address level
-    -- and has not been linked, then make the node a subpart by increasing the
-    -- address rank (city level and above).
+    -- If a place node is contained in an admin or place boundary with the same
+    -- address level and has not been linked, then make the node a subpart
+    -- by increasing the address rank (city level and above).

     FOR location IN
     FOR location IN

-        SELECT rank_address FROM placex
-        WHERE osm_type = 'R' and class = 'boundary' and type = 'administrative'
-              and rank_address = NEW.rank_address
+        SELECT rank_address
+        FROM placex,
+             LATERAL compute_place_rank(country_code, 'A', class, type,
+                                        admin_level, False, null) prank
+        WHERE osm_type = 'R'
+              and rank_address between 1 and 25 -- select right index
+              and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') -- select right index
+              and ((class = 'place' and prank.address_rank = NEW.rank_address)
+                   or (class = 'boundary' and rank_address = NEW.rank_address))

               and geometry && NEW.centroid and _ST_Covers(geometry, NEW.centroid)
         LIMIT 1
     LOOP
               and geometry && NEW.centroid and _ST_Covers(geometry, NEW.centroid)
         LIMIT 1
     LOOP


@@ -962,7 +1002,7 @@ BEGIN
       {% if debug %}RAISE WARNING 'Got parent details from search name';{% endif %}
 
       -- determine postcode
       {% if debug %}RAISE WARNING 'Got parent details from search name';{% endif %}
 
       -- determine postcode

-      NEW.postcode := coalesce(token_normalized_postcode(NEW.address->'postcode'),
+      NEW.postcode := coalesce(token_get_postcode(NEW.token_info),

                                location.postcode,
                                get_nearest_postcode(NEW.country_code, NEW.centroid));
 
                                location.postcode,
                                get_nearest_postcode(NEW.country_code, NEW.centroid));
 


@@ -1001,7 +1041,14 @@ BEGIN
   -- Full indexing
   {% if debug %}RAISE WARNING 'Using full index mode for % %', NEW.osm_type, NEW.osm_id;{% endif %}
   IF linked_place is not null THEN
   -- Full indexing
   {% if debug %}RAISE WARNING 'Using full index mode for % %', NEW.osm_type, NEW.osm_id;{% endif %}
   IF linked_place is not null THEN

-    SELECT * INTO location FROM placex WHERE place_id = linked_place;
+    -- Recompute the ranks here as the ones from the linked place might
+    -- have been shifted to accommodate surrounding boundaries.
+    SELECT place_id, osm_id, class, type, extratags,
+           centroid, geometry,
+           (compute_place_rank(country_code, osm_type, class, type, admin_level,
+                              (extratags->'capital') = 'yes', null)).*
+      INTO location
+      FROM placex WHERE place_id = linked_place;

 
     {% if debug %}RAISE WARNING 'Linked %', location;{% endif %}
 
 
     {% if debug %}RAISE WARNING 'Linked %', location;{% endif %}
 


@@ -1012,11 +1059,11 @@ BEGIN
         NEW.centroid := geom;
     END IF;
 
         NEW.centroid := geom;
     END IF;
 

-    {% if debug %}RAISE WARNING 'parent address: % rank address: %', parent_address_level, location.rank_address;{% endif %}
-    IF location.rank_address > parent_address_level
-       and location.rank_address < 26
+    {% if debug %}RAISE WARNING 'parent address: % rank address: %', parent_address_level, location.address_rank;{% endif %}
+    IF location.address_rank > parent_address_level
+       and location.address_rank < 26

     THEN
     THEN

-      NEW.rank_address := location.rank_address;
+      NEW.rank_address := location.address_rank;

     END IF;
 
     -- merge in extra tags
     END IF;
 
     -- merge in extra tags


@@ -1025,7 +1072,9 @@ BEGIN
                      || coalesce(NEW.extratags, ''::hstore);
 
     -- mark the linked place (excludes from search results)
                      || coalesce(NEW.extratags, ''::hstore);
 
     -- mark the linked place (excludes from search results)

-    UPDATE placex set linked_place_id = NEW.place_id
+    -- Force reindexing to remove any traces from the search indexes and
+    -- reset the address rank if necessary.
+    UPDATE placex set linked_place_id = NEW.place_id, indexed_status = 2

       WHERE place_id = location.place_id;
     -- ensure that those places are not found anymore
     {% if 'search_name' in db.tables %}
       WHERE place_id = location.place_id;
     -- ensure that those places are not found anymore
     {% if 'search_name' in db.tables %}


@@ -1058,13 +1107,22 @@ BEGIN
     END IF;
   END IF;
 
     END IF;
   END IF;
 

+  {% if not disable_diff_updates %}
+  IF OLD.rank_address != NEW.rank_address THEN
+    -- After a rank shift all addresses containing us must be updated.
+    UPDATE placex p SET indexed_status = 2 FROM place_addressline pa
+      WHERE pa.address_place_id = NEW.place_id and p.place_id = pa.place_id
+            and p.indexed_status = 0 and p.rank_address between 4 and 25;
+  END IF;
+  {% endif %}
+

   IF NEW.admin_level = 2
      AND NEW.class = 'boundary' AND NEW.type = 'administrative'
      AND NEW.country_code IS NOT NULL AND NEW.osm_type = 'R'
   THEN
     -- Update the list of country names.
     -- Only take the name from the largest area for the given country code
   IF NEW.admin_level = 2
      AND NEW.class = 'boundary' AND NEW.type = 'administrative'
      AND NEW.country_code IS NOT NULL AND NEW.osm_type = 'R'
   THEN
     -- Update the list of country names.
     -- Only take the name from the largest area for the given country code

-    -- in the hope that this is the authoritive one.
+    -- in the hope that this is the authoritative one.

     -- Also replace any old names so that all mapping mistakes can
     -- be fixed through regular OSM updates.
     FOR location IN
     -- Also replace any old names so that all mapping mistakes can
     -- be fixed through regular OSM updates.
     FOR location IN


@@ -1111,8 +1169,7 @@ BEGIN
 
   {% if debug %}RAISE WARNING 'RETURN insert_addresslines: %, %, %', NEW.parent_place_id, NEW.postcode, nameaddress_vector;{% endif %}
 
 
   {% if debug %}RAISE WARNING 'RETURN insert_addresslines: %, %, %', NEW.parent_place_id, NEW.postcode, nameaddress_vector;{% endif %}
 

-  NEW.postcode := coalesce(token_normalized_postcode(NEW.address->'postcode'),
-                           NEW.postcode);
+  NEW.postcode := coalesce(token_get_postcode(NEW.token_info), NEW.postcode);

 
   -- if we have a name add this to the name search table
   IF NEW.name IS NOT NULL THEN
 
   -- if we have a name add this to the name search table
   IF NEW.name IS NOT NULL THEN


@@ -1153,7 +1210,7 @@ BEGIN
     NEW.postcode := get_nearest_postcode(NEW.country_code, NEW.geometry);
   END IF;
 
     NEW.postcode := get_nearest_postcode(NEW.country_code, NEW.geometry);
   END IF;
 

-  {% if debug %}RAISE WARNING 'place update % % finsihed.', NEW.osm_type, NEW.osm_id;{% endif %}
+  {% if debug %}RAISE WARNING 'place update % % finished.', NEW.osm_type, NEW.osm_id;{% endif %}

 
   NEW.token_info := token_strip_info(NEW.token_info);
   RETURN NEW;
 
   NEW.token_info := token_strip_info(NEW.token_info);
   RETURN NEW;