Merge remote-tracking branch 'upstream/master'

[nominatim.git] / sql / functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index fcd895646c3ffcefc9404e0937d60f3fdab4a220..21d037170744bd59289066a4bc5a8b9a13f5fba6 100644 (file)
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -554,20 +554,6 @@ END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
 
 $$
 LANGUAGE plpgsql IMMUTABLE;
 

-CREATE OR REPLACE FUNCTION get_country_code(place geometry, in_country_code VARCHAR(2)) RETURNS TEXT
-  AS $$
-DECLARE
-  nearcountry RECORD;
-BEGIN
-  FOR nearcountry IN select country_code from country_name where country_code = lower(in_country_code)
-  LOOP
-    RETURN nearcountry.country_code;
-  END LOOP;
-  RETURN get_country_code(place);
-END;
-$$
-LANGUAGE plpgsql IMMUTABLE;
-

 CREATE OR REPLACE FUNCTION get_country_language_code(search_country_code VARCHAR(2)) RETURNS TEXT
   AS $$
 DECLARE
 CREATE OR REPLACE FUNCTION get_country_language_code(search_country_code VARCHAR(2)) RETURNS TEXT
   AS $$
 DECLARE


@@ -596,10 +582,9 @@ END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
 
 $$
 LANGUAGE plpgsql IMMUTABLE;
 

-CREATE OR REPLACE FUNCTION get_partition(place geometry, in_country_code VARCHAR(10)) RETURNS INTEGER
+CREATE OR REPLACE FUNCTION get_partition(in_country_code VARCHAR(10)) RETURNS INTEGER

   AS $$
 DECLARE
   AS $$
 DECLARE

-  place_centre GEOMETRY;

   nearcountry RECORD;
 BEGIN
   FOR nearcountry IN select partition from country_name where country_code = in_country_code
   nearcountry RECORD;
 BEGIN
   FOR nearcountry IN select partition from country_name where country_code = in_country_code


@@ -895,7 +880,7 @@ BEGIN
                 insert into placex (osm_type, osm_id, class, type, admin_level, housenumber, street, addr_place, isin, postcode,
                   country_code, parent_place_id, rank_address, rank_search, indexed_status, geometry)
                   values ('N',prevnode.osm_id, 'place', 'house', prevnode.admin_level, housenum, prevnode.street, prevnode.addr_place, prevnode.isin, coalesce(prevnode.postcode, defpostalcode),
                 insert into placex (osm_type, osm_id, class, type, admin_level, housenumber, street, addr_place, isin, postcode,
                   country_code, parent_place_id, rank_address, rank_search, indexed_status, geometry)
                   values ('N',prevnode.osm_id, 'place', 'house', prevnode.admin_level, housenum, prevnode.street, prevnode.addr_place, prevnode.isin, coalesce(prevnode.postcode, defpostalcode),

-                  prevnode.country_code, prevnode.parent_place_id, prevnode.rank_address, prevnode.rank_search, 1, ST_Line_Interpolate_Point(linegeo, (housenum::float-orginalstartnumber::float)/originalnumberrange::float));
+                  prevnode.country_code, prevnode.parent_place_id, prevnode.rank_address, prevnode.rank_search, 1, ST_LineInterpolatePoint(linegeo, (housenum::float-orginalstartnumber::float)/originalnumberrange::float));

                 newpoints := newpoints + 1;
 --RAISE WARNING 'interpolation number % % ',prevnode.place_id,housenum;
               END LOOP;
                 newpoints := newpoints + 1;
 --RAISE WARNING 'interpolation number % % ',prevnode.place_id,housenum;
               END LOOP;


@@ -970,9 +955,9 @@ BEGIN
   NEW.place_id := nextval('seq_place');
   NEW.indexed_status := 1; --STATUS_NEW
 
   NEW.place_id := nextval('seq_place');
   NEW.indexed_status := 1; --STATUS_NEW
 

-  NEW.calculated_country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
+  NEW.calculated_country_code := lower(get_country_code(NEW.geometry));

 
 

-  NEW.partition := get_partition(NEW.geometry, NEW.calculated_country_code);
+  NEW.partition := get_partition(NEW.calculated_country_code);

   NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
 
   -- copy 'name' to or from the default language (if there is a default language)
   NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
 
   -- copy 'name' to or from the default language (if there is a default language)


@@ -1083,18 +1068,6 @@ BEGIN
       ELSEIF NEW.type in ('village','hamlet','municipality','district','unincorporated_area','borough') THEN
         NEW.rank_search := 19;
         NEW.rank_address := 16;
       ELSEIF NEW.type in ('village','hamlet','municipality','district','unincorporated_area','borough') THEN
         NEW.rank_search := 19;
         NEW.rank_address := 16;

