X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/a293328f11ecfd9640cc35c530e5137780dd0f8f..1b51a5abebd03284924b69058ecba9dbdff11acb:/sql/functions.sql diff --git a/sql/functions.sql b/sql/functions.sql index 1af5fdbf..0cb6d32a 100644 --- a/sql/functions.sql +++ b/sql/functions.sql @@ -345,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 @@ -407,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 @@ -637,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 @@ -704,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; @@ -782,8 +794,8 @@ CREATE OR REPLACE FUNCTION create_interpolation(wayid BIGINT, interpolationtype DECLARE newpoints INTEGER; - waynodes integer[]; - nodeid INTEGER; + waynodes BIGINT[]; + nodeid BIGINT; prevnode RECORD; nextnode RECORD; startnumber INTEGER; @@ -811,10 +823,10 @@ BEGIN FOR nodeidpos in 1..array_upper(waynodes, 1) LOOP - select min(place_id) from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::INTEGER and type = 'house' INTO search_place_id; + select min(place_id) from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT and type = 'house' INTO search_place_id; IF search_place_id IS NULL THEN -- null record of right type - select * from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::INTEGER and type = 'house' limit 1 INTO nextnode; + select * from placex where osm_type = 'N' and osm_id = waynodes[nodeidpos]::BIGINT and type = 'house' limit 1 INTO nextnode; select ST_SetSRID(ST_Point(lon::float/10000000,lat::float/10000000),4326) from planet_osm_nodes where id = waynodes[nodeidpos] INTO nextnode.geometry; IF nextnode.geometry IS NULL THEN -- we don't have any information about this point, most likely @@ -1143,6 +1155,9 @@ BEGIN NEW.rank_address := NEW.rank_search; ELSEIF NEW.class = 'natural' and NEW.type in ('coastline') THEN RETURN NULL; + ELSEIF NEW.class = 'mountain_pass' THEN + NEW.rank_search := 20; + NEW.rank_address := 0; END IF; END IF; @@ -1294,7 +1309,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; @@ -1308,23 +1323,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 @@ -1406,7 +1423,7 @@ BEGIN 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 relation %',relation; - SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::integer + SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint and rank_search = 26 INTO NEW.parent_place_id; END IF; END LOOP; @@ -1435,7 +1452,7 @@ BEGIN 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)::integer + SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint and rank_search = 26 INTO NEW.parent_place_id; END IF; END LOOP; @@ -1480,7 +1497,7 @@ BEGIN 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 '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)::integer + SELECT place_id from placex where osm_type='W' and osm_id = substring(relation.members[i],2,200)::bigint and rank_search = 26 INTO NEW.parent_place_id; END IF; END LOOP; @@ -1573,7 +1590,7 @@ BEGIN -- merge in the label name, re-init word vector IF NOT linkedPlacex.name IS NULL THEN NEW.name := linkedPlacex.name || NEW.name; - name_vector := make_keywords(NEW.name); + name_vector := array_merge(name_vector, make_keywords(linkedPlacex.name)); END IF; -- merge in extra tags @@ -1581,6 +1598,10 @@ BEGIN NEW.extratags := linkedPlacex.extratags || NEW.extratags; END IF; + IF NOT NEW.extratags ? linkedPlacex.class THEN + NEW.extratags := NEW.extratags || hstore(linkedPlacex.class, linkedPlacex.type); + END IF; + -- mark the linked place (excludes from search results) UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; @@ -1600,7 +1621,6 @@ BEGIN IF make_standard_name(NEW.name->'name') = make_standard_name(linkedPlacex.name->'name') AND NEW.rank_address = linkedPlacex.rank_address THEN - -- If we don't already have one use this as the centre point of the geometry IF NEW.centroid IS NULL THEN NEW.centroid := coalesce(linkedPlacex.centroid,st_centroid(linkedPlacex.geometry)); @@ -1617,6 +1637,10 @@ BEGIN NEW.extratags := linkedPlacex.extratags || NEW.extratags; END IF; + IF NOT NEW.extratags ? linkedPlacex.class THEN + NEW.extratags := NEW.extratags || hstore(linkedPlacex.class, linkedPlacex.type); + END IF; + -- mark the linked place (excludes from search results) UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; @@ -1654,6 +1678,10 @@ BEGIN -- merge in extra tags NEW.extratags := linkedPlacex.extratags || NEW.extratags; + IF NOT NEW.extratags ? linkedPlacex.class THEN + NEW.extratags := NEW.extratags || hstore(linkedPlacex.class, linkedPlacex.type); + END IF; + -- mark the linked place (excludes from search results) UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; @@ -1664,6 +1692,19 @@ BEGIN IF NEW.centroid IS NOT NULL THEN place_centroid := NEW.centroid; + -- Place might have had only a name tag before but has now received translations + -- from the linked place. Make sure a name tag for the default language exists in + -- this case. + IF NEW.name is not null AND array_upper(akeys(NEW.name),1) > 1 THEN + default_language := get_country_language_code(NEW.calculated_country_code); + IF default_language IS NOT NULL THEN + IF NEW.name ? 'name' AND NOT NEW.name ? ('name:'||default_language) THEN + NEW.name := NEW.name || hstore(('name:'||default_language), (NEW.name -> 'name')); + ELSEIF NEW.name ? ('name:'||default_language) AND NOT NEW.name ? 'name' THEN + NEW.name := NEW.name || hstore('name', (NEW.name -> ('name:'||default_language))); + END IF; + END IF; + END IF; END IF; -- Did we gain a wikipedia tag in the process? then we need to recalculate our importance @@ -1693,8 +1734,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; @@ -1704,11 +1752,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 @@ -1874,7 +1943,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; @@ -2279,7 +2348,7 @@ DROP TYPE addressline CASCADE; create type addressline as ( place_id BIGINT, osm_type CHAR(1), - osm_id INTEGER, + osm_id BIGINT, name HSTORE, class TEXT, type TEXT,