Travis: documentation suggests we need to add postgresql-client package pre-startup

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

diff --git a/sql/functions/placex_triggers.sql b/sql/functions/placex_triggers.sql
index 842849f2601ccc4ea8520171ad45d1189549ce52..d247651ed490779cdb3b13a8534c483d884e618b 100644 (file)
--- a/sql/functions/placex_triggers.sql
+++ b/sql/functions/placex_triggers.sql
@@ -59,7 +59,6 @@ DECLARE
   parent_place_id BIGINT DEFAULT NULL;
   location RECORD;
   parent RECORD;
-  word_ids INTEGER[];
 BEGIN
     --DEBUG: RAISE WARNING 'finding street for % %', poi_osm_type, poi_osm_id;
 
@@ -85,29 +84,10 @@ BEGIN
       END LOOP;
     END LOOP;
 
-    -- Check for addr:street attributes
-    -- Note that addr:street links can only be indexed, once the street itself is indexed
-    word_ids := word_ids_from_name(addr_street);
-    IF word_ids is not null THEN
-      SELECT place_id
-        FROM getNearestNamedRoadFeature(poi_partition, near_centroid, word_ids)
-        INTO parent_place_id;
-      IF parent_place_id is not null THEN
-        --DEBUG: RAISE WARNING 'Get parent form addr:street: %', parent.place_id;
-        RETURN parent_place_id;
-      END IF;
-    END IF;
-
-    -- Check for addr:place attributes.
-    word_ids := word_ids_from_name(addr_place);
-    IF word_ids is not null THEN
-      SELECT place_id
-        FROM getNearestNamedPlaceFeature(poi_partition, near_centroid, word_ids)
-        INTO parent_place_id;
-      IF parent_place_id is not null THEN
-        --DEBUG: RAISE WARNING 'Get parent form addr:place: %', parent.place_id;
-        RETURN parent_place_id;
-      END IF;
+    parent_place_id := find_parent_for_address(addr_street, addr_place,
+                                               poi_partition, near_centroid);
+    IF parent_place_id is not null THEN
+      RETURN parent_place_id;
     END IF;
 
     IF poi_osm_type = 'N' THEN
@@ -245,7 +225,8 @@ BEGIN
       WHERE make_standard_name(name->'name') = bnd_name
         AND placex.rank_address = bnd.rank_address
         AND placex.osm_type = 'N'
-        AND st_covers(geometry, placex.geometry)
+        AND placex.rank_search < 26 -- needed to select the right index
+        AND _st_covers(bnd.geometry, placex.geometry)
     LOOP
       --DEBUG: RAISE WARNING 'Found matching place node %', linkedPlacex.osm_id;
       RETURN linked_placex;
@@ -255,7 +236,7 @@ BEGIN
   RETURN NULL;
 END;
 $$
-LANGUAGE plpgsql;
+LANGUAGE plpgsql STABLE;
 
 CREATE OR REPLACE FUNCTION placex_insert()
   RETURNS TRIGGER
@@ -433,7 +414,6 @@ CREATE OR REPLACE FUNCTION placex_update()
   RETURNS TRIGGER
   AS $$
 DECLARE
-
   near_centroid GEOMETRY;
 
   search_maxdistance FLOAT[];
@@ -441,17 +421,13 @@ DECLARE
   address_havelevel BOOLEAN[];
 
   i INTEGER;
-  iMax FLOAT;
   location RECORD;
-  way RECORD;
-  relation RECORD;
   relation_members TEXT[];
   addr_item RECORD;
   search_diameter FLOAT;
   search_prevdiameter FLOAT;
   search_maxrank INTEGER;
   address_maxrank INTEGER;
-  address_street_word_id INTEGER;
   address_street_word_ids INTEGER[];
   parent_place_id_rank BIGINT;
 
@@ -603,7 +579,10 @@ BEGIN
     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
@@ -752,15 +731,20 @@ BEGIN
   IF NEW.address IS NOT NULL THEN
     FOR addr_item IN SELECT * FROM each(NEW.address)
     LOOP
-      IF addr_item.key IN ('city', 'tiger:county', 'state', 'suburb', 'province', 'district', 'region', 'county', 'municipality', 'hamlet', 'village', 'subdistrict', 'town', 'neighbourhood', 'quarter', 'parish') THEN
-        address_street_word_id := get_name_id(make_standard_name(addr_item.value));
-        IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
-          isin_tokens := isin_tokens || address_street_word_id;
+      IF addr_item.key IN ('city', 'tiger:county', 'state', 'suburb', 'province',
+                           'district', 'region', 'county', 'municipality',
+                           'hamlet', 'village', 'subdistrict', 'town',
+                           'neighbourhood', 'quarter', 'parish')
+      THEN
+        address_street_word_ids := word_ids_from_name(addr_item.value);
+        IF address_street_word_ids is not null THEN
+          isin_tokens := array_merge(isin_tokens, address_street_word_ids);
         END IF;
         IF NOT %REVERSE-ONLY% THEN
-          address_street_word_id := get_word_id(make_standard_name(addr_item.value));
-          IF address_street_word_id IS NOT NULL THEN
-            nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+          address_street_word_ids := addr_ids_from_name(addr_item.value);
+          IF address_street_word_ids is not null THEN
+            nameaddress_vector := array_merge(nameaddress_vector,
+                                              address_street_word_ids);
           END IF;
         END IF;
       END IF;
@@ -769,16 +753,17 @@ BEGIN
         isin := regexp_split_to_array(addr_item.value, E'[;,]');
         IF array_upper(isin, 1) IS NOT NULL THEN
           FOR i IN 1..array_upper(isin, 1) LOOP
-            address_street_word_id := get_name_id(make_standard_name(isin[i]));
-            IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN
-              isin_tokens := isin_tokens || address_street_word_id;
+            address_street_word_ids := word_ids_from_name(isin[i]);
+            IF address_street_word_ids is not null THEN
+              isin_tokens := array_merge(isin_tokens, address_street_word_ids);
             END IF;
 
             -- merge word into address vector
             IF NOT %REVERSE-ONLY% THEN
-              address_street_word_id := get_word_id(make_standard_name(isin[i]));
-              IF address_street_word_id IS NOT NULL THEN
-                nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]);
+              address_street_word_ids := addr_ids_from_name(isin[i]);
+              IF address_street_word_ids is not null THEN
+                nameaddress_vector := array_merge(nameaddress_vector,
+                                                  address_street_word_ids);
               END IF;
             END IF;
           END LOOP;