-      ELSEIF NEW.type in ('airport') AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
-        NEW.rank_search := 18;
-        NEW.rank_address := 17;
-      ELSEIF NEW.type in ('moor') AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN
-        NEW.rank_search := 17;
-        NEW.rank_address := 18;
-      ELSEIF NEW.type in ('moor') THEN
-        NEW.rank_search := 17;
-        NEW.rank_address := 0;
-      ELSEIF NEW.type in ('national_park') THEN
-        NEW.rank_search := 18;
-        NEW.rank_address := 18;

       ELSEIF NEW.type in ('suburb','croft','subdivision','isolated_dwelling') THEN
         NEW.rank_search := 20;
         NEW.rank_address := NEW.rank_search;
       ELSEIF NEW.type in ('suburb','croft','subdivision','isolated_dwelling') THEN
         NEW.rank_search := 20;
         NEW.rank_address := NEW.rank_search;


@@ -1105,12 +1078,9 @@ BEGIN
         IF (NEW.extratags -> 'locality') = 'townland' THEN
           NEW.rank_address := 20;
         END IF;
         IF (NEW.extratags -> 'locality') = 'townland' THEN
           NEW.rank_address := 20;
         END IF;

-      ELSEIF NEW.type in ('hall_of_residence','neighbourhood','housing_estate','nature_reserve') THEN
+      ELSEIF NEW.type in ('neighbourhood') THEN

         NEW.rank_search := 22;
         NEW.rank_address := 22;
         NEW.rank_search := 22;
         NEW.rank_address := 22;

-      ELSEIF NEW.type in ('airport','street') THEN
-        NEW.rank_search := 26;
-        NEW.rank_address := NEW.rank_search;

       ELSEIF NEW.type in ('house','building') THEN
         NEW.rank_search := 30;
         NEW.rank_address := NEW.rank_search;
       ELSEIF NEW.type in ('house','building') THEN
         NEW.rank_search := 30;
         NEW.rank_address := NEW.rank_search;


@@ -1152,7 +1122,8 @@ BEGIN
     ELSEIF NEW.class = 'waterway' AND NEW.name is NULL THEN
       RETURN NULL;
     ELSEIF NEW.class = 'waterway' THEN
     ELSEIF NEW.class = 'waterway' AND NEW.name is NULL THEN
       RETURN NULL;
     ELSEIF NEW.class = 'waterway' THEN

-      NEW.rank_address := 17;
+      NEW.rank_search := 17;
+      NEW.rank_address := 0;

     ELSEIF NEW.class = 'highway' AND NEW.osm_type != 'N' AND NEW.type in ('service','cycleway','path','footway','steps','bridleway','motorway_link','primary_link','trunk_link','secondary_link','tertiary_link') THEN
       NEW.rank_search := 27;
       NEW.rank_address := NEW.rank_search;
     ELSEIF NEW.class = 'highway' AND NEW.osm_type != 'N' AND NEW.type in ('service','cycleway','path','footway','steps','bridleway','motorway_link','primary_link','trunk_link','secondary_link','tertiary_link') THEN
       NEW.rank_search := 27;
       NEW.rank_address := NEW.rank_search;


@@ -1366,10 +1337,10 @@ BEGIN
       -- for countries, believe the mapped country code,
       -- so that we remain in the right partition if the boundaries
       -- suddenly expand.
       -- for countries, believe the mapped country code,
       -- so that we remain in the right partition if the boundaries
       -- suddenly expand.

-      NEW.partition := get_partition(place_centroid, lower(NEW.country_code));
+      NEW.partition := get_partition(lower(NEW.country_code));

       IF NEW.partition = 0 THEN
         NEW.calculated_country_code := lower(get_country_code(place_centroid));
       IF NEW.partition = 0 THEN
         NEW.calculated_country_code := lower(get_country_code(place_centroid));

-        NEW.partition := get_partition(place_centroid, NEW.calculated_country_code);
+        NEW.partition := get_partition(NEW.calculated_country_code);

       ELSE
         NEW.calculated_country_code := lower(NEW.country_code);
       END IF;
       ELSE
         NEW.calculated_country_code := lower(NEW.country_code);
       END IF;


@@ -1380,7 +1351,7 @@ BEGIN
       ELSE
         NEW.calculated_country_code := NULL;
       END IF;
       ELSE
         NEW.calculated_country_code := NULL;
       END IF;

-      NEW.partition := get_partition(place_centroid, NEW.calculated_country_code);
+      NEW.partition := get_partition(NEW.calculated_country_code);

     END IF;
     NEW.geometry_sector := geometry_sector(NEW.partition, place_centroid);
 
     END IF;
     NEW.geometry_sector := geometry_sector(NEW.partition, place_centroid);
 


