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;