X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/3ea1b35b3243e4e2d061a207460b009883522d24..89a365787f2e14d531dfeeeb0bf3bcb1d2872af8:/sql/functions.sql

diff --git a/sql/functions.sql b/sql/functions.sql
index 157c1525..5ce5d5dd 100644
--- a/sql/functions.sql
+++ b/sql/functions.sql
@@ -537,7 +537,7 @@ END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
 
-CREATE OR REPLACE FUNCTION delete_location(OLD_place_id INTEGER) RETURNS BOOLEAN
+CREATE OR REPLACE FUNCTION delete_location(OLD_place_id BIGINT) RETURNS BOOLEAN
   AS $$
 DECLARE
 BEGIN
@@ -549,7 +549,7 @@ $$
 LANGUAGE plpgsql;
 
 CREATE OR REPLACE FUNCTION add_location(
-    place_id INTEGER,
+    place_id BIGINT,
     country_code varchar(2),
     partition INTEGER,
     keywords INTEGER[],
@@ -654,7 +654,7 @@ LANGUAGE plpgsql;
 
 CREATE OR REPLACE FUNCTION update_location(
     partition INTEGER,
-    place_id INTEGER,
+    place_id BIGINT,
     place_country_code varchar(2),
     name hstore,
     rank_search INTEGER,
@@ -673,7 +673,7 @@ END;
 $$
 LANGUAGE plpgsql;
 
-CREATE OR REPLACE FUNCTION search_name_add_words(parent_place_id INTEGER, to_add INTEGER[])
+CREATE OR REPLACE FUNCTION search_name_add_words(parent_place_id BIGINT, to_add INTEGER[])
   RETURNS BOOLEAN
   AS $$
 DECLARE
@@ -704,7 +704,7 @@ END;
 $$
 LANGUAGE plpgsql;
 
-CREATE OR REPLACE FUNCTION update_location_nameonly(partition INTEGER, OLD_place_id INTEGER, name hstore) RETURNS BOOLEAN
+CREATE OR REPLACE FUNCTION update_location_nameonly(partition INTEGER, OLD_place_id BIGINT, name hstore) RETURNS BOOLEAN
   AS $$
 DECLARE
   newkeywords INTEGER[];
@@ -753,7 +753,7 @@ DECLARE
   originalnumberrange INTEGER;
   housenum INTEGER;
   linegeo GEOMETRY;
-  search_place_id INTEGER;
+  search_place_id BIGINT;
   defpostalcode TEXT;
 
   havefirstpoint BOOLEAN;
@@ -1176,7 +1176,7 @@ DECLARE
   search_maxrank INTEGER;
   address_maxrank INTEGER;
   address_street_word_id INTEGER;
-  parent_place_id_rank INTEGER;
+  parent_place_id_rank BIGINT;
   
   isin TEXT[];
   isin_tokens INT[];
@@ -1220,7 +1220,9 @@ BEGIN
     DELETE FROM place_boundingbox where place_id = NEW.place_id;
     result := deleteRoad(NEW.partition, NEW.place_id);
     result := deleteLocationArea(NEW.partition, NEW.place_id);
-  
+
+    -- reclaculate country and partition (should probably have a country_code and calculated_country_code as seperate fields)
+    SELECT country_code from place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO NEW.country_code;
     NEW.country_code := lower(get_country_code(NEW.geometry, NEW.country_code));
     NEW.partition := get_partition(NEW.geometry, NEW.country_code);
     NEW.geometry_sector := geometry_sector(NEW.partition, NEW.geometry);
@@ -1377,13 +1379,13 @@ BEGIN
       IF NEW.parent_place_id IS NOT NULL THEN
 
         -- Add the street to the address as zero distance to force to front of list
-        INSERT INTO place_addressline VALUES (NEW.place_id, NEW.parent_place_id, true, true, 0, 26);
+--        INSERT INTO place_addressline VALUES (NEW.place_id, NEW.parent_place_id, true, true, 0, 26);
         address_havelevel[26] := true;
 
         -- Import address details from parent, reclculating distance in process
-        INSERT INTO place_addressline select NEW.place_id, x.address_place_id, x.fromarea, x.isaddress, ST_distance(NEW.geometry, placex.geometry), placex.rank_address
-          from place_addressline as x join placex on (address_place_id = placex.place_id)
-          where x.place_id = NEW.parent_place_id and x.address_place_id != NEW.parent_place_id;
+--        INSERT INTO place_addressline select NEW.place_id, x.address_place_id, x.fromarea, x.isaddress, ST_distance(NEW.geometry, placex.geometry), placex.rank_address
+--          from place_addressline as x join placex on (address_place_id = placex.place_id)
+--          where x.place_id = NEW.parent_place_id and x.address_place_id != NEW.parent_place_id;
 
         -- Get the details of the parent road
         select * from search_name where place_id = NEW.parent_place_id INTO location;
@@ -1405,7 +1407,7 @@ BEGIN
           result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry);
         END IF;
 
-        result := insertSearchName(NEW.partition, NEW.place_id, NEW.country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, place_centroid);
+        result := insertSearchName(NEW.partition, NEW.place_id, NEW.country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid);
 
         return NEW;
       END IF;
@@ -1448,7 +1450,7 @@ BEGIN
     -- Process area matches
     location_rank_search := 100;
     location_distance := 0;
---RAISE WARNING '  getNearFeatures(%,%,%,%)',NEW.partition, place_centroid, search_maxrank, isin_tokens;
+--RAISE WARNING '  getNearFeatures(%,''%'',%,''%'')',NEW.partition, place_centroid, search_maxrank, isin_tokens;
     FOR location IN SELECT * from getNearFeatures(NEW.partition, place_centroid, search_maxrank, isin_tokens) LOOP
 
 --RAISE WARNING '  AREA: %',location;
@@ -1458,13 +1460,15 @@ BEGIN
         location_distance := location.distance * 1.5;
       END IF;
 
-      IF location.distance < location_distance THEN
+      IF location.distance < location_distance OR NOT location.isguess THEN
 
         -- Add it to the list of search terms
         nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
         INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, true, NOT address_havelevel[location.rank_address], location.distance, location.rank_address); 
         address_havelevel[location.rank_address] := true;
 