@@ -1502,9 +1473,9 @@ BEGIN
             FOR relation IN SELECT place_id FROM placex
               WHERE ST_DWithin(location.geometry, placex.geometry, 0.001) and placex.rank_search = 26
                 and st_geometrytype(location.geometry) in ('ST_LineString')
             FOR relation IN SELECT place_id FROM placex
               WHERE ST_DWithin(location.geometry, placex.geometry, 0.001) and placex.rank_search = 26
                 and st_geometrytype(location.geometry) in ('ST_LineString')

-              ORDER BY (ST_distance(placex.geometry, ST_Line_Interpolate_Point(location.geometry,0))+
-                        ST_distance(placex.geometry, ST_Line_Interpolate_Point(location.geometry,0.5))+
-                        ST_distance(placex.geometry, ST_Line_Interpolate_Point(location.geometry,1))) ASC limit 1
+              ORDER BY (ST_distance(placex.geometry, ST_LineInterpolatePoint(location.geometry,0))+
+                        ST_distance(placex.geometry, ST_LineInterpolatePoint(location.geometry,0.5))+
+                        ST_distance(placex.geometry, ST_LineInterpolatePoint(location.geometry,1))) ASC limit 1

             LOOP
 --RAISE WARNING 'using nearest street to address interpolation line,0.001 %',relation;
               NEW.parent_place_id := relation.place_id;
             LOOP
 --RAISE WARNING 'using nearest street to address interpolation line,0.001 %',relation;
               NEW.parent_place_id := relation.place_id;


@@ -1971,7 +1942,7 @@ BEGIN
     -- mark everything linked to this place for re-indexing
     --DEBUG: RAISE WARNING 'placex_delete:03 % %',OLD.osm_type,OLD.osm_id;
     UPDATE placex set indexed_status = 2 from place_addressline where address_place_id = OLD.place_id 
     -- mark everything linked to this place for re-indexing
     --DEBUG: RAISE WARNING 'placex_delete:03 % %',OLD.osm_type,OLD.osm_id;
     UPDATE placex set indexed_status = 2 from place_addressline where address_place_id = OLD.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 place_addressline.isaddress;

 
     --DEBUG: RAISE WARNING 'placex_delete:04 % %',OLD.osm_type,OLD.osm_id;
     DELETE FROM place_addressline where address_place_id = OLD.place_id;
 
     --DEBUG: RAISE WARNING 'placex_delete:04 % %',OLD.osm_type,OLD.osm_id;
     DELETE FROM place_addressline where address_place_id = OLD.place_id;


@@ -2021,15 +1992,18 @@ LANGUAGE plpgsql;
 CREATE OR REPLACE FUNCTION place_delete() RETURNS TRIGGER
   AS $$
 DECLARE
 CREATE OR REPLACE FUNCTION place_delete() RETURNS TRIGGER
   AS $$
 DECLARE

-  placeid BIGINT;
+  has_rank BOOLEAN;

 BEGIN
 
   --DEBUG: RAISE WARNING 'delete: % % % %',OLD.osm_type,OLD.osm_id,OLD.class,OLD.type;
 
   -- deleting large polygons can have a massive effect on the system - require manual intervention to let them through
   IF st_area(OLD.geometry) > 2 and st_isvalid(OLD.geometry) THEN
 BEGIN
 
   --DEBUG: RAISE WARNING 'delete: % % % %',OLD.osm_type,OLD.osm_id,OLD.class,OLD.type;
 
   -- deleting large polygons can have a massive effect on the system - require manual intervention to let them through
   IF st_area(OLD.geometry) > 2 and st_isvalid(OLD.geometry) THEN

-    insert into import_polygon_delete values (OLD.osm_type,OLD.osm_id,OLD.class,OLD.type);
-    RETURN NULL;
+    SELECT bool_or(not (rank_address = 0 or rank_address > 26)) as ranked FROM placex WHERE osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type INTO has_rank;
+    IF has_rank THEN
+      insert into import_polygon_delete values (OLD.osm_type,OLD.osm_id,OLD.class,OLD.type);
+      RETURN NULL;
+    END IF;

   END IF;
 
   -- mark for delete
   END IF;
 
   -- mark for delete


@@ -2068,6 +2042,8 @@ BEGIN
 
   -- Just block these - lots and pointless
   IF NEW.class in ('landuse','natural') and NEW.name is null THEN
 
   -- Just block these - lots and pointless
   IF NEW.class in ('landuse','natural') and NEW.name is null THEN

+    -- if the name tag was removed, older versions might still be lurking in the place table
+    DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;

     RETURN null;
   END IF;
 
     RETURN null;
   END IF;
 


