X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/6cbef84cad0d08221d4a386cb5c3db19447ddede..1b1ed820c3af58de7281607a8b82f6a5dfe37319:/lib-sql/functions/placex_triggers.sql

diff --git a/lib-sql/functions/placex_triggers.sql b/lib-sql/functions/placex_triggers.sql
index 6998224e..dba301cb 100644
--- a/lib-sql/functions/placex_triggers.sql
+++ b/lib-sql/functions/placex_triggers.sql
@@ -1,5 +1,52 @@
 -- Trigger functions for the placex table.
 
+-- Retrieve the data needed by the indexer for updating the place.
+--
+-- Return parameters:
+--  name            list of names
+--  address         list of address tags, either from the object or a surrounding
+--                  building
+--  country_feature If the place is a country feature, this contains the
+--                  country code, otherwise it is null.
+CREATE OR REPLACE FUNCTION placex_prepare_update(p placex,
+                                                 OUT name HSTORE,
+                                                 OUT address HSTORE,
+                                                 OUT country_feature VARCHAR)
+  AS $$
+BEGIN
+  -- For POI nodes, check if the address should be derived from a surrounding
+  -- building.
+  IF p.rank_search < 30 OR p.osm_type != 'N' OR p.address is not null THEN
+    RAISE WARNING 'self address for % %', p.osm_type, p.osm_id;
+    address := p.address;
+  ELSE
+    -- The additional && condition works around the misguided query
+    -- planner of postgis 3.0.
+    SELECT placex.address || hstore('_inherited', '') INTO address
+      FROM placex
+     WHERE ST_Covers(geometry, p.centroid)
+           and geometry && p.centroid
+           and placex.address is not null
+           and (placex.address ? 'housenumber' or placex.address ? 'street' or placex.address ? 'place')
+           and rank_search = 30 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
+     LIMIT 1;
+    RAISE WARNING 'other address for % %: % (%)', p.osm_type, p.osm_id, address, p.centroid;
+  END IF;
+
+  address := address - '_unlisted_place'::TEXT;
+  name := p.name;
+
+  country_feature := CASE WHEN p.admin_level = 2
+                               and p.class = 'boundary' and p.type = 'administrative'
+                               and p.osm_type = 'R'
+                          THEN p.country_code
+                          ELSE null
+                     END;
+END;
+$$
+LANGUAGE plpgsql STABLE;
+
+
 -- Find the parent road of a POI.
 --
 -- \returns Place ID of parent object or NULL if none
@@ -169,7 +216,7 @@ BEGIN
   END IF;
 
   IF bnd.name ? 'name' THEN
-    bnd_name := make_standard_name(bnd.name->'name');
+    bnd_name := lower(bnd.name->'name');
     IF bnd_name = '' THEN
       bnd_name := NULL;
     END IF;
@@ -180,12 +227,14 @@ BEGIN
   IF bnd.extratags ? 'place' and bnd_name is not null THEN
     FOR linked_placex IN
       SELECT * FROM placex
-      WHERE make_standard_name(name->'name') = bnd_name
+      WHERE (position(lower(name->'name') in bnd_name) > 0
+             OR position(bnd_name in lower(name->'name')) > 0)
         AND placex.class = 'place' AND placex.type = bnd.extratags->'place'
         AND placex.osm_type = 'N'
         AND placex.linked_place_id is null
         AND placex.rank_search < 26 -- needed to select the right index
-        AND _st_covers(bnd.geometry, placex.geometry)
+        AND placex.type != 'postcode'
+        AND ST_Covers(bnd.geometry, placex.geometry)
     LOOP
       {% if debug %}RAISE WARNING 'Found type-matching place node %', linked_placex.osm_id;{% endif %}
       RETURN linked_placex;
@@ -201,7 +250,7 @@ BEGIN
         AND placex.linked_place_id is null
         AND placex.rank_search < 26
         AND _st_covers(bnd.geometry, placex.geometry)
-      ORDER BY make_standard_name(name->'name') = bnd_name desc
+      ORDER BY lower(name->'name') = bnd_name desc
     LOOP
       {% if debug %}RAISE WARNING 'Found wikidata-matching place node %', linked_placex.osm_id;{% endif %}
       RETURN linked_placex;
@@ -213,7 +262,7 @@ BEGIN
     {% if debug %}RAISE WARNING 'Looking for nodes with matching names';{% endif %}
     FOR linked_placex IN
       SELECT placex.* from placex
