X-Git-Url: https://git.openstreetmap.org./nominatim.git/blobdiff_plain/cc61b74cdef1672fe391814fdc0dff5dbb3f0205..eed2e3f2a8d52c1729f41da435cc8ee397fdee94:/sql/functions/normalization.sql diff --git a/sql/functions/normalization.sql b/sql/functions/normalization.sql index 71b14ee5..8bb4915b 100644 --- a/sql/functions/normalization.sql +++ b/sql/functions/normalization.sql @@ -207,19 +207,31 @@ CREATE OR REPLACE FUNCTION addr_ids_from_name(lookup_word TEXT) RETURNS INTEGER[] AS $$ DECLARE - lookup_token TEXT; + words TEXT[]; id INTEGER; return_word_id INTEGER[]; + word_ids INTEGER[]; + j INTEGER; BEGIN - lookup_token := make_standard_name(lookup_word); - SELECT array_agg(word_id) FROM word - WHERE word_token = lookup_token and class is null and type is null - INTO return_word_id; - IF return_word_id IS NULL THEN - id := nextval('seq_word'); - INSERT INTO word VALUES (id, lookup_token, null, null, null, null, 0); - return_word_id = ARRAY[id]; + words := string_to_array(make_standard_name(lookup_word), ' '); + IF array_upper(words, 1) IS NOT NULL THEN + FOR j IN 1..array_upper(words, 1) LOOP + IF (words[j] != '') THEN + SELECT array_agg(word_id) INTO word_ids + FROM word + WHERE word_token = words[j] and class is null and type is null; + + IF word_ids IS NULL THEN + id := nextval('seq_word'); + INSERT INTO word VALUES (id, words[j], null, null, null, null, 0); + return_word_id := return_word_id || id; + ELSE + return_word_id := array_merge(return_word_id, word_ids); + END IF; + END IF; + END LOOP; END IF; + RETURN return_word_id; END; $$ @@ -414,10 +426,14 @@ $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION create_poi_search_terms(parent_place_id BIGINT, +CREATE OR REPLACE FUNCTION create_poi_search_terms(obj_place_id BIGINT, + in_partition SMALLINT, + parent_place_id BIGINT, address HSTORE, + country TEXT, housenumber TEXT, initial_name_vector INTEGER[], + geometry GEOMETRY, OUT name_vector INTEGER[], OUT nameaddress_vector INTEGER[]) AS $$ @@ -427,82 +443,82 @@ DECLARE addr_place_ids INTEGER[]; addr_item RECORD; + parent_address_place_ids BIGINT[]; + filtered_address HSTORE; BEGIN - -- Compute all search terms from the addr: tags. nameaddress_vector := '{}'::INTEGER[]; - IF address IS NOT NULL THEN - FOR addr_item IN SELECT * FROM each(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 - nameaddress_vector := array_merge(nameaddress_vector, - addr_ids_from_name(addr_item.value)); - END IF; - END LOOP; - END IF; - - -- If the POI is named, simply mix in all address terms and be done. - IF array_length(initial_name_vector, 1) is not NULL THEN - -- Cheating here by not recomputing all terms but simply using the ones - -- from the parent object. - SELECT array_merge(s.name_vector, s.nameaddress_vector) - INTO parent_address_vector - FROM search_name s - WHERE s.place_id = parent_place_id; + SELECT s.name_vector, s.nameaddress_vector + INTO parent_name_vector, parent_address_vector + FROM search_name s + WHERE s.place_id = parent_place_id; - name_vector := initial_name_vector; - nameaddress_vector := array_merge(nameaddress_vector, parent_address_vector); + -- Find all address tags that don't appear in the parent search names. + SELECT hstore(array_agg(ARRAY[k, v])) INTO filtered_address + FROM (SELECT skeys(address) as k, svals(address) as v) a + WHERE not addr_ids_from_name(v) && parent_address_vector + AND k not in ('country', 'street', 'place', 'postcode', + 'housenumber', 'streetnumber', 'conscriptionnumber'); - IF not address ? 'street' and address ? 'place' THEN - -- make sure addr:place terms are always searchable - nameaddress_vector := array_merge(nameaddress_vector, - addr_ids_from_name(address->'place')); - END IF; + -- Compute all search terms from the addr: tags. + IF filtered_address IS NOT NULL THEN + FOR addr_item IN + SELECT * FROM + get_places_for_addr_tags(in_partition, geometry, filtered_address, country) + LOOP + IF addr_item.place_id is null THEN + nameaddress_vector := array_merge(nameaddress_vector, + addr_item.keywords); + CONTINUE; + END IF; - RETURN; - END IF; + IF parent_address_place_ids is null THEN + SELECT array_agg(parent_place_id) INTO parent_address_place_ids + FROM place_addressline + WHERE place_id = parent_place_id; + END IF; - ----- unnamed POIS + IF not parent_address_place_ids @> ARRAY[addr_item.place_id] THEN + nameaddress_vector := array_merge(nameaddress_vector, + addr_item.keywords); - IF (array_length(nameaddress_vector, 1) is null - and (address ? 'street'or not address ? 'place')) - or housenumber is null - THEN - RETURN; + INSERT INTO place_addressline (place_id, address_place_id, fromarea, + isaddress, distance, cached_rank_address) + VALUES (obj_place_id, addr_item.place_id, not addr_item.isguess, + true, addr_item.distance, addr_item.rank_address); + END IF; + END LOOP; END IF; - SELECT s.name_vector, s.nameaddress_vector - INTO parent_name_vector, parent_address_vector - FROM search_name s - WHERE s.place_id = parent_place_id; + name_vector := initial_name_vector; -- Check if the parent covers all address terms. -- If not, create a search name entry with the house number as the name. -- This is unusual for the search_name table but prevents that the place -- is returned when we only search for the street/place. - IF not nameaddress_vector <@ parent_address_vector THEN - name_vector := ARRAY[getorcreate_name_id(housenumber)]; + IF housenumber is not null and not nameaddress_vector <@ parent_address_vector THEN + name_vector := array_merge(name_vector, + ARRAY[getorcreate_housenumber_id(make_standard_name(housenumber))]); END IF; IF not address ? 'street' and address ? 'place' THEN addr_place_ids := addr_ids_from_name(address->'place'); IF not addr_place_ids <@ parent_name_vector THEN - -- addr:place tag exists without a corresponding place. Mix in addr:place - -- in the address and drop the name from the parent. This would only be - -- the street name of the nearest street. + -- make sure addr:place terms are always searchable nameaddress_vector := array_merge(nameaddress_vector, addr_place_ids); - name_vector := ARRAY[getorcreate_name_id(housenumber)]; + -- If there is a housenumber, also add the place name as a name, + -- so we can search it by the usual housenumber+place algorithms. + IF housenumber is not null THEN + name_vector := array_merge(name_vector, + ARRAY[getorcreate_name_id(make_standard_name(address->'place'))]); + END IF; END IF; - ELSE - nameaddress_vector := array_merge(nameaddress_vector, parent_name_vector); END IF; - -- The address vector always gets merged in. + -- Cheating here by not recomputing all terms but simply using the ones + -- from the parent object. + nameaddress_vector := array_merge(nameaddress_vector, parent_name_vector); nameaddress_vector := array_merge(nameaddress_vector, parent_address_vector); END;