@@ -2113,8 +2089,19 @@ BEGIN
   DELETE from import_polygon_error where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
   DELETE from import_polygon_delete where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
 
   DELETE from import_polygon_error where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
   DELETE from import_polygon_delete where osm_type = NEW.osm_type and osm_id = NEW.osm_id;
 

-  -- To paraphrase, if there isn't an existing item, OR if the admin level has changed, OR if it is a major change in geometry
-  IF existingplacex.osm_type IS NULL THEN
+  -- To paraphrase, if there isn't an existing item, OR if the admin level has changed
+  IF existingplacex.osm_type IS NULL OR
+    (coalesce(existingplacex.admin_level, 15) != coalesce(NEW.admin_level, 15) AND existingplacex.class = 'boundary' AND existingplacex.type = 'administrative')
+  THEN
+
+    IF existingplacex.osm_type IS NOT NULL THEN
+      -- sanity check: ignore admin_level changes on places with too many active children
+      -- or we end up reindexing entire countries because somebody accidentally deleted admin_level
+      --LIMIT INDEXING: SELECT count(*) FROM (SELECT 'a' FROM placex , place_addressline where address_place_id = existingplacex.place_id and placex.place_id = place_addressline.place_id and indexed_status = 0 and place_addressline.isaddress LIMIT 100001) sub INTO i;
+      --LIMIT INDEXING: IF i > 100000 THEN
+      --LIMIT INDEXING:  RETURN null;
+      --LIMIT INDEXING: END IF;
+    END IF;

 
     IF existing.osm_type IS NOT NULL THEN
       -- pathological case caused by the triggerless copy into place during initial import
 
     IF existing.osm_type IS NOT NULL THEN
       -- pathological case caused by the triggerless copy into place during initial import


@@ -2274,6 +2261,16 @@ BEGIN
       geometry = NEW.geometry
       where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
 
       geometry = NEW.geometry
       where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type;
 

+    IF NEW.class in ('place','boundary') AND NEW.type in ('postcode','postal_code') THEN
+        IF NEW.postcode IS NULL THEN
+            -- postcode was deleted, no longer retain in placex
+            DELETE FROM placex where place_id = existingplacex.place_id;
+            RETURN NULL;
+        END IF;
+
+        NEW.name := hstore('ref', NEW.postcode);
+    END IF;
+

     update placex set 
       name = NEW.name,
       housenumber = NEW.housenumber,
     update placex set 
       name = NEW.name,
       housenumber = NEW.housenumber,


@@ -2510,7 +2507,7 @@ BEGIN
       CASE WHEN class = 'place' and type = 'postcode' THEN hstore('name', postcode) ELSE name END as name,
       class, type, admin_level, fromarea, isaddress,
       CASE WHEN address_place_id = for_place_id AND rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
       CASE WHEN class = 'place' and type = 'postcode' THEN hstore('name', postcode) ELSE name END as name,
       class, type, admin_level, fromarea, isaddress,
       CASE WHEN address_place_id = for_place_id AND rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,

-      distance,calculated_country_code
+      distance,calculated_country_code,postcode

       from place_addressline join placex on (address_place_id = placex.place_id) 
       where place_addressline.place_id = for_place_id 
       and (cached_rank_address > 0 AND cached_rank_address < searchrankaddress)
       from place_addressline join placex on (address_place_id = placex.place_id) 
       where place_addressline.place_id = for_place_id 
       and (cached_rank_address > 0 AND cached_rank_address < searchrankaddress)


@@ -2525,6 +2522,9 @@ BEGIN
     IF searchpostcode IS NOT NULL and location.type = 'postcode' THEN
       location.isaddress := FALSE;
     END IF;
     IF searchpostcode IS NOT NULL and location.type = 'postcode' THEN
       location.isaddress := FALSE;
     END IF;

+    IF searchpostcode IS NULL and location.isaddress and location.type != 'postcode' and location.postcode IS NOT NULL THEN
+      searchpostcode := location.postcode;
+    END IF;

     IF location.rank_address = 4 AND location.isaddress THEN
       hadcountry := true;
     END IF;
     IF location.rank_address = 4 AND location.isaddress THEN
       hadcountry := true;
     END IF;


@@ -2821,7 +2821,7 @@ DECLARE
 BEGIN
 
   place_centroid := ST_Centroid(pointgeo);
 BEGIN
 
   place_centroid := ST_Centroid(pointgeo);

-  out_partition := get_partition(place_centroid, in_countrycode);
+  out_partition := get_partition(in_countrycode);

   out_parent_place_id := null;
 
   address_street_word_id := get_name_id(make_standard_name(in_street));
   out_parent_place_id := null;
 
   address_street_word_id := get_name_id(make_standard_name(in_street));