X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/1fa9441113f9e9544cd1ce44061853d0f2dd64ee..7391e00bc3b590bcf43a08421ee39c5f50a1b4dc:/sql/functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index 6ad13892..27fc6ae9 100644
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -914,7 +914,7 @@ BEGIN
   NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
 
   -- copy 'name' to or from the default language (if there is a default language)
-  IF NEW.name is not null AND array_upper(%#NEW.name,1) > 1 THEN
+  IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
     default_language := get_country_language_code(NEW.country_code);
     IF default_language IS NOT NULL THEN
       IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
@@ -940,7 +940,50 @@ BEGIN
     NEW.rank_address := NEW.rank_search;
 
     -- By doing in postgres we have the country available to us - currently only used for postcode
-    IF NEW.class = 'place' THEN
+    IF NEW.class in ('place','boundary') AND NEW.type in ('postcode','postal_code') THEN
+
+        NEW.name := 'ref'=>NEW.postcode;
+
+        IF NEW.country_code = 'gb' THEN
+
+          IF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9][A-Z][A-Z])$' THEN
+            NEW.rank_search := 25;
+            NEW.rank_address := 5;
+          ELSEIF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9])$' THEN
+            NEW.rank_search := 23;
+            NEW.rank_address := 5;
+          ELSEIF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z])$' THEN
+            NEW.rank_search := 21;
+            NEW.rank_address := 5;
+          END IF;
+
+        ELSEIF NEW.country_code = 'de' THEN
+
+          IF NEW.postcode ~ '^([0-9]{5})$' THEN
+            NEW.rank_search := 21;
+            NEW.rank_address := 11;
+          END IF;
+
+        ELSE
+          -- Guess at the postcode format and coverage (!)
+          IF upper(NEW.postcode) ~ '^[A-Z0-9]{1,5}$' THEN -- Probably too short to be very local
+            NEW.rank_search := 21;
+            NEW.rank_address := 11;
+          ELSE
+            -- Does it look splitable into and area and local code?
+            postcode := substring(upper(NEW.postcode) from '^([- :A-Z0-9]+)([- :][A-Z0-9]+)$');
+
+            IF postcode IS NOT NULL THEN
+              NEW.rank_search := 25;
+              NEW.rank_address := 11;
+            ELSEIF NEW.postcode ~ '^[- :A-Z0-9]{6,}$' THEN
+              NEW.rank_search := 21;
+              NEW.rank_address := 11;
+            END IF;
+          END IF;
+        END IF;
+
+    ELSEIF NEW.class = 'place' THEN
       IF NEW.type in ('continent') THEN
         NEW.rank_search := 2;
         NEW.rank_address := NEW.rank_search;
@@ -992,49 +1035,6 @@ BEGIN
       ELSEIF NEW.type in ('hall_of_residence','neighbourhood','housing_estate','nature_reserve') THEN
         NEW.rank_search := 22;
         NEW.rank_address := 22;
-      ELSEIF NEW.type in ('postcode') THEN
-
-        NEW.name := 'ref'=>NEW.postcode;
-
-        IF NEW.country_code = 'gb' THEN
-
-          IF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9][A-Z][A-Z])$' THEN
-            NEW.rank_search := 25;
-            NEW.rank_address := 5;
-          ELSEIF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z]? [0-9])$' THEN
-            NEW.rank_search := 23;
-            NEW.rank_address := 5;
-          ELSEIF NEW.postcode ~ '^([A-Z][A-Z]?[0-9][0-9A-Z])$' THEN
-            NEW.rank_search := 21;
-            NEW.rank_address := 5;
-          END IF;
-
-        ELSEIF NEW.country_code = 'de' THEN
-
-          IF NEW.postcode ~ '^([0-9]{5})$' THEN
-            NEW.rank_search := 21;
-            NEW.rank_address := 11;
-          END IF;
-
-        ELSE
-          -- Guess at the postcode format and coverage (!)
-          IF upper(NEW.postcode) ~ '^[A-Z0-9]{1,5}$' THEN -- Probably too short to be very local
-            NEW.rank_search := 21;
-            NEW.rank_address := 11;
-          ELSE
-            -- Does it look splitable into and area and local code?
-            postcode := substring(upper(NEW.postcode) from '^([- :A-Z0-9]+)([- :][A-Z0-9]+)$');
-
-            IF postcode IS NOT NULL THEN
-              NEW.rank_search := 25;
-              NEW.rank_address := 11;
-            ELSEIF NEW.postcode ~ '^[- :A-Z0-9]{6,}$' THEN
-              NEW.rank_search := 21;
-              NEW.rank_address := 11;
-            END IF;
-          END IF;
-        END IF;
-
       ELSEIF NEW.type in ('airport','street') THEN
         NEW.rank_search := 26;
         NEW.rank_address := NEW.rank_search;