+--RAISE WARNING '  Terms: (%) %',location, nameaddress_vector;
+
         IF location.rank_address > parent_place_id_rank THEN
           NEW.parent_place_id = location.place_id;
           parent_place_id_rank = location.rank_address;
@@ -1477,9 +1481,12 @@ BEGIN
     -- try using the isin value to find parent places
     IF array_upper(isin_tokens, 1) IS NOT NULL THEN
       FOR i IN 1..array_upper(isin_tokens, 1) LOOP
---RAISE WARNING '  ISIN: % % % %',NEW.partition, place_centroid, search_maxrank, isin_tokens[i];
+--RAISE WARNING '  getNearestNamedFeature: % % % %',NEW.partition, place_centroid, search_maxrank, isin_tokens[i];
 
         FOR location IN SELECT * from getNearestNamedFeature(NEW.partition, place_centroid, search_maxrank, isin_tokens[i]) LOOP
+
+--RAISE WARNING '  ISIN: %',location;
+
           nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]);
           INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address);
           address_havelevel[location.rank_address] := true;
@@ -1530,7 +1537,7 @@ BEGIN
         result := insertLocationRoad(NEW.partition, NEW.place_id, NEW.country_code, NEW.geometry);
       END IF;
 
-      result := insertSearchName(NEW.partition, NEW.place_id, NEW.country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, place_centroid);
+      result := insertSearchName(NEW.partition, NEW.place_id, NEW.country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid);
 
 --      INSERT INTO search_name values (NEW.place_id, NEW.rank_search, NEW.rank_search, 0, NEW.country_code, name_vector, nameaddress_vector, place_centroid);
     END IF;
@@ -1581,11 +1588,17 @@ LANGUAGE plpgsql;
 CREATE OR REPLACE FUNCTION place_delete() RETURNS TRIGGER
   AS $$
 DECLARE
-  placeid INTEGER;
+  placeid BIGINT;
 BEGIN
 
 --  RAISE WARNING 'delete: % % % %',OLD.osm_type,OLD.osm_id,OLD.class,OLD.type;
 
+  -- deleting large polygons can have a massive effect ont he system - require manual intervention to let them through
+  IF st_area(OLD.geometry) > 2 THEN
+    insert into import_polygon_delete values (OLD.osm_type,OLD.osm_id,OLD.class,OLD.type);
+    RETURN NULL;
+  END IF;
+
   -- mark for delete
   UPDATE placex set indexed_status = 100 where osm_type = OLD.osm_type and osm_id = OLD.osm_id and class = OLD.class and type = OLD.type;
 
