From: Sarah Hoffmann Date: Sun, 30 Sep 2012 07:51:54 +0000 (+0200) Subject: Merge remote-tracking branch 'lonvia/partial-search-terms' X-Git-Tag: v2.0.0~14 X-Git-Url: https://git.openstreetmap.org./nominatim.git/commitdiff_plain/ee2863200ccef14104cc0e7ac14d3cd8f0a1ce81?hp=-c Merge remote-tracking branch 'lonvia/partial-search-terms' --- ee2863200ccef14104cc0e7ac14d3cd8f0a1ce81 diff --combined sql/functions.sql index 035eaf90,7faadc16..2d7b029d --- a/sql/functions.sql +++ b/sql/functions.sql @@@ -83,18 -83,24 +83,24 @@@ END $$ LANGUAGE 'plpgsql' IMMUTABLE; + -- returns NULL if the word is too common CREATE OR REPLACE FUNCTION getorcreate_word_id(lookup_word TEXT) RETURNS INTEGER AS $$ DECLARE lookup_token TEXT; return_word_id INTEGER; + count INTEGER; BEGIN lookup_token := trim(lookup_word); - SELECT min(word_id) FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_id; + SELECT min(word_id), max(search_name_count) FROM word WHERE word_token = lookup_token and class is null and type is null into return_word_id, count; IF return_word_id IS NULL THEN return_word_id := nextval('seq_word'); INSERT INTO word VALUES (return_word_id, lookup_token, regexp_replace(lookup_token,E'([^0-9])\\1+',E'\\1','g'), null, null, null, null, 0, null); + ELSE + IF count > get_maxwordfreq() THEN + return_word_id := NULL; + END IF; END IF; RETURN return_word_id; END; @@@ -317,7 -323,7 +323,7 @@@ BEGI FOR j IN 1..array_upper(words, 1) LOOP IF (words[j] != '') THEN w = getorcreate_word_id(words[j]); - IF NOT (ARRAY[w] <@ result) THEN + IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN result := result || w; END IF; END IF; @@@ -330,7 -336,7 +336,7 @@@ s := make_standard_name(words[j]); IF s != '' THEN w := getorcreate_word_id(s); - IF NOT (ARRAY[w] <@ result) THEN + IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN result := result || w; END IF; END IF; @@@ -379,7 -385,7 +385,7 @@@ BEGI FOR j IN 1..array_upper(words, 1) LOOP IF (words[j] != '') THEN w = getorcreate_word_id(words[j]); - IF NOT (ARRAY[w] <@ result) THEN + IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN result := result || w; END IF; END IF; @@@ -392,7 -398,7 +398,7 @@@ s := make_standard_name(words[j]); IF s != '' THEN w := getorcreate_word_id(s); - IF NOT (ARRAY[w] <@ result) THEN + IF w IS NOT NULL AND NOT (ARRAY[w] <@ result) THEN result := result || w; END IF; END IF; @@@ -1068,9 -1074,6 +1074,9 @@@ BEGI ELSEIF NEW.class = 'landuse' AND ST_GeometryType(NEW.geometry) in ('ST_Polygon','ST_MultiPolygon') THEN NEW.rank_search := 22; NEW.rank_address := NEW.rank_search; + ELSEIF NEW.class = 'natural' and NEW.type in ('peak','volcano','mountain_range') THEN + NEW.rank_search := 18; + NEW.rank_address := 0; -- 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; @@@ -1096,6 -1099,9 +1102,6 @@@ NEW.rank_address := NEW.rank_search; ELSEIF NEW.class = 'natural' and NEW.type in ('coastline') THEN RETURN NULL; - ELSEIF NEW.class = 'natural' and NEW.type in ('peak','volcano') THEN - NEW.rank_search := 18; - NEW.rank_address := 0; END IF; END IF; @@@ -1502,76 -1508,74 +1508,76 @@@ BEGI select members from planet_osm_rels where id = NEW.osm_id INTO relation_members; -- RAISE WARNING 'get_osm_rel_members, label'; - FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['label']) as member LOOP + IF relation_members IS NOT NULL THEN + FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['label']) as member LOOP - FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1) - and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP + FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1) + and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP - -- 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)); - END IF; + -- 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)); + END IF; - -- 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); - END IF; + -- 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); + END IF; - -- merge in extra tags - IF NOT linkedPlacex.extratags IS NULL THEN - NEW.extratags := linkedPlacex.extratags || NEW.extratags; - END IF; + -- merge in extra tags + IF NOT linkedPlacex.extratags IS NULL THEN + NEW.extratags := linkedPlacex.extratags || NEW.extratags; + 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; + -- mark the linked place (excludes from search results) + UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; + + END LOOP; END LOOP; - END LOOP; + IF NEW.centroid IS NULL THEN - IF NEW.centroid IS NULL THEN + FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP - FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP + FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1) + and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP - FOR linkedPlacex IN select * from placex where osm_type = upper(substring(relMember.member,1,1))::char(1) - and osm_id = substring(relMember.member,2,10000)::bigint order by rank_search desc limit 1 LOOP + -- For an admin centre we also want a name match - still not perfect, for example 'new york, new york' + -- But that can be fixed by explicitly setting the label in the data + IF make_standard_name(NEW.name->'name') = make_standard_name(linkedPlacex.name->'name') + AND NEW.rank_address = linkedPlacex.rank_address THEN - -- For an admin centre we also want a name match - still not perfect, for example 'new york, new york' - -- But that can be fixed by explicitly setting the label in the data - 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)); + END IF; - -- 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)); - END IF; + -- merge in the name, re-init word vector + IF NOT linkedPlacex.name IS NULL THEN + NEW.name := linkedPlacex.name || NEW.name; + name_vector := make_keywords(NEW.name); + END IF; - -- merge in the name, re-init word vector - IF NOT linkedPlacex.name IS NULL THEN - NEW.name := linkedPlacex.name || NEW.name; - name_vector := make_keywords(NEW.name); - END IF; + -- merge in extra tags + IF NOT linkedPlacex.extratags IS NULL THEN + NEW.extratags := linkedPlacex.extratags || NEW.extratags; + END IF; - -- merge in extra tags - IF NOT linkedPlacex.extratags IS NULL THEN - NEW.extratags := linkedPlacex.extratags || NEW.extratags; - 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; - -- mark the linked place (excludes from search results) - UPDATE placex set linked_place_id = NEW.place_id where place_id = linkedPlacex.place_id; + -- keep a note of the node id in case we need it for wikipedia in a bit + linked_node_id := linkedPlacex.osm_id; + END IF; - -- keep a note of the node id in case we need it for wikipedia in a bit - linked_node_id := linkedPlacex.osm_id; - END IF; + END LOOP; END LOOP; - END LOOP; - + END IF; END IF; -- not found one yet? how about doing a name search @@@ -1699,24 -1703,21 +1705,24 @@@ 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 + FOR location IN SELECT * from getNearestNamedFeature(NEW.partition, place_centroid, search_maxrank, isin_tokens[i]) LOOP ---RAISE WARNING ' ISIN: %',location; + --RAISE WARNING ' ISIN: %',location; - nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]); - INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address); - address_havelevel[location.rank_address] := true; + nameaddress_vector := array_merge(nameaddress_vector, location.keywords::integer[]); + INSERT INTO place_addressline VALUES (NEW.place_id, location.place_id, false, NOT address_havelevel[location.rank_address], location.distance, location.rank_address); + 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; + 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 LOOP; + END LOOP; + + END IF; END LOOP; END IF;