X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/6b52e3765c7de9fa4831470d5f079b37e45d92bf..e1b096cf8cf1ac6904c9adf0963f0e1756ca65ea:/sql/functions/address_lookup.sql?ds=inline diff --git a/sql/functions/address_lookup.sql b/sql/functions/address_lookup.sql index dd096728..b832aed8 100644 --- a/sql/functions/address_lookup.sql +++ b/sql/functions/address_lookup.sql @@ -8,6 +8,7 @@ CREATE TYPE addressline as ( name HSTORE, class TEXT, type TEXT, + place_type TEXT, admin_level INTEGER, fromarea BOOLEAN, isaddress BOOLEAN, @@ -59,14 +60,16 @@ BEGIN prevresult := ''; FOR location IN - SELECT * FROM get_addressdata(for_place_id, housenumber) + SELECT name, + CASE WHEN place_id = for_place_id THEN 99 ELSE rank_address END as rank_address + FROM get_addressdata(for_place_id, housenumber) WHERE isaddress order by rank_address desc LOOP currresult := trim(get_name_by_language(location.name, languagepref)); IF currresult != prevresult AND currresult IS NOT NULL AND result[(100 - location.rank_address)] IS NULL THEN - result[(100 - location.rank_address)] := trim(get_name_by_language(location.name, languagepref)); + result[(100 - location.rank_address)] := currresult; prevresult := currresult; END IF; END LOOP; @@ -76,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. -- @@ -84,192 +99,215 @@ CREATE OR REPLACE FUNCTION get_addressdata(in_place_id BIGINT, in_housenumber IN RETURNS setof addressline AS $$ DECLARE - for_place_id BIGINT; - result TEXT[]; - search TEXT[]; - found INTEGER; + place addressdata_place; location RECORD; - countrylocation RECORD; - searchcountrycode varchar(2); - searchhousenumber TEXT; - searchhousename HSTORE; - searchrankaddress INTEGER; - searchpostcode TEXT; - postcode_isaddress BOOL; - searchclass TEXT; - searchtype TEXT; - countryname HSTORE; + current_rank_address INTEGER; + location_isaddress 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; + -- The place in question might not have a direct entry in place_addressline. + -- 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, country_code, in_housenumber::text, 30, postcode, - null, 'place', 'house' + SELECT parent_place_id as place_id, country_code, + in_housenumber as housenumber, postcode, + 'place' as class, 'house' as type, + null as name, null as address, + ST_Centroid(linegeo) as centroid + INTO place 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; + WHERE place_id = in_place_id + AND in_housenumber between startnumber and endnumber; END IF; --then query tiger data -- %NOTIGERDATA% IF 0 THEN - IF for_place_id IS NULL AND in_housenumber >= 0 THEN - SELECT parent_place_id, 'us', in_housenumber::text, 30, postcode, null, - 'place', 'house' + 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, + 'place' as class, 'house' as type, + null as name, null as address, + ST_Centroid(linegeo) as centroid + INTO place 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; + WHERE place_id = in_place_id + AND in_housenumber between startnumber and endnumber; 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' + 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 - INTO for_place_id,searchcountrycode, searchhousenumber, searchrankaddress, - searchpostcode, searchhousename, searchclass, searchtype; + WHERE place_id = in_place_id; END IF; -- %NOAUXDATA% END IF; -- postcode table - IF for_place_id IS NULL THEN - SELECT parent_place_id, country_code, rank_search, postcode, 'place', 'postcode' + IF place IS NULL THEN + SELECT parent_place_id as place_id, country_code, + null::text as housenumber, postcode, + 'place' as class, 'postcode' as type, + null as name, null as address, + null as centroid + INTO place FROM location_postcode - WHERE place_id = in_place_id - INTO for_place_id, searchcountrycode, searchrankaddress, searchpostcode, - searchclass, searchtype; + WHERE place_id = in_place_id; 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 + IF place IS NULL THEN + SELECT parent_place_id as place_id, country_code, + housenumber, postcode, + class, type, + name, address, + centroid + INTO place FROM placex - WHERE place_id = in_place_id and rank_search > 27 - INTO for_place_id, searchcountrycode, searchhousenumber, searchrankaddress, - searchpostcode, searchhousename, searchclass, searchtype; + 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 for_place_id IS NULL THEN - select coalesce(linked_place_id, place_id), country_code, - housenumber, rank_search, postcode, null - from placex where place_id = in_place_id - INTO for_place_id, searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode, searchhousename; + 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 centroid + INTO place + FROM placex where place_id = in_place_id; END IF; ---RAISE WARNING '% % % %',searchcountrycode, searchhousenumber, searchrankaddress, searchpostcode; +--RAISE WARNING '% % % %',searchcountrycode, searchhousenumber, searchpostcode; - found := 1000; -- the lowest rank_address included + -- --- Return the record for the base entry. - -- Return the record for the base entry. FOR location IN SELECT placex.place_id, osm_type, osm_id, name, + coalesce(extratags->'linked_place', extratags->'place') as place_type, 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 + country_code FROM placex - WHERE place_id = for_place_id + WHERE place_id = place.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.rank_address < 4 THEN -- no country locations for ranks higher than country - searchcountrycode := 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; - 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; + + RETURN NEXT ROW(location.place_id, location.osm_type, location.osm_id, + location.name, location.class, location.type, + location.place_type, + location.admin_level, true, + location.type not in ('postcode', 'postal_code'), + location.rank_address, 0)::addressline; + + current_rank_address := location.rank_address; END LOOP; + -- --- Return records for address parts. + FOR location IN - SELECT placex.place_id, osm_type, osm_id, name, - CASE WHEN extratags ? 'place' or extratags ? 'linked_place' - THEN 'place' ELSE class END as class, - coalesce(extratags->'place', extratags->'linked_place', type) as type, + SELECT placex.place_id, osm_type, osm_id, name, class, type, + coalesce(extratags->'linked_place', extratags->'place') as place_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) + WHERE place_addressline.place_id IN (place.place_id, in_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, + 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, + (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; - IF searchcountrycode IS NULL AND location.country_code IS NOT NULL THEN - searchcountrycode := location.country_code; + -- 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; - IF location.type in ('postcode', 'postal_code') THEN - postcode_isaddress := false; - IF location.osm_type != 'R' THEN - location.isaddress := FALSE; + IF location.type in ('postcode', 'postal_code') + AND place.postcode is not null + THEN + -- 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 + AND (place.address is null or not place.address ? 'postcode') + THEN + place.postcode := null; -- remove the less exact postcode + ELSE + 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, - location.distance)::addressline; - RETURN NEXT countrylocation; - found := location.rank_address; + 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, + 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. - IF found > 4 THEN - 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', + IF current_rank_address > 4 THEN + FOR location IN + SELECT 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, null, true, true, 4, 0)::addressline; - RETURN NEXT location; - END IF; + END LOOP; 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; + IF place.country_code IS NOT NULL THEN + location := ROW(null, null, null, hstore('ref', place.country_code), + 'place', 'country_code', null, 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; + IF place.name IS NOT NULL THEN + location := ROW(in_place_id, null, null, place.name, place.class, + place.type, null, 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; + IF place.housenumber IS NOT NULL THEN + location := ROW(null, null, null, hstore('ref', place.housenumber), + 'place', 'house_number', null, 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, false, postcode_isaddress, 5, 0)::addressline; + IF place.address is not null and place.address ? '_unlisted_place' THEN + RETURN NEXT ROW(null, null, null, hstore('name', place.address->'_unlisted_place'), + 'place', 'locality', null, null, true, true, 25, 0)::addressline; + END IF; + + IF place.postcode is not null THEN + location := ROW(null, null, null, hstore('ref', place.postcode), 'place', + 'postcode', null, null, false, true, 5, 0)::addressline; RETURN NEXT location; END IF;