@@ -1603,7 +1616,7 @@ DECLARE
   existing RECORD;
   existingplacex RECORD;
   existinggeometry GEOMETRY;
-  existingplace_id INTEGER;
+  existingplace_id BIGINT;
   result BOOLEAN;
   partition INTEGER;
 BEGIN
@@ -1642,11 +1655,11 @@ BEGIN
   select * from placex where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type = NEW.type INTO existingplacex;
 
   -- Handle a place changing type by removing the old data
-  -- My generated 'place' types are causing havok because they overlap with real tags
+  -- My generated 'place' types are causing havok because they overlap with real keys
   -- TODO: move them to their own special purpose key/class to avoid collisions
---  IF existing.osm_type IS NULL AND (NEW.type not in ('postcode','house','houses')) THEN
---    DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type not in ('postcode','house','houses');
---  END IF;
+  IF existing.osm_type IS NULL AND (NEW.type not in ('postcode','house','houses')) THEN
+    DELETE FROM place where osm_type = NEW.osm_type and osm_id = NEW.osm_id and class = NEW.class and type not in ('postcode','house','houses');
+  END IF;
 
 --  RAISE WARNING 'Existing: %',existing.place_id;
 
@@ -1662,6 +1675,7 @@ BEGIN
   END IF;
 
   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 existing.osm_type IS NULL 
@@ -1893,7 +1907,7 @@ END;
 $$
 LANGUAGE plpgsql IMMUTABLE;
 
-CREATE OR REPLACE FUNCTION get_address_postcode(for_place_id INTEGER) RETURNS TEXT
+CREATE OR REPLACE FUNCTION get_address_postcode(for_place_id BIGINT) RETURNS TEXT
   AS $$
 DECLARE
   result TEXT[];
@@ -1932,7 +1946,7 @@ END;
 $$
 LANGUAGE plpgsql;
 
-CREATE OR REPLACE FUNCTION get_address_by_language(for_place_id INTEGER, languagepref TEXT[]) RETURNS TEXT
+CREATE OR REPLACE FUNCTION get_address_by_language(for_place_id BIGINT, languagepref TEXT[]) RETURNS TEXT
   AS $$
 DECLARE
   result TEXT[];
@@ -1959,7 +1973,7 @@ LANGUAGE plpgsql;
 
 DROP TYPE addressline CASCADE;
 create type addressline as (
-  place_id INTEGER,
+  place_id BIGINT,
   osm_type CHAR(1),
   osm_id INTEGER,
   name HSTORE,
@@ -1972,10 +1986,10 @@ create type addressline as (
   distance FLOAT
 );
 
-CREATE OR REPLACE FUNCTION get_addressdata(in_place_id INTEGER) RETURNS setof addressline 
+CREATE OR REPLACE FUNCTION get_addressdata(in_place_id BIGINT) RETURNS setof addressline 
   AS $$
 DECLARE
-  for_place_id INTEGER;
+  for_place_id BIGINT;
   result TEXT[];
   search TEXT[];
   found INTEGER;
@@ -2018,6 +2032,39 @@ BEGIN
 
   found := 1000;
   hadcountry := false;
+  FOR location IN 
+    select placex.place_id, osm_type, osm_id,
+      CASE WHEN class = 'place' and type = 'postcode' THEN 'name' => postcode ELSE name END as name,
+      class, type, admin_level, true as fromarea, true as isaddress,
+      CASE WHEN rank_address = 0 THEN 100 WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address,
+      0 as distance, country_code
+      from placex
+      where place_id = for_place_id 
+  LOOP
+--RAISE WARNING '%',location;
+    IF searchcountrycode IS NULL AND location.country_code IS NOT NULL THEN
+      searchcountrycode := location.country_code;
+    END IF;
+    IF searchpostcode IS NOT NULL and location.type = 'postcode' THEN
+      location.isaddress := FALSE;
+    END IF;
+    IF location.rank_address = 4 AND location.isaddress THEN
+      hadcountry := true;
+    END IF;
+    IF location.rank_address < 4 AND NOT hadcountry THEN
+      select name from country_name where country_code = searchcountrycode limit 1 INTO countryname;
+      IF countryname IS NOT NULL THEN
+        countrylocation := ROW(null, null, null, countryname, 'place', 'country', null, true, true, 4, 0)::addressline;
+        RETURN NEXT countrylocation;
+      END IF;
+    END IF;
+    countrylocation := ROW(location.place_id, location.osm_type, location.osm_id, location.name, location.class, 
+                           location.type, location.admin_level, location.fromarea, location.isaddress, location.rank_address, 
+                           location.distance)::addressline;
+    RETURN NEXT countrylocation;
+    found := location.rank_address;
+  END LOOP;
+
   FOR location IN 
     select placex.place_id, osm_type, osm_id,
       CASE WHEN class = 'place' and type = 'postcode' THEN 'name' => postcode ELSE name END as name,
@@ -2026,7 +2073,8 @@ BEGIN
       distance,country_code
       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) OR address_place_id = for_place_id)
