X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/0eecadb091539b48fb4e0ef6857da3a913c2bd39..12c0944d939581e456045673b9387cde3681b0bf:/sql/functions.sql?ds=sidebyside diff --git a/sql/functions.sql b/sql/functions.sql index dfb64634..6fbdeb46 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -5,7 +5,6 @@ -- score integer --); - CREATE OR REPLACE FUNCTION getclasstypekey(c text, t text) RETURNS TEXT AS $$ DECLARE @@ -346,6 +345,12 @@ BEGIN result := result || w; END IF; + w := getorcreate_word_id(s); + + IF NOT (ARRAY[w] <@ result) THEN + result := result || w; + END IF; + words := string_to_array(s, ' '); IF array_upper(words, 1) IS NOT NULL THEN FOR j IN 1..array_upper(words, 1) LOOP @@ -408,6 +413,12 @@ BEGIN result := result || w; END IF; + w := getorcreate_word_id(s); + + IF NOT (ARRAY[w] <@ result) THEN + result := result || w; + END IF; + words := string_to_array(s, ' '); IF array_upper(words, 1) IS NOT NULL THEN FOR j IN 1..array_upper(words, 1) LOOP @@ -638,7 +649,7 @@ BEGIN -- RAISE WARNING 'Adding location with rank > 25 (% rank %)', place_id, rank_search; - x := deleteLocationArea(partition, place_id); + x := deleteLocationArea(partition, place_id, rank_search); isarea := false; IF (ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') AND ST_IsValid(geometry)) THEN @@ -705,7 +716,7 @@ CREATE OR REPLACE FUNCTION update_location( DECLARE b BOOLEAN; BEGIN - b := deleteLocationArea(partition, place_id); + b := deleteLocationArea(partition, place_id, rank_search); -- result := add_location(NEW.place_id, NEW.country_code, NEW.partition, name_vector, NEW.rank_search, NEW.rank_address, NEW.geometry); RETURN add_location(place_id, place_country_code, name, rank_search, rank_address, geometry); END; @@ -1295,7 +1306,7 @@ BEGIN RETURN NULL; END IF; - IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 OR NEW.linked_place_id is not null THEN + IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 THEN RETURN NEW; END IF; @@ -1309,23 +1320,25 @@ BEGIN RETURN NEW; END IF; + -- TODO: this test is now redundant? IF OLD.indexed_status != 0 THEN - --DEBUG: RAISE WARNING 'placex_update_0 % %',NEW.osm_type,NEW.osm_id; NEW.indexed_date = now(); - IF NEW.class = 'place' AND NEW.type = 'houses' THEN - i := create_interpolation(NEW.osm_id, NEW.housenumber); + result := deleteSearchName(NEW.partition, NEW.place_id); + DELETE FROM place_addressline WHERE place_id = NEW.place_id; + DELETE FROM place_boundingbox where place_id = NEW.place_id; + result := deleteRoad(NEW.partition, NEW.place_id); + result := deleteLocationArea(NEW.partition, NEW.place_id, NEW.rank_search); + UPDATE placex set linked_place_id = null where linked_place_id = NEW.place_id; + + IF NEW.linked_place_id is not null THEN RETURN NEW; END IF; - IF OLD.indexed_status > 0 THEN - result := deleteSearchName(NEW.partition, NEW.place_id); - DELETE FROM place_addressline WHERE place_id = NEW.place_id; - DELETE FROM place_boundingbox where place_id = NEW.place_id; - result := deleteRoad(NEW.partition, NEW.place_id); - result := deleteLocationArea(NEW.partition, NEW.place_id); - UPDATE placex set linked_place_id = null where linked_place_id = NEW.place_id; + IF NEW.class = 'place' AND NEW.type = 'houses' THEN + i := create_interpolation(NEW.osm_id, NEW.housenumber); + RETURN NEW; END IF; -- Speed up searches - just use the centroid of the feature @@ -1389,6 +1402,9 @@ BEGIN --RAISE WARNING 'finding street for %', NEW; + -- We won't get a better centroid, besides these places are too small to care + NEW.centroid := place_centroid; + NEW.parent_place_id := null; -- to do that we have to find our parent road @@ -1691,8 +1707,15 @@ BEGIN 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 + 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]); + END IF; END LOOP; END IF; END IF; @@ -1702,11 +1725,32 @@ BEGIN 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 + nameaddress_vector := array_merge(nameaddress_vector, ARRAY[address_street_word_id]); isin_tokens := isin_tokens || address_street_word_id; END IF; + + -- merge 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]); + END IF; END LOOP; END IF; END IF; + + -- for the USA we have an additional address table. Merge in zip codes from there too + IF NEW.rank_search = 26 AND NEW.calculated_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]); + END LOOP; + END IF; + -- RAISE WARNING 'ISIN: %', isin_tokens; -- Process area matches @@ -1872,7 +1916,7 @@ BEGIN --DEBUG: RAISE WARNING 'placex_delete:08 % %',OLD.osm_type,OLD.osm_id; IF OLD.rank_address < 26 THEN - b := deleteLocationArea(OLD.partition, OLD.place_id); + b := deleteLocationArea(OLD.partition, OLD.place_id, OLD.rank_search); END IF; --DEBUG: RAISE WARNING 'placex_delete:09 % %',OLD.osm_type,OLD.osm_id;