-- Trigger functions for the placex table.
-CREATE OR REPLACE FUNCTION get_rel_node_members(members TEXT[], memberLabels TEXT[])
- RETURNS SETOF BIGINT
- AS $$
-DECLARE
- i INTEGER;
-BEGIN
- FOR i IN 1..ARRAY_UPPER(members,1) BY 2 LOOP
- IF members[i+1] = ANY(memberLabels)
- AND upper(substring(members[i], 1, 1))::char(1) = 'N'
- THEN
- RETURN NEXT substring(members[i], 2)::bigint;
- END IF;
- END LOOP;
-
- RETURN;
-END;
-$$
-LANGUAGE plpgsql IMMUTABLE;
-
--- copy 'name' to or from the default language (if there is a default language)
-CREATE OR REPLACE FUNCTION add_default_place_name(country_code VARCHAR(2),
- INOUT name HSTORE)
- AS $$
-DECLARE
- default_language VARCHAR(10);
-BEGIN
- IF name is not null AND array_upper(akeys(name),1) > 1 THEN
- default_language := get_country_language_code(country_code);
- IF default_language IS NOT NULL THEN
- IF name ? 'name' AND NOT name ? ('name:'||default_language) THEN
- name := name || hstore(('name:'||default_language), (name -> 'name'));
- ELSEIF name ? ('name:'||default_language) AND NOT name ? 'name' THEN
- name := name || hstore('name', (name -> ('name:'||default_language)));
- END IF;
- END IF;
- END IF;
-END;
-$$
-LANGUAGE plpgsql IMMUTABLE;
-
-- Find the parent road of a POI.
--
-- \returns Place ID of parent object or NULL if none
END LOOP;
END IF;
- -- Search for relation members with role admin_center.
- IF bnd.osm_type = 'R' and bnd_name is not null
- and relation_members is not null
- THEN
- FOR rel_member IN
- SELECT get_rel_node_members(relation_members,
- ARRAY['admin_center','admin_centre']) as member
+ IF bnd.extratags ? 'wikidata' THEN
+ FOR linked_placex IN
+ SELECT * FROM placex
+ WHERE placex.class = 'place' AND placex.osm_type = 'N'
+ AND placex.extratags ? 'wikidata' -- needed to select right index
+ AND placex.extratags->'wikidata' = bnd.extratags->'wikidata'
+ AND placex.rank_search < 26
+ AND _st_covers(bnd.geometry, placex.geometry)
+ ORDER BY make_standard_name(name->'name') = bnd_name desc
LOOP
- --DEBUG: RAISE WARNING 'Found admin_center member %', rel_member.member;
- FOR linked_placex IN
- SELECT * from placex
- WHERE osm_type = 'N' and osm_id = rel_member.member
- and class = 'place'
- 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 bnd_name = make_standard_name(linked_placex.name->'name')
- AND bnd.rank_address = linked_placex.rank_address
- THEN
- RETURN linked_placex;
- END IF;
- --DEBUG: RAISE WARNING 'Linked admin_center';
- END LOOP;
+ --DEBUG: RAISE WARNING 'Found wikidata-matching place node %', linked_placex.osm_id;
+ RETURN linked_placex;
END LOOP;
END IF;
FOR linked_placex IN
SELECT placex.* from placex
WHERE make_standard_name(name->'name') = bnd_name
- AND placex.rank_address = bnd.rank_address
+ AND ((bnd.rank_address > 0 and placex.rank_address = bnd.rank_address)
+ OR (bnd.rank_address = 0 and placex.rank_search = bnd.rank_search))
AND placex.osm_type = 'N'
AND placex.rank_search < 26 -- needed to select the right index
AND _st_covers(bnd.geometry, placex.geometry)
END IF;
ELSE
-- mark nearby items for re-indexing, where 'nearby' depends on the features rank_search and is a complete guess :(
- diameter := 0;
- -- 16 = city, anything higher than city is effectively ignored (polygon required!)
- IF NEW.type='postcode' THEN
- diameter := 0.05;
- ELSEIF NEW.rank_search < 16 THEN
- diameter := 0;
- ELSEIF NEW.rank_search < 18 THEN
- diameter := 0.1;
- ELSEIF NEW.rank_search < 20 THEN
- diameter := 0.05;
- ELSEIF NEW.rank_search = 21 THEN
- diameter := 0.001;
- ELSEIF NEW.rank_search < 24 THEN
- diameter := 0.02;
- ELSEIF NEW.rank_search < 26 THEN
- diameter := 0.002; -- 100 to 200 meters
- ELSEIF NEW.rank_search < 28 THEN
- diameter := 0.001; -- 50 to 100 meters
- END IF;
+ diameter := update_place_diameter(NEW.rank_search);
IF diameter > 0 THEN
-- RAISE WARNING 'placex point insert: % % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type,diameter;
IF NEW.rank_search >= 26 THEN
and ( relation_members[i+1] != 'side_stream' or NEW.name->'name' = name->'name')
LOOP
UPDATE placex SET linked_place_id = NEW.place_id WHERE place_id = linked_node_id;
- DELETE FROM search_name WHERE place_id = linked_node_id;
+ IF NOT %REVERSE-ONLY% THEN
+ DELETE FROM search_name WHERE place_id = linked_node_id;
+ END IF;
END LOOP;
END IF;
END LOOP;
UPDATE placex set linked_place_id = NEW.place_id
WHERE place_id = location.place_id;
-- ensure that those places are not found anymore
- DELETE FROM search_name WHERE place_id = location.place_id;
+ IF NOT %REVERSE-ONLY% THEN
+ DELETE FROM search_name WHERE place_id = location.place_id;
+ END IF;
SELECT wikipedia, importance
FROM compute_importance(location.extratags, NEW.country_code,
projects / nominatim.git / blobdiff