-      WHERE make_standard_name(name->'name') = bnd_name
+      WHERE lower(name->'name') = bnd_name
         AND ((bnd.rank_address > 0
               and bnd.rank_address = (compute_place_rank(placex.country_code,
                                                          'N', placex.class,
@@ -221,9 +270,11 @@ BEGIN
                                                          false, placex.postcode)).address_rank)
              OR (bnd.rank_address = 0 and placex.rank_search = bnd.rank_search))
         AND placex.osm_type = 'N'
+        AND placex.class = 'place'
         AND placex.linked_place_id is null
         AND placex.rank_search < 26 -- needed to select the right index
-        AND _st_covers(bnd.geometry, placex.geometry)
+        AND placex.type != 'postcode'
+        AND ST_Covers(bnd.geometry, placex.geometry)
     LOOP
       {% if debug %}RAISE WARNING 'Found matching place node %', linked_placex.osm_id;{% endif %}
       RETURN linked_placex;
@@ -393,10 +444,11 @@ BEGIN
   NEW.place_id := nextval('seq_place');
   NEW.indexed_status := 1; --STATUS_NEW
 
-  NEW.country_code := lower(get_country_code(NEW.geometry));
+  NEW.centroid := ST_PointOnSurface(NEW.geometry);
+  NEW.country_code := lower(get_country_code(NEW.centroid));
 
   NEW.partition := get_partition(NEW.country_code);
-  NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
+  NEW.geometry_sector := geometry_sector(NEW.partition, NEW.centroid);
 
   IF NEW.osm_type = 'X' THEN
     -- E'X'ternal records should already be in the right format so do nothing
@@ -527,8 +579,6 @@ DECLARE
   nameaddress_vector INTEGER[];
   addr_nameaddress_vector INTEGER[];
 
-  inherited_address HSTORE;
-
   linked_node_id BIGINT;
   linked_importance FLOAT;
   linked_wikipedia TEXT;
@@ -562,7 +612,6 @@ BEGIN
   -- update not necessary for osmline, cause linked_place_id does not exist
 
   NEW.extratags := NEW.extratags - 'linked_place'::TEXT;
-  NEW.address := NEW.address - '_unlisted_place'::TEXT;
 
   IF NEW.linked_place_id is not null THEN
     {% if debug %}RAISE WARNING 'place already linked to %', NEW.linked_place_id;{% endif %}
@@ -746,27 +795,6 @@ BEGIN
     {% if debug %}RAISE WARNING 'finding street for % %', NEW.osm_type, NEW.osm_id;{% endif %}
     NEW.parent_place_id := null;
 
-    -- if we have a POI and there is no address information,
-    -- see if we can get it from a surrounding building
-    inherited_address := ''::HSTORE;
-    IF NEW.osm_type = 'N' AND addr_street IS NULL AND addr_place IS NULL
-       AND NEW.housenumber IS NULL THEN
-      FOR location IN
-        -- The additional && condition works around the misguided query
-        -- planner of postgis 3.0.
-        SELECT address from placex where ST_Covers(geometry, NEW.centroid)
-            and geometry && NEW.centroid
-            and (address ? 'housenumber' or address ? 'street' or address ? 'place')
-            and rank_search > 28 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
-            limit 1
-      LOOP
-        NEW.housenumber := location.address->'housenumber';
-        addr_street := location.address->'street';
-        addr_place := location.address->'place';
-        inherited_address := location.address;
-      END LOOP;
-    END IF;
-
     -- We have to find our parent road.
     NEW.parent_place_id := find_parent_for_poi(NEW.osm_type, NEW.osm_id,
                                                NEW.partition,
@@ -819,12 +847,12 @@ BEGIN
 
       {% if not db.reverse_only %}
       IF array_length(name_vector, 1) is not NULL
-         OR inherited_address is not NULL OR NEW.address is not NULL
+         OR NEW.address is not NULL
       THEN
         SELECT * INTO name_vector, nameaddress_vector
           FROM create_poi_search_terms(NEW.place_id,
                                        NEW.partition, NEW.parent_place_id,
-                                       inherited_address || NEW.address,
+                                       NEW.address,
                                        NEW.country_code, NEW.housenumber,
                                        name_vector, NEW.centroid);
 
@@ -840,6 +868,16 @@ BEGIN
       END IF;
       {% endif %}
 
+      -- If the address was inherited from a surrounding building,
+      -- do not add it permanently to the table.
+      IF NEW.address ? '_inherited' THEN
+        IF NEW.address ? '_unlisted_place' THEN
+          NEW.address := hstore('_unlisted_place', NEW.address->'_unlisted_place');
+        ELSE
+          NEW.address := null;
+        END IF;
+      END IF;
+
       RETURN NEW;
     END IF;
 