@@ -1115,11 +1115,12 @@ BEGIN
     IF st_area(NEW.geometry) < 1 THEN
       -- mark items within the geometry for re-indexing
 --    RAISE WARNING 'placex poly insert: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
--- work around bug in postgis
+
+      -- work around bug in postgis, this may have been fixed in 2.0.0 (see http://trac.osgeo.org/postgis/ticket/547)
       update placex set indexed_status = 2 where (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) 
-       AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point';
+       AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) = 'ST_Point' and (rank_search < 28 or name is not null);
       update placex set indexed_status = 2 where (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry)) 
-       AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point';
+       AND rank_search > NEW.rank_search and indexed_status = 0 and ST_geometrytype(placex.geometry) != 'ST_Point' and (rank_search < 28 or name is not null);
     END IF;
   ELSE
     -- mark nearby items for re-indexing, where 'nearby' depends on the features rank_search and is a complete guess :(
@@ -1144,7 +1145,7 @@ BEGIN
     END IF;
     IF diameter > 0 THEN
 --      RAISE WARNING 'placex point insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,diameter;
-      update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter);
+      update placex set indexed_status = 2 where indexed_status = 0 and rank_search > NEW.rank_search and ST_DWithin(placex.geometry, NEW.geometry, diameter) and (rank_search < 28 or name is not null);
     END IF;
 
   END IF;
@@ -1203,6 +1204,7 @@ DECLARE
 
   tagpairid INTEGER;
 
+  default_language TEXT;
   name_vector INTEGER[];
   nameaddress_vector INTEGER[];
 
@@ -1256,6 +1258,19 @@ BEGIN
     -- cheaper but less acurate
     place_centroid := ST_Centroid(NEW.geometry);
 
+    -- Thought this wasn't needed but when we add new languages to the country_name table
+    -- we need to update the existing names
+    IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN
+      default_language := get_country_language_code(NEW.country_code);
+      IF default_language IS NOT NULL THEN
+        IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN
+          NEW.name := NEW.name || (('name:'||default_language) => (NEW.name -> 'name'));
+        ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN
+          NEW.name := NEW.name || ('name' => (NEW.name -> 'name:'||default_language));
+        END IF;
+      END IF;
+    END IF;
+
     -- Initialise the name vector using our name
     name_vector := make_keywords(NEW.name);
     nameaddress_vector := '{}'::int[];
@@ -1792,12 +1807,12 @@ BEGIN
       update placex set indexed_status = 2 where indexed_status = 0 and 
           (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
           AND NOT (ST_Contains(existinggeometry, placex.geometry) OR ST_Intersects(existinggeometry, placex.geometry))
-          AND rank_search > existingplacex.rank_search;
+          AND rank_search > existingplacex.rank_search AND (rank_search < 28 or name is not null);
 
       update placex set indexed_status = 2 where indexed_status = 0 and 
           (ST_Contains(existinggeometry, placex.geometry) OR ST_Intersects(existinggeometry, placex.geometry))
           AND NOT (ST_Contains(NEW.geometry, placex.geometry) OR ST_Intersects(NEW.geometry, placex.geometry))
-          AND rank_search > existingplacex.rank_search;
+          AND rank_search > existingplacex.rank_search AND (rank_search < 28 or name is not null);
 
     END IF;
 
@@ -1817,7 +1832,8 @@ BEGIN
 
       IF st_area(NEW.geometry) < 0.5 THEN
         UPDATE placex set indexed_status = 2 from place_addressline where address_place_id = existingplacex.place_id 
-          and placex.place_id = place_addressline.place_id and indexed_status = 0;
+          and placex.place_id = place_addressline.place_id and indexed_status = 0
+          and (rank_search < 28 or name is not null);
       END IF;
 
     END IF;
@@ -2393,13 +2409,6 @@ END;
 $$
 LANGUAGE plpgsql;
 
-CREATE AGGREGATE array_agg(INT[])
-(
-    sfunc = array_cat,
-    stype = INT[],
-    initcond = '{}'
-);
-
 CREATE OR REPLACE FUNCTION tigger_create_interpolation(linegeo GEOMETRY, in_startnumber INTEGER, 
   in_endnumber INTEGER, interpolationtype TEXT, 
   in_street TEXT, in_isin TEXT, in_postcode TEXT) RETURNS INTEGER
@@ -2414,8 +2423,8 @@ DECLARE
   numberrange INTEGER;
   rangestartnumber INTEGER;
   place_centroid GEOMETRY;
-  partition INTEGER;
-  parent_place_id BIGINT;
+  out_partition INTEGER;
+  out_parent_place_id BIGINT;
   location RECORD;
   address_street_word_id INTEGER;  
 
@@ -2452,32 +2461,32 @@ BEGIN
   END IF;
 
   place_centroid := ST_Centroid(linegeo);
-  partition := get_partition(place_centroid, 'us');
-  parent_place_id := null;
+  out_partition := get_partition(place_centroid, 'us');
+  out_parent_place_id := null;
 
   address_street_word_id := get_name_id(make_standard_name(in_street));
   IF address_street_word_id IS NOT NULL THEN
-    FOR location IN SELECT * from getNearestNamedRoadFeature(partition, place_centroid, address_street_word_id) LOOP
-      parent_place_id := location.place_id;
+    FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, address_street_word_id) LOOP
+      out_parent_place_id := location.place_id;
     END LOOP;
   END IF;
 
