X-Git-Url: https://git.openstreetmap.org/nominatim.git/blobdiff_plain/caf018538fab83e3b5a32bbb5e5c41ec7045ead2..d9e0ef0ebfbc2860e7c0b7e7510a4014300074be:/sql/functions.sql diff --git a/sql/functions.sql b/sql/functions.sql index 3c64cf15..89c1f936 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,137 +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 = '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) @@ -1231,6 +1175,7 @@ DECLARE relation_members TEXT[]; relMember RECORD; linkedplacex RECORD; + addr_item RECORD; search_diameter FLOAT; search_prevdiameter FLOAT; search_maxrank INTEGER; @@ -1276,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); @@ -1307,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'); @@ -1352,7 +1300,7 @@ BEGIN --DEBUG: RAISE WARNING 'waterway parent %, child %/%', NEW.osm_id, i, relation_members[i]; FOR linked_node_id IN SELECT place_id FROM placex WHERE osm_type = 'W' and osm_id = substring(relation_members[i],2,200)::bigint - and class = NEW.class and type = NEW.type + and class = NEW.class and type in ('river', 'stream', 'canal', 'drain', 'ditch') and ( relation_members[i+1] != 'side_stream' or NEW.name->'name' = name->'name') LOOP UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = linked_node_id; @@ -1415,7 +1363,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') @@ -1452,9 +1400,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; @@ -1462,88 +1408,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; @@ -1552,7 +1490,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'; @@ -1561,7 +1501,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); @@ -1574,21 +1514,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; @@ -1754,7 +1707,7 @@ BEGIN END IF; -- make sure all names are in the word table - IF NEW.admin_level = 2 AND NEW.class = 'boundary' AND NEW.type = 'administrative' AND NEW.country_code IS NOT NULL THEN + IF NEW.admin_level = 2 AND NEW.class = 'boundary' AND NEW.type = 'administrative' AND NEW.country_code IS NOT NULL AND NEW.osm_type = 'R' THEN perform create_country(NEW.name, lower(NEW.country_code)); --DEBUG: RAISE WARNING 'Country names updated'; END IF; @@ -1763,44 +1716,49 @@ BEGIN parent_place_id_rank = 0; - -- convert isin to array of tokenids + -- convert address store to array of tokenids --DEBUG: RAISE WARNING 'Starting address search'; isin_tokens := '{}'::int[]; IF NEW.address IS NOT NULL THEN - isin := avals(NEW.address); - IF array_upper(isin, 1) IS NOT NULL THEN - FOR i IN 1..array_upper(isin, 1) LOOP - -- TODO further split terms with comma and semicolon - address_street_word_id := get_name_id(make_standard_name(isin[i])); + FOR addr_item IN SELECT * FROM each(NEW.address) + LOOP + IF addr_item.key IN ('city', 'tiger:county', 'state', 'suburb', 'province', 'district', 'region', 'county', 'municipality', 'hamlet', 'village', 'subdistrict', 'town', 'neighbourhood', 'quarter', 'parish') THEN + address_street_word_id := get_name_id(make_standard_name(addr_item.value)); IF address_street_word_id IS NOT NULL AND NOT(ARRAY[address_street_word_id] <@ isin_tokens) THEN - nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); isin_tokens := isin_tokens || address_street_word_id; 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(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 LOOP; - END IF; - END IF; + END IF; + IF addr_item.key = 'is_in' THEN + -- is_in items need splitting + isin := regexp_split_to_array(addr_item.value, E'[;,]'); + IF array_upper(isin, 1) IS NOT NULL THEN + FOR i IN 1..array_upper(isin, 1) LOOP + address_street_word_id := get_name_id(make_standard_name(isin[i])); + 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; - -- %NOTIGERDATA% IF 0 THEN - -- for the USA we have an additional address table. Merge in zip codes from there too - IF NEW.rank_search = 26 AND NEW.country_code = 'us' THEN - FOR location IN SELECT distinct postcode from location_property_tiger where parent_place_id = NEW.place_id LOOP - address_street_word_id := get_name_id(make_standard_name(location.postcode)); - nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); - isin_tokens := isin_tokens || address_street_word_id; - - -- also merge in the single word version - address_street_word_id := get_word_id(make_standard_name(location.postcode)); - nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); + -- merge word into address vector + 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; - --DEBUG: RAISE WARNING 'Tiger postcodes collected'; - -- %NOTIGERDATA% END IF; + IF NOT %REVERSE-ONLY% THEN + nameaddress_vector := array_merge(nameaddress_vector, isin_tokens); + END IF; -- RAISE WARNING 'ISIN: %', isin_tokens; @@ -1849,7 +1807,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) @@ -1881,67 +1839,6 @@ BEGIN END LOOP; --DEBUG: RAISE WARNING 'address computed'; - -- try using the isin value to find parent places - IF array_upper(isin_tokens, 1) IS NOT NULL THEN - FOR i IN 1..array_upper(isin_tokens, 1) LOOP ---RAISE WARNING ' getNearestNamedFeature: % % % %',NEW.partition, place_centroid, search_maxrank, isin_tokens[i]; - IF NOT ARRAY[isin_tokens[i]] <@ nameaddress_vector THEN - - FOR location IN SELECT * from getNearestNamedFeature(NEW.partition, place_centroid, search_maxrank, isin_tokens[i]) LOOP - ---RAISE WARNING ' ISIN: %',location; - - IF location.rank_search > 4 THEN - 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, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address); - IF NEW.postcode is null AND location.postcode is not null - AND NOT address_havelevel[location.rank_address] THEN - NEW.postcode := location.postcode; - END IF; - - address_havelevel[location.rank_address] := true; - - IF location.rank_address > parent_place_id_rank THEN - NEW.parent_place_id = location.place_id; - parent_place_id_rank = location.rank_address; - END IF; - END IF; - END LOOP; - - END IF; - - END LOOP; - END IF; - --DEBUG: RAISE WARNING 'isin tokens processed'; - - -- 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')); @@ -1964,8 +1861,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; @@ -2024,6 +1931,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; @@ -2197,7 +2107,9 @@ BEGIN -- To paraphrase, if there isn't an existing item, OR if the admin level has changed IF existingplacex.osm_type IS NULL OR - (coalesce(existingplacex.admin_level, 15) != coalesce(NEW.admin_level, 15) AND existingplacex.class = 'boundary' AND existingplacex.type = 'administrative') + (existingplacex.class = 'boundary' AND + ((coalesce(existingplacex.admin_level, 15) != coalesce(NEW.admin_level, 15) AND existingplacex.type = 'administrative') OR + (existingplacex.type != NEW.type))) THEN IF existingplacex.osm_type IS NOT NULL THEN @@ -2506,7 +2418,7 @@ BEGIN 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 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 LOOP