X-Git-Url: https://git.openstreetmap.org/nominatim.git/blobdiff_plain/9b7f0627eabba2aaeaf5d31b8a36a7dc2ff8d45a..bdd64093e5f8328c9d0c86597ef9fe0c2f5bba5a:/sql/functions.sql diff --git a/sql/functions.sql b/sql/functions.sql index 024d1caa..73790353 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -561,14 +561,6 @@ BEGIN RETURN nearcountry.country_code; END LOOP; --- RAISE WARNING 'natural earth: %', ST_AsText(place_centre); - - -- Natural earth data - FOR nearcountry IN select country_code from country_naturalearthdata where st_covers(geometry, place_centre) limit 1 - LOOP - RETURN nearcountry.country_code; - END LOOP; - -- RAISE WARNING 'near osm fallback: %', ST_AsText(place_centre); -- @@ -577,14 +569,6 @@ BEGIN RETURN nearcountry.country_code; END LOOP; --- RAISE WARNING 'near natural earth: %', ST_AsText(place_centre); - - -- Natural earth data - FOR nearcountry IN select country_code from country_naturalearthdata where st_dwithin(geometry, place_centre, 0.5) limit 1 - LOOP - RETURN nearcountry.country_code; - END LOOP; - RETURN NULL; END; $$ @@ -817,11 +801,12 @@ DECLARE i INTEGER; postcode TEXT; result BOOLEAN; + is_area BOOLEAN; country_code VARCHAR(2); default_language VARCHAR(10); diameter FLOAT; classtable TEXT; - line RECORD; + classtype TEXT; BEGIN --DEBUG: RAISE WARNING '% % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type; @@ -848,144 +833,74 @@ BEGIN IF NEW.osm_type = 'X' THEN -- E'X'ternal records should already be in the right format so do nothing ELSE - NEW.rank_search := 30; - NEW.rank_address := NEW.rank_search; - - -- By doing in postgres we have the country available to us - currently only used for postcode - IF NEW.class in ('place','boundary') AND NEW.type in ('postcode','postal_code') THEN + is_area := ST_GeometryType(NEW.geometry) IN ('ST_Polygon','ST_MultiPolygon'); - IF NEW.address IS NULL OR NOT NEW.address ? 'postcode' THEN - -- most likely just a part of a multipolygon postcode boundary, throw it away - RETURN NULL; - END IF; + IF NEW.class in ('place','boundary') + AND NEW.type in ('postcode','postal_code') THEN - NEW.name := hstore('ref', NEW.address->'postcode'); + IF NEW.address IS NULL OR NOT NEW.address ? 'postcode' THEN + -- most likely just a part of a multipolygon postcode boundary, throw it away + RETURN NULL; + END IF; - SELECT * FROM get_postcode_rank(NEW.country_code, NEW.address->'postcode') - INTO NEW.rank_search, NEW.rank_address; + NEW.name := hstore('ref', NEW.address->'postcode'); - IF NOT ST_GeometryType(NEW.geometry) IN ('ST_Polygon','ST_MultiPolygon') THEN - NEW.rank_address := 0; - END IF; + SELECT * FROM get_postcode_rank(NEW.country_code, NEW.address->'postcode') + INTO NEW.rank_search, NEW.rank_address; - ELSEIF NEW.class = 'place' THEN - IF NEW.type in ('continent') THEN - NEW.rank_search := 2; - NEW.rank_address := NEW.rank_search; - NEW.country_code := NULL; - ELSEIF NEW.type in ('sea') THEN - NEW.rank_search := 2; - NEW.rank_address := 0; - NEW.country_code := NULL; - ELSEIF NEW.type in ('country') THEN - NEW.rank_search := 4; - NEW.rank_address := NEW.rank_search; - ELSEIF NEW.type in ('state') THEN - NEW.rank_search := 8; - NEW.rank_address := NEW.rank_search; - ELSEIF NEW.type in ('region') THEN - NEW.rank_search := 18; -- dropped from previous value of 10 - NEW.rank_address := 0; -- So badly miss-used that better to just drop it! - ELSEIF NEW.type in ('county') THEN - NEW.rank_search := 12; - NEW.rank_address := NEW.rank_search; - ELSEIF NEW.type in ('city') THEN - NEW.rank_search := 16; - NEW.rank_address := NEW.rank_search; - ELSEIF NEW.type in ('island') THEN - NEW.rank_search := 17; - NEW.rank_address := 0; - ELSEIF NEW.type in ('town') THEN - NEW.rank_search := 18; - NEW.rank_address := 16; - ELSEIF NEW.type in ('village','hamlet','municipality','district','unincorporated_area','borough') THEN - NEW.rank_search := 19; - NEW.rank_address := 16; - ELSEIF NEW.type in ('suburb','croft','subdivision','isolated_dwelling') THEN - NEW.rank_search := 20; - NEW.rank_address := NEW.rank_search; - ELSEIF NEW.type in ('farm','locality','islet','mountain_pass') THEN - NEW.rank_search := 20; - NEW.rank_address := 0; - -- Irish townlands, tagged as place=locality and locality=townland - IF (NEW.extratags -> 'locality') = 'townland' THEN - NEW.rank_address := 20; - END IF; - ELSEIF NEW.type in ('neighbourhood') THEN - NEW.rank_search := 22; - NEW.rank_address := 22; - ELSEIF NEW.type in ('house','building') THEN - NEW.rank_search := 30; - NEW.rank_address := NEW.rank_search; - ELSEIF NEW.type in ('houses') THEN - -- can't guarantee all required nodes loaded yet due to caching in osm2pgsql - NEW.rank_search := 28; - NEW.rank_address := 0; + IF NOT is_area THEN + NEW.rank_address := 0; END IF; - - ELSEIF NEW.class = 'boundary' THEN - IF ST_GeometryType(NEW.geometry) NOT IN ('ST_Polygon','ST_MultiPolygon') THEN --- RAISE WARNING 'invalid boundary %',NEW.osm_id; + ELSEIF NEW.class = 'boundary' AND NOT is_area THEN return NULL; - END IF; - NEW.rank_search := NEW.admin_level * 2; - IF NEW.type = 'administrative' THEN - NEW.rank_address := NEW.rank_search; + 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 + NEW.rank_search = 30; + NEW.rank_address = 0; + ELSEIF NEW.class = 'landuse' AND NOT is_area THEN + NEW.rank_search = 30; + NEW.rank_address = 0; + ELSE + -- do table lookup stuff + IF NEW.class = 'boundary' and NEW.type = 'administrative' THEN + classtype = NEW.type || NEW.admin_level::TEXT; ELSE - NEW.rank_address := 0; + classtype = NEW.type; END IF; - ELSEIF NEW.class = 'landuse' AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN - NEW.rank_search := 22; - IF NEW.type in ('residential', 'farm', 'farmyard', 'industrial', 'commercial', 'allotments', 'retail') THEN - NEW.rank_address := NEW.rank_search; - ELSE - NEW.rank_address := 0; + SELECT l.rank_search, l.rank_address FROM address_levels l + WHERE (l.country_code = NEW.country_code or l.country_code is NULL) + AND l.class = NEW.class AND (l.type = classtype or l.type is NULL) + ORDER BY l.country_code, l.class, l.type LIMIT 1 + INTO NEW.rank_search, NEW.rank_address; + + IF NEW.rank_search is NULL THEN + NEW.rank_search := 30; END IF; - ELSEIF NEW.class = 'leisure' and NEW.type in ('park') THEN - NEW.rank_search := 24; - NEW.rank_address := 0; - ELSEIF NEW.class = 'natural' and NEW.type in ('peak','volcano','mountain_range') THEN - NEW.rank_search := 18; - NEW.rank_address := 0; - ELSEIF NEW.class = 'natural' and NEW.type = 'sea' THEN - NEW.rank_search := 4; - NEW.rank_address := NEW.rank_search; - -- any feature more than 5 square miles is probably worth indexing - ELSEIF ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_Area(NEW.geometry) > 0.1 THEN - NEW.rank_search := 22; - NEW.rank_address := 0; - ELSEIF NEW.class = 'railway' AND NEW.type in ('rail') THEN - RETURN NULL; - ELSEIF NEW.class = 'waterway' THEN - IF NEW.osm_type = 'R' THEN - NEW.rank_search := 16; - ELSE - NEW.rank_search := 17; + + IF NEW.rank_address is NULL THEN + NEW.rank_address := 30; END IF; - NEW.rank_address := 0; - ELSEIF NEW.class = 'highway' AND NEW.osm_type != 'N' AND NEW.type in ('service','cycleway','path','footway','steps','bridleway','motorway_link','primary_link','trunk_link','secondary_link','tertiary_link') THEN - NEW.rank_search := 27; - NEW.rank_address := NEW.rank_search; - ELSEIF NEW.class = 'highway' AND NEW.osm_type != 'N' THEN - NEW.rank_search := 26; - NEW.rank_address := NEW.rank_search; - ELSEIF NEW.class = 'mountain_pass' THEN - NEW.rank_search := 20; - NEW.rank_address := 0; END IF; - END IF; - - IF NEW.rank_search > 30 THEN - NEW.rank_search := 30; - END IF; + -- some postcorrections + IF NEW.class = 'place' THEN + IF NEW.type in ('continent', 'sea', 'country', 'state') AND NEW.osm_type = 'N' THEN + NEW.rank_address := 0; + END IF; + ELSEIF NEW.class = 'waterway' AND NEW.osm_type = 'R' THEN + -- Slightly promote waterway relations so that they are processed + -- before their members. + NEW.rank_search := NEW.rank_search - 1; + END IF; - IF NEW.rank_address > 30 THEN - NEW.rank_address := 30; - END IF; + IF (NEW.extratags -> 'capital') = 'yes' THEN + NEW.rank_search := NEW.rank_search - 1; + END IF; - IF (NEW.extratags -> 'capital') = 'yes' THEN - NEW.rank_search := NEW.rank_search - 1; END IF; -- a country code make no sense below rank 4 (country) @@ -1306,6 +1221,9 @@ BEGIN NEW.indexed_date = now(); + IF NOT %REVERSE-ONLY% THEN + DELETE from search_name WHERE place_id = NEW.place_id; + END IF; result := deleteSearchName(NEW.partition, NEW.place_id); DELETE FROM place_addressline WHERE place_id = NEW.place_id; result := deleteRoad(NEW.partition, NEW.place_id); @@ -1393,10 +1311,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; @@ -1572,8 +1486,9 @@ BEGIN IF NEW.parent_place_id IS NOT NULL THEN -- Get the details of the parent road - select s.country_code, s.name_vector, s.nameaddress_vector from search_name s - where s.place_id = NEW.parent_place_id INTO location; + SELECT p.country_code, p.postcode FROM placex p + WHERE p.place_id = NEW.parent_place_id INTO location; + NEW.country_code := location.country_code; --DEBUG: RAISE WARNING 'Got parent details from search name'; @@ -1582,7 +1497,7 @@ BEGIN IF NEW.address is not null AND NEW.address ? 'postcode' THEN NEW.postcode = upper(trim(NEW.address->'postcode')); ELSE - SELECT postcode FROM placex WHERE place_id = NEW.parent_place_id INTO NEW.postcode; + NEW.postcode := location.postcode; END IF; IF NEW.postcode is null THEN NEW.postcode := get_nearest_postcode(NEW.country_code, place_centroid); @@ -1595,21 +1510,34 @@ BEGIN return NEW; END IF; - -- Merge address from parent - nameaddress_vector := array_merge(nameaddress_vector, location.nameaddress_vector); - nameaddress_vector := array_merge(nameaddress_vector, location.name_vector); - -- Performance, it would be more acurate to do all the rest of the import -- process but it takes too long -- Just be happy with inheriting from parent road only - IF NEW.rank_search <= 25 and NEW.rank_address > 0 THEN result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, upper(trim(NEW.address->'postcode')), NEW.geometry); --DEBUG: RAISE WARNING 'Place added to location table'; END IF; - result := insertSearchName(NEW.partition, NEW.place_id, NEW.country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry); - --DEBUG: RAISE WARNING 'Place added to search table'; + result := insertSearchName(NEW.partition, NEW.place_id, name_vector, + NEW.rank_search, NEW.rank_address, NEW.geometry); + + IF NOT %REVERSE-ONLY% THEN + -- Merge address from parent + SELECT s.name_vector, s.nameaddress_vector FROM search_name s + WHERE s.place_id = NEW.parent_place_id INTO location; + + nameaddress_vector := array_merge(nameaddress_vector, + location.nameaddress_vector); + nameaddress_vector := array_merge(nameaddress_vector, location.name_vector); + + INSERT INTO search_name (place_id, search_rank, address_rank, + importance, country_code, name_vector, + nameaddress_vector, centroid) + VALUES (NEW.place_id, NEW.rank_search, NEW.rank_address, + NEW.importance, NEW.country_code, name_vector, + nameaddress_vector, place_centroid); + --DEBUG: RAISE WARNING 'Place added to search table'; + END IF; return NEW; END IF; @@ -1795,9 +1723,11 @@ BEGIN IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN isin_tokens := isin_tokens || address_street_word_id; END IF; - address_street_word_id := get_word_id(make_standard_name(addr_item.value)); - IF address_street_word_id IS NOT NULL THEN - nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); + IF NOT %REVERSE-ONLY% THEN + address_street_word_id := get_word_id(make_standard_name(addr_item.value)); + IF address_street_word_id IS NOT NULL THEN + nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); + END IF; END IF; END IF; IF addr_item.key = 'is_in' THEN @@ -1811,16 +1741,20 @@ BEGIN END IF; -- merge word into address vector - address_street_word_id := get_word_id(make_standard_name(isin[i])); - IF address_street_word_id IS NOT NULL THEN - nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); + IF NOT %REVERSE-ONLY% THEN + address_street_word_id := get_word_id(make_standard_name(isin[i])); + IF address_street_word_id IS NOT NULL THEN + nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); + END IF; END IF; END LOOP; END IF; END IF; END LOOP; END IF; - nameaddress_vector := array_merge(nameaddress_vector, isin_tokens); + IF NOT %REVERSE-ONLY% THEN + nameaddress_vector := array_merge(nameaddress_vector, isin_tokens); + END IF; -- RAISE WARNING 'ISIN: %', isin_tokens; @@ -1869,7 +1803,7 @@ BEGIN -- RAISE WARNING '% isaddress: %', location.place_id, location_isaddress; -- Add it to the list of search terms - IF location.rank_search > 4 THEN + IF NOT %REVERSE-ONLY% AND location.rank_search > 4 THEN nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]); END IF; INSERT INTO place_addressline (place_id, address_place_id, fromarea, isaddress, distance, cached_rank_address) @@ -1923,8 +1857,18 @@ BEGIN --DEBUG: RAISE WARNING 'insert into road location table (full)'; END IF; - result := insertSearchName(NEW.partition, NEW.place_id, NEW.country_code, name_vector, nameaddress_vector, NEW.rank_search, NEW.rank_address, NEW.importance, place_centroid, NEW.geometry); - --DEBUG: RAISE WARNING 'added to serach name (full)'; + result := insertSearchName(NEW.partition, NEW.place_id, name_vector, + NEW.rank_search, NEW.rank_address, NEW.geometry); + --DEBUG: RAISE WARNING 'added to search name (full)'; + + IF NOT %REVERSE-ONLY% THEN + INSERT INTO search_name (place_id, search_rank, address_rank, + importance, country_code, name_vector, + nameaddress_vector, centroid) + VALUES (NEW.place_id, NEW.rank_search, NEW.rank_address, + NEW.importance, NEW.country_code, name_vector, + nameaddress_vector, place_centroid); + END IF; END IF; @@ -1983,6 +1927,9 @@ BEGIN --DEBUG: RAISE WARNING 'placex_delete:09 % %',OLD.osm_type,OLD.osm_id; IF OLD.name is not null THEN + IF NOT %REVERSE-ONLY% THEN + DELETE from search_name WHERE place_id = OLD.place_id; + END IF; b := deleteSearchName(OLD.partition, OLD.place_id); END IF; @@ -2354,6 +2301,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 @@ -2371,50 +2321,66 @@ DECLARE 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. + -- 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 @@ -2424,52 +2390,51 @@ 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, + 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 @@ -2478,49 +2443,49 @@ BEGIN 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; - 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, true, true, 5, 0)::addressline; RETURN NEXT location; END IF; @@ -2530,96 +2495,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 $$