$$
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;
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;
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;
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;
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;
result BOOLEAN;
BEGIN
+ -- deferred delete
+ IF OLD.indexed_status = 100 THEN
+ --DEBUG: RAISE WARNING 'placex_update_delete % %',NEW.osm_type,NEW.osm_id;
+ delete from placex where place_id = OLD.place_id;
+ RETURN NULL;
+ END IF;
+
IF NEW.indexed_status != 0 OR OLD.indexed_status = 0 OR NEW.linked_place_id is not null THEN
RETURN NEW;
END IF;
RETURN NEW;
END IF;
- -- deferred delete
- IF OLD.indexed_status = 100 THEN
- --DEBUG: RAISE WARNING 'placex_update_delete % %',NEW.osm_type,NEW.osm_id;
- delete from placex where place_id = OLD.place_id;
- RETURN NULL;
- END IF;
-
IF OLD.indexed_status != 0 THEN
--DEBUG: RAISE WARNING 'placex_update_0 % %',NEW.osm_type,NEW.osm_id;
-- RAISE WARNING 'get_osm_rel_members, label';
FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['label']) as member LOOP
- 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 into linkedPlacex;
+ 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
- NEW.name := linkedPlacex.name || NEW.name;
- name_vector := make_keywords(NEW.name);
+ -- 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
- NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+ -- 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;
FOR relMember IN select get_osm_rel_members(relation_members,ARRAY['admin_center','admin_centre']) as member LOOP
- 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 into linkedPlacex;
+ 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_search = linkedPlacex.rank_search 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
- NEW.name := linkedPlacex.name || NEW.name;
- name_vector := make_keywords(NEW.name);
+ -- 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
- NEW.extratags := linkedPlacex.extratags || NEW.extratags;
+ -- 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;
FOR linkedPlacex IN select placex.* from placex WHERE
make_standard_name(name->'name') = make_standard_name(NEW.name->'name')
- AND placex.rank_search = NEW.rank_search
+ AND placex.rank_address = NEW.rank_address
AND placex.place_id != NEW.place_id
AND placex.osm_type = 'N'::char(1) AND placex.rank_search < 26
AND st_covers(NEW.geometry, placex.geometry)
location_rank_search := 0;
location_distance := 0;
location_parent := NULL;
+ -- added ourself as address already
+ address_havelevel[NEW.rank_address] := true;
-- RAISE WARNING ' getNearFeatures(%,''%'',%,''%'')',NEW.partition, place_centroid, search_maxrank, isin_tokens;
FOR location IN SELECT * from getNearFeatures(NEW.partition, place_centroid, search_maxrank, isin_tokens) LOOP
BEGIN
-- RAISE WARNING 'placex_delete % %',OLD.osm_type,OLD.osm_id;
- update placex set linked_place_id = null where linked_place_id = OLD.place_id;
+ update placex set linked_place_id = null, indexed_status = 2 where linked_place_id = OLD.place_id and indexed_status = 0;
--DEBUG: RAISE WARNING 'placex_delete:01 % %',OLD.osm_type,OLD.osm_id;
- update placex set indexed_status = 2 where linked_place_id = OLD.place_id and indexed_status = 0;
+ update placex set linked_place_id = null where linked_place_id = OLD.place_id;
--DEBUG: RAISE WARNING 'placex_delete:02 % %',OLD.osm_type,OLD.osm_id;
IF OLD.rank_address < 30 THEN
projects / nominatim.git / blobdiff