X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/041b3edf891b26bd3e043bb3cd7ad52d05d384d9..3f8fcd4055228afeed05dbba7436de5bc011aa64:/sql/functions.sql?ds=sidebyside diff --git a/sql/functions.sql b/sql/functions.sql index 1baecfc0..b61f52b2 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 @@ -1029,7 +1029,7 @@ BEGIN ELSEIF NEW.type in ('suburb','croft','subdivision') THEN NEW.rank_search := 20; NEW.rank_address := NEW.rank_search; - ELSEIF NEW.type in ('farm','locality','islet','isolated_dwelling') THEN + ELSEIF NEW.type in ('farm','locality','islet','isolated_dwelling','mountain_pass') THEN NEW.rank_search := 20; NEW.rank_address := 0; ELSEIF NEW.type in ('hall_of_residence','neighbourhood','housing_estate','nature_reserve') THEN @@ -1265,7 +1265,7 @@ BEGIN -- 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(%#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 @@ -1481,34 +1481,36 @@ BEGIN -- mark the linked place (excludes from search results) UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; - DELETE from search_name where place_id = linkedPlacex.place_id; END LOOP; - FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP + IF NEW.centroid IS NULL THEN - select * from placex where osm_type = upper(substring(relMember.member,1,1)) - and osm_id = substring(relMember.member,2,10000)::integer order by rank_search desc limit 1 into linkedPlacex; + FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP - IF NEW.name->'name' = linkedPlacex.name->'name' THEN - -- If we don't already have one use this as the centre point of the geometry - IF NEW.centroid IS NULL THEN - NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry)); - END IF; + select * from placex where osm_type = upper(substring(relMember.member,1,1)) + and osm_id = substring(relMember.member,2,10000)::integer order by rank_search desc limit 1 into linkedPlacex; - -- merge in the name, re-init word vector - NEW.name := linkedPlacex.name || NEW.name; - name_vector := make_keywords(NEW.name); + IF NEW.name->'name' = linkedPlacex.name->'name' AND NEW.rank_search = linkedPlacex.rank_search THEN + -- If we don't already have one use this as the centre point of the geometry + IF NEW.centroid IS NULL THEN + NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry)); + END IF; - -- merge in extra tags - NEW.extratags := linkedPlacex.extratags || NEW.extratags; + -- merge in the name, re-init word vector + NEW.name := linkedPlacex.name || NEW.name; + name_vector := make_keywords(NEW.name); - -- mark the linked place (excludes from search results) - UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; - DELETE from search_name where place_id = linkedPlacex.place_id; - END IF; + -- merge in extra tags + NEW.extratags := linkedPlacex.extratags || NEW.extratags; - END LOOP; + -- mark the linked place (excludes from search results) + UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; + END IF; + + END LOOP; + + END IF; -- not found one yet? how about doing a name search IF NEW.centroid IS NULL THEN @@ -1534,12 +1536,12 @@ BEGIN -- mark the linked place (excludes from search results) UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; - DELETE from search_name where place_id = linkedPlacex.place_id; + END LOOP; END IF; IF NEW.centroid IS NOT NULL THEN - place_centroid := NEW.centroid + place_centroid := NEW.centroid; END IF; END IF; @@ -2523,8 +2525,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; @@ -2561,32 +2563,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; @@ -2603,7 +2605,7 @@ DECLARE newpoints INTEGER; place_centroid GEOMETRY; - partition INTEGER; + out_partition INTEGER; out_parent_place_id BIGINT; location RECORD; address_street_word_id INTEGER; @@ -2612,18 +2614,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; @@ -2633,12 +2635,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;