-  IF parent_place_id IS NULL THEN
-    FOR location IN SELECT place_id FROM getNearestParellelRoadFeature(partition, linegeo) LOOP
-      parent_place_id := location.place_id;
+  IF out_parent_place_id IS NULL THEN
+    FOR location IN SELECT place_id FROM getNearestParellelRoadFeature(out_partition, linegeo) LOOP
+      out_parent_place_id := location.place_id;
     END LOOP;    
   END IF;
 
-  IF parent_place_id IS NULL THEN
-    FOR location IN SELECT place_id FROM getNearestRoadFeature(partition, place_centroid) LOOP
-      parent_place_id := location.place_id;
+  IF out_parent_place_id IS NULL THEN
+    FOR location IN SELECT place_id FROM getNearestRoadFeature(out_partition, place_centroid) LOOP
+      out_parent_place_id := location.place_id;
     END LOOP;    
   END IF;
 
   newpoints := 0;
   FOR housenum IN startnumber..endnumber BY stepsize LOOP
     insert into location_property_tiger (place_id, partition, parent_place_id, housenumber, postcode, centroid)
-    values (nextval('seq_place'), partition, parent_place_id, housenum, in_postcode,
+    values (nextval('seq_place'), out_partition, out_parent_place_id, housenum, in_postcode,
       ST_Line_Interpolate_Point(linegeo, (housenum::float-rangestartnumber::float)/numberrange::float));
     newpoints := newpoints + 1;
   END LOOP;
@@ -2494,7 +2503,7 @@ DECLARE
 
   newpoints INTEGER;
   place_centroid GEOMETRY;
-  partition INTEGER;
+  out_partition INTEGER;
   out_parent_place_id BIGINT;
   location RECORD;
   address_street_word_id INTEGER;  
@@ -2503,18 +2512,18 @@ DECLARE
 BEGIN
 
   place_centroid := ST_Centroid(pointgeo);
-  partition := get_partition(place_centroid, in_countrycode);
+  out_partition := get_partition(place_centroid, in_countrycode);
   out_parent_place_id := null;
 
   address_street_word_id := get_name_id(make_standard_name(in_street));
   IF address_street_word_id IS NOT NULL THEN
-    FOR location IN SELECT * from getNearestNamedRoadFeature(partition, place_centroid, address_street_word_id) LOOP
+    FOR location IN SELECT * from getNearestNamedRoadFeature(out_partition, place_centroid, address_street_word_id) LOOP
       out_parent_place_id := location.place_id;
     END LOOP;
   END IF;
 
   IF out_parent_place_id IS NULL THEN
-    FOR location IN SELECT place_id FROM getNearestRoadFeature(partition, place_centroid) LOOP
+    FOR location IN SELECT place_id FROM getNearestRoadFeature(out_partition, place_centroid) LOOP
       out_parent_place_id := location.place_id;
     END LOOP;    
   END IF;
@@ -2524,12 +2533,12 @@ BEGIN
     SELECT postcode from placex where place_id = out_parent_place_id INTO out_postcode;
   END IF;
   IF out_postcode IS NULL THEN
-    out_postcode := getNearestPostcode(partition, place_centroid);
+    out_postcode := getNearestPostcode(out_partition, place_centroid);
   END IF;
 
   newpoints := 0;
   insert into location_property_aux (place_id, partition, parent_place_id, housenumber, postcode, centroid)
-    values (nextval('seq_place'), partition, out_parent_place_id, in_housenumber, out_postcode, place_centroid);
+    values (nextval('seq_place'), out_partition, out_parent_place_id, in_housenumber, out_postcode, place_centroid);
   newpoints := newpoints + 1;
 
   RETURN newpoints;
