X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/d4fa528d5c9d8435126b1d9286e84d5208bdfd9d..a09db943dadbb4ec3fae718900d0ccb1b500b8bd:/sql/functions.sql diff --git a/sql/functions.sql b/sql/functions.sql index e45062a8..75ccfd68 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -853,6 +853,9 @@ BEGIN END IF; ELSEIF NEW.class = 'boundary' AND NOT is_area THEN return NULL; + ELSEIF NEW.class = 'boundary' AND NEW.type = 'administrative' + AND NEW.admin_level <= 4 AND NEW.osm_type = 'W' THEN + return NULL; ELSEIF NEW.class = 'railway' AND NEW.type in ('rail') THEN return NULL; ELSEIF NEW.osm_type = 'N' AND NEW.class = 'highway' THEN @@ -1159,6 +1162,7 @@ TRIGGER DECLARE place_centroid GEOMETRY; + near_centroid GEOMETRY; search_maxdistance FLOAT[]; search_mindistance FLOAT[]; @@ -1263,6 +1267,8 @@ BEGIN -- Speed up searches - just use the centroid of the feature -- cheaper but less acurate place_centroid := ST_PointOnSurface(NEW.geometry); + -- For searching near features rather use the centroid + near_centroid := ST_Centroid(NEW.geometry); NEW.centroid := null; NEW.postcode := null; --DEBUG: RAISE WARNING 'Computing preliminary centroid at %',ST_AsText(place_centroid); @@ -1308,10 +1314,6 @@ BEGIN --DEBUG: RAISE WARNING 'Waterway processed'; END IF; - -- Adding ourselves to the list simplifies address calculations later - INSERT INTO place_addressline (place_id, address_place_id, fromarea, isaddress, distance, cached_rank_address) - VALUES (NEW.place_id, NEW.place_id, true, true, 0, NEW.rank_address); - -- What level are we searching from search_maxrank := NEW.rank_search; @@ -1397,7 +1399,7 @@ BEGIN IF NEW.parent_place_id IS NULL AND addr_street IS NOT NULL THEN address_street_word_ids := get_name_ids(make_standard_name(addr_street)); IF address_street_word_ids IS NOT NULL THEN - SELECT place_id from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) INTO NEW.parent_place_id; + SELECT place_id from getNearestNamedRoadFeature(NEW.partition, near_centroid, address_street_word_ids) INTO NEW.parent_place_id; END IF; END IF; --DEBUG: RAISE WARNING 'Checked for addr:street (%)', NEW.parent_place_id; @@ -1405,7 +1407,7 @@ BEGIN IF NEW.parent_place_id IS NULL AND addr_place IS NOT NULL THEN address_street_word_ids := get_name_ids(make_standard_name(addr_place)); IF address_street_word_ids IS NOT NULL THEN - SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) INTO NEW.parent_place_id; + SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, near_centroid, address_street_word_ids) INTO NEW.parent_place_id; END IF; END IF; --DEBUG: RAISE WARNING 'Checked for addr:place (%)', NEW.parent_place_id; @@ -1440,7 +1442,7 @@ BEGIN IF location.address ? 'street' THEN address_street_word_ids := get_name_ids(make_standard_name(location.address->'street')); IF address_street_word_ids IS NOT NULL THEN - SELECT place_id from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) INTO NEW.parent_place_id; + SELECT place_id from getNearestNamedRoadFeature(NEW.partition, near_centroid, address_street_word_ids) INTO NEW.parent_place_id; EXIT WHEN NEW.parent_place_id is not NULL; END IF; END IF; @@ -1449,7 +1451,7 @@ BEGIN IF location.address ? 'place' THEN address_street_word_ids := get_name_ids(make_standard_name(location.address->'place')); IF address_street_word_ids IS NOT NULL THEN - SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) INTO NEW.parent_place_id; + SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, near_centroid, address_street_word_ids) INTO NEW.parent_place_id; EXIT WHEN NEW.parent_place_id is not NULL; END IF; END IF; @@ -1478,7 +1480,7 @@ BEGIN -- Still nothing, just use the nearest road IF NEW.parent_place_id IS NULL THEN - SELECT place_id FROM getNearestRoadFeature(NEW.partition, place_centroid) INTO NEW.parent_place_id; + SELECT place_id FROM getNearestRoadFeature(NEW.partition, near_centroid) INTO NEW.parent_place_id; END IF; --DEBUG: RAISE WARNING 'Checked for nearest way (%)', NEW.parent_place_id; @@ -1501,7 +1503,7 @@ BEGIN NEW.postcode := location.postcode; END IF; IF NEW.postcode is null THEN - NEW.postcode := get_nearest_postcode(NEW.country_code, place_centroid); + NEW.postcode := get_nearest_postcode(NEW.country_code, near_centroid); END IF; END IF; @@ -1771,7 +1773,7 @@ BEGIN CASE WHEN NEW.rank_search >= 26 AND NEW.rank_search < 30 THEN NEW.geometry - ELSE place_centroid END, + ELSE near_centroid END, search_maxrank, isin_tokens) LOOP IF location.rank_address != location_rank_search THEN @@ -2302,6 +2304,9 @@ create type addressline as ( distance FLOAT ); +-- Compute the list of address parts for the given place. +-- +-- If in_housenumber is greator or equal 0, look for an interpolation. CREATE OR REPLACE FUNCTION get_addressdata(in_place_id BIGINT, in_housenumber INTEGER) RETURNS setof addressline AS $$ DECLARE @@ -2316,53 +2321,72 @@ DECLARE searchhousename HSTORE; searchrankaddress INTEGER; searchpostcode TEXT; + postcode_isaddress BOOL; searchclass TEXT; searchtype TEXT; countryname HSTORE; - hadcountry BOOLEAN; BEGIN + -- The place ein question might not have a direct entry in place_addressline. + -- Look for the parent of such places then and save if in for_place_id. + + postcode_isaddress := true; + -- first query osmline (interpolation lines) - select parent_place_id, country_code, 30, postcode, null, 'place', 'house' from location_property_osmline - WHERE place_id = in_place_id AND in_housenumber>=startnumber AND in_housenumber <= endnumber - INTO for_place_id,searchcountrycode, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype; - IF for_place_id IS NOT NULL THEN - searchhousenumber = in_housenumber::text; + IF in_housenumber >= 0 THEN + SELECT parent_place_id, country_code, in_housenumber::text, 30, postcode, + null, 'place', 'house' + FROM location_property_osmline + WHERE place_id = in_place_id AND in_housenumber>=startnumber + AND in_housenumber <= endnumber + INTO for_place_id, searchcountrycode, searchhousenumber, searchrankaddress, + searchpostcode, searchhousename, searchclass, searchtype; END IF; --then query tiger data -- %NOTIGERDATA% IF 0 THEN - IF for_place_id IS NULL THEN - select parent_place_id,'us', 30, postcode, null, 'place', 'house' from location_property_tiger - WHERE place_id = in_place_id AND in_housenumber>=startnumber AND in_housenumber <= endnumber - INTO for_place_id,searchcountrycode, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype; - IF for_place_id IS NOT NULL THEN - searchhousenumber = in_housenumber::text; - END IF; + IF for_place_id IS NULL AND in_housenumber >= 0 THEN + SELECT parent_place_id, 'us', in_housenumber::text, 30, postcode, null, + 'place', 'house' + FROM location_property_tiger + WHERE place_id = in_place_id AND in_housenumber >= startnumber + AND in_housenumber <= endnumber + INTO for_place_id, searchcountrycode, searchhousenumber, searchrankaddress, + searchpostcode, searchhousename, searchclass, searchtype; END IF; -- %NOTIGERDATA% END IF; -- %NOAUXDATA% IF 0 THEN IF for_place_id IS NULL THEN - select parent_place_id,'us', housenumber, 30, postcode, null, 'place', 'house' from location_property_aux + SELECT parent_place_id, 'us', housenumber, 30, postcode, null, 'place', 'house' + FROM location_property_aux WHERE place_id = in_place_id - INTO for_place_id,searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype; + INTO for_place_id,searchcountrycode, searchhousenumber, searchrankaddress, + searchpostcode, searchhousename, searchclass, searchtype; END IF; -- %NOAUXDATA% END IF; -- postcode table IF for_place_id IS NULL THEN - select parent_place_id, country_code, rank_address, postcode, 'place', 'postcode' + SELECT parent_place_id, country_code, rank_address, postcode, 'place', 'postcode' FROM location_postcode WHERE place_id = in_place_id - INTO for_place_id, searchcountrycode, searchrankaddress, searchpostcode, searchclass, searchtype; + INTO for_place_id, searchcountrycode, searchrankaddress, searchpostcode, + searchclass, searchtype; END IF; + -- POI objects in the placex table IF for_place_id IS NULL THEN - select parent_place_id, country_code, housenumber, rank_search, postcode, name, class, type from placex - WHERE place_id = in_place_id and rank_search > 27 - INTO for_place_id, searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename, searchclass, searchtype; + SELECT parent_place_id, country_code, housenumber, rank_search, postcode, + name, class, type + FROM placex + WHERE place_id = in_place_id and rank_search > 27 + INTO for_place_id, searchcountrycode, searchhousenumber, searchrankaddress, + searchpostcode, searchhousename, searchclass, searchtype; END IF; + -- If for_place_id is still NULL at this point then the object has its own + -- entry in place_address line. However, still check if there is not linked + -- place we should be using instead. IF for_place_id IS NULL THEN select coalesce(linked_place_id, place_id), country_code, housenumber, rank_search, postcode, null @@ -2372,103 +2396,105 @@ BEGIN --RAISE WARNING '% % % %',searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode; - found := 1000; - hadcountry := false; - FOR location IN - select placex.place_id, osm_type, osm_id, name, - class, type, admin_level, 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, postcode - from placex - where place_id = for_place_id + found := 1000; -- the lowest rank_address included + + -- Return the record for the base entry. + FOR location IN + SELECT placex.place_id, osm_type, osm_id, name, + class, type, admin_level, + type not in ('postcode', 'postal_code') 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, postcode + 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 location.type in ('postcode', 'postal_code') THEN - location.isaddress := FALSE; - ELSEIF location.rank_address = 4 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; + IF location.rank_address < 4 THEN + -- no country locations for ranks higher than country + searchcountrycode := NULL; END IF; - countrylocation := ROW(location.place_id, location.osm_type, location.osm_id, location.name, location.class, - location.type, location.admin_level, true, location.isaddress, location.rank_address, - location.distance)::addressline; + countrylocation := ROW(location.place_id, location.osm_type, location.osm_id, + location.name, location.class, location.type, + location.admin_level, true, 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, name, - CASE WHEN extratags ? 'place' THEN 'place' ELSE class END as class, - CASE WHEN extratags ? 'place' THEN extratags->'place' ELSE type END as 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,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) - and address_place_id != for_place_id and linked_place_id is null - and (placex.country_code IS NULL OR searchcountrycode IS NULL OR placex.country_code = searchcountrycode) - order by rank_address desc,isaddress desc,fromarea desc,distance asc,rank_search desc + FOR location IN + SELECT placex.place_id, osm_type, osm_id, name, + CASE WHEN extratags ? 'place' THEN 'place' ELSE class END as class, + CASE WHEN extratags ? 'place' THEN extratags->'place' ELSE type END as type, + admin_level, fromarea, isaddress and linked_place_id is NULL as isaddress, + CASE WHEN rank_address = 11 THEN 5 ELSE rank_address END as rank_address, + distance, 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 >= 4 AND cached_rank_address < searchrankaddress) + AND linked_place_id is null + AND (placex.country_code IS NULL OR searchcountrycode IS NULL + OR placex.country_code = searchcountrycode) + ORDER BY rank_address desc, isaddress desc, fromarea desc, + distance asc, rank_search desc LOOP --RAISE WARNING '%',location; IF searchcountrycode IS NULL AND location.country_code IS NOT NULL THEN searchcountrycode := location.country_code; END IF; IF location.type in ('postcode', 'postal_code') 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; + postcode_isaddress := false; + IF location.osm_type != 'R' THEN + location.isaddress := FALSE; 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, + 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; + -- If no country was included yet, add the name information from country_name. IF found > 4 THEN - select name from country_name where country_code = searchcountrycode limit 1 INTO countryname; + SELECT name FROM country_name + WHERE country_code = searchcountrycode LIMIT 1 INTO countryname; --RAISE WARNING '% % %',found,searchcountrycode,countryname; IF countryname IS NOT NULL THEN - location := ROW(null, null, null, countryname, 'place', 'country', null, true, true, 4, 0)::addressline; + location := ROW(null, null, null, countryname, 'place', 'country', + null, true, true, 4, 0)::addressline; RETURN NEXT location; END IF; END IF; + -- Finally add some artificial rows. IF searchcountrycode IS NOT NULL THEN - location := ROW(null, null, null, hstore('ref', searchcountrycode), 'place', 'country_code', null, true, false, 4, 0)::addressline; + location := ROW(null, null, null, hstore('ref', searchcountrycode), + 'place', 'country_code', null, true, false, 4, 0)::addressline; RETURN NEXT location; END IF; IF searchhousename IS NOT NULL THEN - location := ROW(in_place_id, null, null, searchhousename, searchclass, searchtype, null, true, true, 29, 0)::addressline; + location := ROW(in_place_id, null, null, searchhousename, searchclass, + searchtype, null, true, true, 29, 0)::addressline; RETURN NEXT location; END IF; IF searchhousenumber IS NOT NULL THEN - location := ROW(in_place_id, null, null, hstore('ref', searchhousenumber), 'place', 'house_number', null, true, true, 28, 0)::addressline; + location := ROW(in_place_id, null, null, hstore('ref', searchhousenumber), + 'place', 'house_number', null, true, true, 28, 0)::addressline; RETURN NEXT location; END IF; IF searchpostcode IS NOT NULL THEN - location := ROW(null, null, null, hstore('ref', searchpostcode), 'place', 'postcode', null, true, true, 5, 0)::addressline; + location := ROW(null, null, null, hstore('ref', searchpostcode), 'place', + 'postcode', null, false, postcode_isaddress, 5, 0)::addressline; RETURN NEXT location; END IF; @@ -2478,96 +2504,6 @@ $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION get_searchrank_label(rank INTEGER) RETURNS TEXT - AS $$ -DECLARE -BEGIN - IF rank < 2 THEN - RETURN 'Continent'; - ELSEIF rank < 4 THEN - RETURN 'Sea'; - ELSEIF rank < 8 THEN - RETURN 'Country'; - ELSEIF rank < 12 THEN - RETURN 'State'; - ELSEIF rank < 16 THEN - RETURN 'County'; - ELSEIF rank = 16 THEN - RETURN 'City'; - ELSEIF rank = 17 THEN - RETURN 'Town / Island'; - ELSEIF rank = 18 THEN - RETURN 'Village / Hamlet'; - ELSEIF rank = 20 THEN - RETURN 'Suburb'; - ELSEIF rank = 21 THEN - RETURN 'Postcode Area'; - ELSEIF rank = 22 THEN - RETURN 'Croft / Farm / Locality / Islet'; - ELSEIF rank = 23 THEN - RETURN 'Postcode Area'; - ELSEIF rank = 25 THEN - RETURN 'Postcode Point'; - ELSEIF rank = 26 THEN - RETURN 'Street / Major Landmark'; - ELSEIF rank = 27 THEN - RETURN 'Minory Street / Path'; - ELSEIF rank = 28 THEN - RETURN 'House / Building'; - ELSE - RETURN 'Other: '||rank; - END IF; - -END; -$$ -LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION get_addressrank_label(rank INTEGER) RETURNS TEXT - AS $$ -DECLARE -BEGIN - IF rank = 0 THEN - RETURN 'None'; - ELSEIF rank < 2 THEN - RETURN 'Continent'; - ELSEIF rank < 4 THEN - RETURN 'Sea'; - ELSEIF rank = 5 THEN - RETURN 'Postcode'; - ELSEIF rank < 8 THEN - RETURN 'Country'; - ELSEIF rank < 12 THEN - RETURN 'State'; - ELSEIF rank < 16 THEN - RETURN 'County'; - ELSEIF rank = 16 THEN - RETURN 'City'; - ELSEIF rank = 17 THEN - RETURN 'Town / Village / Hamlet'; - ELSEIF rank = 20 THEN - RETURN 'Suburb'; - ELSEIF rank = 21 THEN - RETURN 'Postcode Area'; - ELSEIF rank = 22 THEN - RETURN 'Croft / Farm / Locality / Islet'; - ELSEIF rank = 23 THEN - RETURN 'Postcode Area'; - ELSEIF rank = 25 THEN - RETURN 'Postcode Point'; - ELSEIF rank = 26 THEN - RETURN 'Street / Major Landmark'; - ELSEIF rank = 27 THEN - RETURN 'Minory Street / Path'; - ELSEIF rank = 28 THEN - RETURN 'House / Building'; - ELSE - RETURN 'Other: '||rank; - END IF; - -END; -$$ -LANGUAGE plpgsql; - CREATE OR REPLACE FUNCTION aux_create_property(pointgeo GEOMETRY, in_housenumber TEXT, in_street TEXT, in_isin TEXT, in_postcode TEXT, in_countrycode char(2)) RETURNS INTEGER AS $$