X-Git-Url: https://git.openstreetmap.org/nominatim.git/blobdiff_plain/080ba00956527cc0b2e95083b35aacaa62fb856e..bdd64093e5f8328c9d0c86597ef9fe0c2f5bba5a:/sql/functions.sql?ds=sidebyside diff --git a/sql/functions.sql b/sql/functions.sql index 16862acb..73790353 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -35,7 +35,7 @@ CREATE OR REPLACE FUNCTION make_standard_name(name TEXT) RETURNS TEXT DECLARE o TEXT; BEGIN - o := gettokenstring(transliteration(name)); + o := public.gettokenstring(public.transliteration(name)); RETURN trim(substr(o,1,length(o))); END; $$ @@ -256,6 +256,28 @@ END; $$ LANGUAGE plpgsql IMMUTABLE; +CREATE OR REPLACE FUNCTION reverse_place_diameter(rank_search SMALLINT) + RETURNS FLOAT + AS $$ +BEGIN + IF rank_search <= 4 THEN + RETURN 5.0; + ELSIF rank_search <= 8 THEN + RETURN 1.8; + ELSIF rank_search <= 12 THEN + RETURN 0.6; + ELSIF rank_search <= 17 THEN + RETURN 0.16; + ELSIF rank_search <= 18 THEN + RETURN 0.08; + ELSIF rank_search <= 19 THEN + RETURN 0.04; + END IF; + + RETURN 0.02; +END; +$$ +LANGUAGE plpgsql IMMUTABLE; CREATE OR REPLACE FUNCTION get_postcode_rank(country_code VARCHAR(2), postcode TEXT, OUT rank_search SMALLINT, OUT rank_address SMALLINT) @@ -539,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); -- @@ -555,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; $$ @@ -795,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; @@ -826,140 +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; + is_area := ST_GeometryType(NEW.geometry) IN ('ST_Polygon','ST_MultiPolygon'); - -- 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 + IF NEW.class in ('place','boundary') + AND NEW.type in ('postcode','postal_code') THEN - 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.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; - NEW.name := hstore('ref', NEW.address->'postcode'); + NEW.name := hstore('ref', NEW.address->'postcode'); - SELECT * FROM get_postcode_rank(NEW.country_code, NEW.address->'postcode') - INTO NEW.rank_search, NEW.rank_address; + 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) @@ -1280,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); @@ -1311,8 +1255,8 @@ BEGIN i := getorcreate_housenumber_id(make_standard_name(NEW.housenumber)); END IF; - addr_street = NEW.address->'street'; - addr_place = NEW.address->'place'; + addr_street := NEW.address->'street'; + addr_place := NEW.address->'place'; IF NEW.address ? 'postcode' and NEW.address->'postcode' not similar to '%(,|;)%' THEN i := getorcreate_postcode_id(NEW.address->'postcode'); @@ -1367,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; @@ -1419,7 +1359,7 @@ BEGIN -- see if we can get it from a surrounding building IF NEW.osm_type = 'N' AND addr_street IS NULL AND addr_place IS NULL AND NEW.housenumber IS NULL THEN - FOR location IN select * from placex where ST_Covers(geometry, place_centroid) + FOR location IN select address from placex where ST_Covers(geometry, place_centroid) and address is not null and (address ? 'housenumber' or address ? 'street' or address ? 'place') and rank_search > 28 AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') @@ -1456,9 +1396,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 - FOR location IN SELECT * from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP - NEW.parent_place_id := location.place_id; - END LOOP; + SELECT place_id from getNearestNamedRoadFeature(NEW.partition, place_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; @@ -1466,88 +1404,80 @@ 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 - FOR location IN SELECT * from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP - NEW.parent_place_id := location.place_id; - END LOOP; + SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, place_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; -- Is this node part of an interpolation? IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN - FOR location IN - SELECT q.parent_place_id FROM location_property_osmline q, planet_osm_ways x - WHERE q.linegeo && NEW.geometry and x.id = q.osm_id and NEW.osm_id = any(x.nodes) - LIMIT 1 - LOOP - NEW.parent_place_id := location.parent_place_id; - END LOOP; + SELECT q.parent_place_id FROM location_property_osmline q, planet_osm_ways x + WHERE q.linegeo && NEW.geometry and x.id = q.osm_id and NEW.osm_id = any(x.nodes) + LIMIT 1 INTO NEW.parent_place_id; END IF; --DEBUG: RAISE WARNING 'Checked for interpolation (%)', NEW.parent_place_id; -- Is this node part of a way? IF NEW.parent_place_id IS NULL AND NEW.osm_type = 'N' THEN - FOR location IN select p.place_id, p.osm_id, p.parent_place_id, p.rank_search, p.address from placex p, planet_osm_ways w - where p.osm_type = 'W' and p.rank_search >= 26 and p.geometry && NEW.geometry and w.id = p.osm_id and NEW.osm_id = any(w.nodes) + FOR location IN + SELECT p.place_id, p.osm_id, p.rank_search, p.address from placex p, planet_osm_ways w + WHERE p.osm_type = 'W' and p.rank_search >= 26 and p.geometry && NEW.geometry and w.id = p.osm_id and NEW.osm_id = any(w.nodes) LOOP --DEBUG: RAISE WARNING 'Node is part of way % ', location.osm_id; -- Way IS a road then we are on it - that must be our road - IF location.rank_search < 28 AND NEW.parent_place_id IS NULL THEN + IF location.rank_search < 28 THEN --RAISE WARNING 'node in way that is a street %',location; NEW.parent_place_id := location.place_id; + EXIT; END IF; --DEBUG: RAISE WARNING 'Checked if way is street (%)', NEW.parent_place_id; -- If the way mentions a street or place address, try that for parenting. - IF NEW.parent_place_id IS NULL AND 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 - FOR linkedplacex IN SELECT place_id from getNearestNamedRoadFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP - NEW.parent_place_id := linkedplacex.place_id; - END LOOP; + IF location.address is not null THEN + 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; + EXIT WHEN NEW.parent_place_id is not NULL; + END IF; END IF; - END IF; - --DEBUG: RAISE WARNING 'Checked for addr:street in way (%)', NEW.parent_place_id; + --DEBUG: RAISE WARNING 'Checked for addr:street in way (%)', NEW.parent_place_id; - IF NEW.parent_place_id IS NULL AND 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 - FOR linkedplacex IN SELECT place_id from getNearestNamedPlaceFeature(NEW.partition, place_centroid, address_street_word_ids) LOOP - NEW.parent_place_id := linkedplacex.place_id; - END LOOP; + 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; + EXIT WHEN NEW.parent_place_id is not NULL; + END IF; END IF; - END IF; --DEBUG: RAISE WARNING 'Checked for addr:place in way (%)', NEW.parent_place_id; + END IF; -- Is the WAY part of a relation - IF NEW.parent_place_id IS NULL THEN - FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id] and members @> ARRAY['w'||location.osm_id] - LOOP - -- At the moment we only process one type of relation - associatedStreet - IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN - FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP - IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN - --RAISE WARNING 'node in way that is in a relation %',relation; - SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint - and rank_search = 26 and name is not null INTO NEW.parent_place_id; - END IF; - END LOOP; + FOR relation IN select * from planet_osm_rels where parts @> ARRAY[location.osm_id] and members @> ARRAY['w'||location.osm_id] + LOOP + -- At the moment we only process one type of relation - associatedStreet + IF relation.tags @> ARRAY['associatedStreet'] AND array_upper(relation.members, 1) IS NOT NULL THEN + FOR i IN 1..array_upper(relation.members, 1) BY 2 LOOP + IF NEW.parent_place_id IS NULL AND relation.members[i+1] = 'street' THEN +--RAISE WARNING 'node in way that is in a relation %',relation; + SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint + and rank_search = 26 and name is not null INTO NEW.parent_place_id; END IF; END LOOP; - END IF; + END IF; + END LOOP; + EXIT WHEN NEW.parent_place_id is not null; --DEBUG: RAISE WARNING 'Checked for street relation in way (%)', NEW.parent_place_id; END LOOP; - END IF; -- Still nothing, just use the nearest road IF NEW.parent_place_id IS NULL THEN - FOR location IN SELECT place_id FROM getNearestRoadFeature(NEW.partition, place_centroid) LOOP - NEW.parent_place_id := location.place_id; - END LOOP; + SELECT place_id FROM getNearestRoadFeature(NEW.partition, place_centroid) INTO NEW.parent_place_id; END IF; --DEBUG: RAISE WARNING 'Checked for nearest way (%)', NEW.parent_place_id; @@ -1556,7 +1486,9 @@ BEGIN IF NEW.parent_place_id IS NOT NULL THEN -- Get the details of the parent road - select * from search_name where 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'; @@ -1565,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); @@ -1578,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; @@ -1778,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 @@ -1794,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; @@ -1852,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) @@ -1884,33 +1835,6 @@ BEGIN END LOOP; --DEBUG: RAISE WARNING 'address computed'; - -- for long ways we should add search terms for the entire length - IF st_length(NEW.geometry) > 0.05 THEN - - location_rank_search := 0; - location_distance := 0; - - FOR location IN SELECT * from getNearFeatures(NEW.partition, NEW.geometry, search_maxrank, isin_tokens) LOOP - - IF location.rank_address != location_rank_search THEN - location_rank_search := location.rank_address; - location_distance := location.distance * 1.5; - END IF; - - IF location.rank_search > 4 AND location.distance < location_distance THEN - - -- Add it to the list of search terms - nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]); - INSERT INTO place_addressline (place_id, address_place_id, fromarea, isaddress, distance, cached_rank_address) - VALUES (NEW.place_id, location.place_id, true, false, location.distance, location.rank_address); - - END IF; - - END LOOP; - - END IF; - --DEBUG: RAISE WARNING 'search terms for long ways added'; - IF NEW.address is not null AND NEW.address ? 'postcode' AND NEW.address->'postcode' not similar to '%(,|;)%' THEN NEW.postcode := upper(trim(NEW.address->'postcode')); @@ -1933,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; @@ -1993,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; @@ -2364,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 @@ -2381,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 @@ -2434,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 @@ -2488,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; @@ -2540,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 $$