+      and (cached_rank_address > 0 AND cached_rank_address < searchrankaddress)
+      and address_place_id != for_place_id
       and (placex.country_code IS NULL OR searchcountrycode IS NULL OR placex.country_code = searchcountrycode OR rank_address < 4)
       order by rank_address desc,isaddress desc,fromarea desc,distance asc,rank_search desc
   LOOP
@@ -2089,7 +2137,7 @@ END;
 $$
 LANGUAGE plpgsql;
 
-CREATE OR REPLACE FUNCTION get_place_boundingbox(search_place_id INTEGER) RETURNS place_boundingbox
+CREATE OR REPLACE FUNCTION get_place_boundingbox(search_place_id BIGINT) RETURNS place_boundingbox
   AS $$
 DECLARE
   result place_boundingbox;
@@ -2126,7 +2174,7 @@ $$
 LANGUAGE plpgsql;
 
 -- don't do the operation if it would be slow
-CREATE OR REPLACE FUNCTION get_place_boundingbox_quick(search_place_id INTEGER) RETURNS place_boundingbox
+CREATE OR REPLACE FUNCTION get_place_boundingbox_quick(search_place_id BIGINT) RETURNS place_boundingbox
   AS $$
 DECLARE
   result place_boundingbox;
@@ -2165,7 +2213,7 @@ END;
 $$
 LANGUAGE plpgsql;
 
-CREATE OR REPLACE FUNCTION update_place(search_place_id INTEGER) RETURNS BOOLEAN
+CREATE OR REPLACE FUNCTION update_place(search_place_id BIGINT) RETURNS BOOLEAN
   AS $$
 DECLARE
   result place_boundingbox;
@@ -2338,7 +2386,7 @@ DECLARE
   rangestartnumber INTEGER;
   place_centroid GEOMETRY;
   partition INTEGER;
-  parent_place_id INTEGER;
+  parent_place_id BIGINT;
   location RECORD;
   address_street_word_id INTEGER;  
 
@@ -2418,32 +2466,41 @@ DECLARE
   newpoints INTEGER;
   place_centroid GEOMETRY;
   partition INTEGER;
-  parent_place_id INTEGER;
+  out_parent_place_id BIGINT;
   location RECORD;
   address_street_word_id INTEGER;  
+  out_postcode TEXT;
 
 BEGIN
 
   place_centroid := ST_Centroid(pointgeo);
   partition := get_partition(place_centroid, in_countrycode);
-  parent_place_id := null;
+  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;
+      out_parent_place_id := location.place_id;
     END LOOP;
   END IF;
 
-  IF parent_place_id IS NULL THEN
+  IF out_parent_place_id IS NULL THEN
     FOR location IN SELECT place_id FROM getNearestRoadFeature(partition, place_centroid) LOOP
-      parent_place_id := location.place_id;
+      out_parent_place_id := location.place_id;
     END LOOP;    
   END IF;
 
+  out_postcode := in_postcode;
+  IF out_postcode IS NULL THEN
+    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);
+  END IF;
+
   newpoints := 0;
   insert into location_property_aux (place_id, partition, parent_place_id, housenumber, postcode, centroid)
-    values (nextval('seq_place'), partition, parent_place_id, in_housenumber, in_postcode, place_centroid);
+    values (nextval('seq_place'), partition, out_parent_place_id, in_housenumber, out_postcode, place_centroid);
   newpoints := newpoints + 1;
 
   RETURN newpoints;
