X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/ff85da0a31874050c32712d9bb1d1a5592c50a81..9afebddb16b09e09fa9a74624dcd79475b476f10:/sql/functions/address_lookup.sql?ds=sidebyside diff --git a/sql/functions/address_lookup.sql b/sql/functions/address_lookup.sql index b57ae040..b832aed8 100644 --- a/sql/functions/address_lookup.sql +++ b/sql/functions/address_lookup.sql @@ -79,6 +79,18 @@ END; $$ LANGUAGE plpgsql STABLE; +DROP TYPE IF EXISTS addressdata_place; +CREATE TYPE addressdata_place AS ( + place_id BIGINT, + country_code VARCHAR(2), + housenumber TEXT, + postcode TEXT, + class TEXT, + type TEXT, + name HSTORE, + address HSTORE, + centroid GEOMETRY +); -- Compute the list of address parts for the given place. -- @@ -87,19 +99,21 @@ CREATE OR REPLACE FUNCTION get_addressdata(in_place_id BIGINT, in_housenumber IN RETURNS setof addressline AS $$ DECLARE - place RECORD; + place addressdata_place; location RECORD; current_rank_address INTEGER; + location_isaddress BOOLEAN; BEGIN -- The place in 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. + -- Look for the parent of such places then and save it in place. -- first query osmline (interpolation lines) IF in_housenumber >= 0 THEN SELECT parent_place_id as place_id, country_code, - in_housenumber::text as housenumber, postcode, + in_housenumber as housenumber, postcode, 'place' as class, 'house' as type, - null as name, null::hstore as address + null as name, null as address, + ST_Centroid(linegeo) as centroid INTO place FROM location_property_osmline WHERE place_id = in_place_id @@ -110,9 +124,10 @@ BEGIN -- %NOTIGERDATA% IF 0 THEN IF place IS NULL AND in_housenumber >= 0 THEN SELECT parent_place_id as place_id, 'us' as country_code, - in_housenumber::text as housenumber, postcode, + in_housenumber as housenumber, postcode, 'place' as class, 'house' as type, - null as name, null::hstore as address + null as name, null as address, + ST_Centroid(linegeo) as centroid INTO place FROM location_property_tiger WHERE place_id = in_place_id @@ -125,7 +140,8 @@ BEGIN SELECT parent_place_id as place_id, 'us' as country_code, housenumber, postcode, 'place' as class, 'house' as type, - null as name, null::hstore as address + null as name, null as address, + centroid INTO place FROM location_property_aux WHERE place_id = in_place_id; @@ -135,9 +151,10 @@ BEGIN -- postcode table IF place IS NULL THEN SELECT parent_place_id as place_id, country_code, - null as housenumber, postcode, + null::text as housenumber, postcode, 'place' as class, 'postcode' as type, - null as name, null::hstore as address + null as name, null as address, + null as centroid INTO place FROM location_postcode WHERE place_id = in_place_id; @@ -148,20 +165,22 @@ BEGIN SELECT parent_place_id as place_id, country_code, housenumber, postcode, class, type, - name, address + name, address, + centroid INTO place FROM placex WHERE place_id = in_place_id and rank_search > 27; END IF; - -- If for_place_id is still NULL at this point then the object has its own + -- If place 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 place IS NULL THEN select coalesce(linked_place_id, place_id) as place_id, country_code, housenumber, postcode, class, type, - null as name, address + null as name, address, + null as centroid INTO place FROM placex where place_id = in_place_id; END IF; @@ -184,7 +203,7 @@ BEGIN --RAISE WARNING '%',location; IF location.rank_address < 4 THEN -- no country locations for ranks higher than country - place.country_code := NULL; + place.country_code := NULL::varchar(2); ELSEIF place.country_code IS NULL AND location.country_code IS NOT NULL THEN place.country_code := location.country_code; END IF; @@ -204,7 +223,7 @@ BEGIN FOR location IN SELECT placex.place_id, osm_type, osm_id, name, class, type, coalesce(extratags->'linked_place', extratags->'place') as place_type, - admin_level, fromarea, isaddress, + 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) @@ -212,11 +231,17 @@ BEGIN AND linked_place_id is null AND (placex.country_code IS NULL OR place.country_code IS NULL OR placex.country_code = place.country_code) - ORDER BY rank_address desc, (place_addressline.place_id = in_place_id) desc, + 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 LOOP -- RAISE WARNING '%',location; + location_isaddress := location.rank_address != current_rank_address; + IF place.country_code IS NULL AND location.country_code IS NOT NULL THEN place.country_code := location.country_code; END IF; @@ -231,20 +256,18 @@ BEGIN THEN place.postcode := null; -- remove the less exact postcode ELSE - location.isaddress := false; + location_isaddress := false; END IF; END IF; RETURN NEXT ROW(location.place_id, location.osm_type, location.osm_id, - location.name, location.class, location.type, - location.place_type, - location.admin_level, location.fromarea, - location.isaddress and location.rank_address != current_rank_address, - location.rank_address, - location.distance)::addressline; - - IF location.isaddress THEN - current_rank_address := location.rank_address; - END IF; + location.name, location.class, location.type, + location.place_type, + location.admin_level, location.fromarea, + location_isaddress, + location.rank_address, + location.distance)::addressline; + + current_rank_address := location.rank_address; END LOOP; -- If no country was included yet, add the name information from country_name.