X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/6a7e0d652b1d40a397e1c1386d500101796676c4..8c14df55a61d71f2476c8a17287e1bd162a5c6a6:/lib-sql/functions/address_lookup.sql diff --git a/lib-sql/functions/address_lookup.sql b/lib-sql/functions/address_lookup.sql index 4d7cc789..26ce2073 100644 --- a/lib-sql/functions/address_lookup.sql +++ b/lib-sql/functions/address_lookup.sql @@ -1,3 +1,10 @@ +-- SPDX-License-Identifier: GPL-2.0-only +-- +-- This file is part of Nominatim. (https://nominatim.org) +-- +-- Copyright (C) 2022 by the Nominatim developer community. +-- For a full list of authors see the git log. + -- Functions for returning address information for a place. DROP TYPE IF EXISTS addressline CASCADE; @@ -36,8 +43,8 @@ BEGIN END IF; END LOOP; - -- anything will do as a fallback - just take the first name type thing there is - RETURN trim((avals(name))[1]); + -- as a fallback - take the last element since it is the default name + RETURN trim((avals(name))[array_length(avals(name), 1)]); END; $$ LANGUAGE plpgsql IMMUTABLE; @@ -101,6 +108,7 @@ CREATE OR REPLACE FUNCTION get_addressdata(in_place_id BIGINT, in_housenumber IN DECLARE place addressdata_place; location RECORD; + country RECORD; current_rank_address INTEGER; location_isaddress BOOLEAN; BEGIN @@ -121,7 +129,7 @@ BEGIN END IF; --then query tiger data - -- %NOTIGERDATA% IF 0 THEN + {% if config.get_bool('USE_US_TIGER_DATA') %} IF place IS NULL AND in_housenumber >= 0 THEN SELECT parent_place_id as place_id, 'us' as country_code, in_housenumber as housenumber, postcode, @@ -133,20 +141,7 @@ BEGIN WHERE place_id = in_place_id AND in_housenumber between startnumber and endnumber; END IF; - -- %NOTIGERDATA% END IF; - - -- %NOAUXDATA% IF 0 THEN - IF place IS NULL THEN - SELECT parent_place_id as place_id, 'us' as country_code, - housenumber, postcode, - 'place' as class, 'house' as type, - null as name, null as address, - centroid - INTO place - FROM location_property_aux - WHERE place_id = in_place_id; - END IF; - -- %NOAUXDATA% END IF; + {% endif %} -- postcode table IF place IS NULL THEN @@ -163,7 +158,10 @@ BEGIN -- POI objects in the placex table IF place IS NULL THEN SELECT parent_place_id as place_id, country_code, - housenumber, postcode, + coalesce(address->'housenumber', + address->'streetnumber', + address->'conscriptionnumber')::text as housenumber, + postcode, class, type, name, address, centroid @@ -177,7 +175,7 @@ BEGIN -- place we should be using instead. IF place IS NULL THEN select coalesce(linked_place_id, place_id) as place_id, country_code, - housenumber, postcode, + null::text as housenumber, postcode, class, type, null as name, address, null as centroid @@ -189,6 +187,7 @@ BEGIN -- --- Return the record for the base entry. + current_rank_address := 1000; FOR location IN SELECT placex.place_id, osm_type, osm_id, name, coalesce(extratags->'linked_place', extratags->'place') as place_type, @@ -201,6 +200,16 @@ BEGIN WHERE place_id = place.place_id LOOP --RAISE WARNING '%',location; + -- mix in default names for countries + IF location.rank_address = 4 and place.country_code is not NULL THEN + FOR country IN + SELECT coalesce(name, ''::hstore) as name FROM country_name + WHERE country_code = place.country_code LIMIT 1 + LOOP + place.name := country.name || place.name; + END LOOP; + END IF; + IF location.rank_address < 4 THEN -- no country locations for ranks higher than country place.country_code := NULL::varchar(2); @@ -233,11 +242,13 @@ BEGIN OR placex.country_code = place.country_code) ORDER BY rank_address desc, (place_addressline.place_id = in_place_id) desc, - (fromarea and place.centroid is not null and not isaddress - and (place.address is null or avals(name) && avals(place.address)) - and ST_Contains(geometry, place.centroid)) desc, - isaddress desc, fromarea desc, - distance asc, rank_search desc + (CASE WHEN coalesce((avals(name) && avals(place.address)), False) THEN 2 + WHEN isaddress THEN 0 + WHEN fromarea + and place.centroid is not null + and ST_Contains(geometry, place.centroid) THEN 1 + ELSE -1 END) desc, + fromarea desc, distance asc, rank_search desc LOOP -- RAISE WARNING '%',location; location_isaddress := location.rank_address != current_rank_address; @@ -251,7 +262,7 @@ BEGIN -- If the place had a postcode assigned, take this one only -- into consideration when it is an area and the place does not have -- a postcode itself. - IF location.fromarea AND location.isaddress + IF location.fromarea AND location_isaddress AND (place.address is null or not place.address ? 'postcode') THEN place.postcode := null; -- remove the less exact postcode @@ -273,7 +284,8 @@ BEGIN -- If no country was included yet, add the name information from country_name. IF current_rank_address > 4 THEN FOR location IN - SELECT name FROM country_name WHERE country_code = place.country_code LIMIT 1 + SELECT name || coalesce(derived_name, ''::hstore) as name FROM country_name + WHERE country_code = place.country_code LIMIT 1 LOOP --RAISE WARNING '% % %',current_rank_address,searchcountrycode,countryname; RETURN NEXT ROW(null, null, null, location.name, 'place', 'country', NULL, @@ -309,6 +321,11 @@ BEGIN location := ROW(null, null, null, hstore('ref', place.postcode), 'place', 'postcode', null, null, false, true, 5, 0)::addressline; RETURN NEXT location; + ELSEIF place.address is not null and place.address ? 'postcode' + and not place.address->'postcode' SIMILAR TO '%(,|;)%' THEN + location := ROW(null, null, null, hstore('ref', place.address->'postcode'), 'place', + 'postcode', null, null, false, true, 5, 0)::addressline; + RETURN NEXT location; END IF; RETURN;