-- 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;
country_code VARCHAR(2);
diameter FLOAT;
classtable TEXT;
- classtype TEXT;
BEGIN
--DEBUG: RAISE WARNING '% % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
is_area := ST_GeometryType(NEW.geometry) IN ('ST_Polygon','ST_MultiPolygon');
IF NEW.class in ('place','boundary')
- AND NEW.type in ('postcode','postal_code') THEN
-
+ AND NEW.type in ('postcode','postal_code')
+ THEN
IF NEW.address IS NULL OR NOT NEW.address ? 'postcode' THEN
-- most likely just a part of a multipolygon postcode boundary, throw it away
RETURN NULL;
NEW.name := hstore('ref', NEW.address->'postcode');
- SELECT * FROM get_postcode_rank(NEW.country_code, NEW.address->'postcode')
- INTO NEW.rank_search, NEW.rank_address;
-
- IF NOT is_area THEN
- NEW.rank_address := 0;
- END IF;
ELSEIF NEW.class = 'boundary' AND NOT is_area THEN
- return NULL;
+ RETURN NULL;
ELSEIF NEW.class = 'boundary' AND NEW.type = 'administrative'
- AND NEW.admin_level <= 4 AND NEW.osm_type = 'W' THEN
- return NULL;
- ELSEIF NEW.osm_type = 'N' AND NEW.class = 'highway' THEN
- NEW.rank_search = 30;
- NEW.rank_address = 0;
- ELSEIF NEW.class = 'landuse' AND NOT is_area THEN
- NEW.rank_search = 30;
- NEW.rank_address = 0;
- ELSE
- -- do table lookup stuff
- IF NEW.class = 'boundary' and NEW.type = 'administrative' THEN
- classtype = NEW.type || NEW.admin_level::TEXT;
- ELSE
- classtype = NEW.type;
- END IF;
- SELECT l.rank_search, l.rank_address FROM address_levels l
- WHERE (l.country_code = NEW.country_code or l.country_code is NULL)
- AND l.class = NEW.class AND (l.type = classtype or l.type is NULL)
- ORDER BY l.country_code, l.class, l.type LIMIT 1
- INTO NEW.rank_search, NEW.rank_address;
-
- IF NEW.rank_search is NULL THEN
- NEW.rank_search := 30;
- END IF;
-
- IF NEW.rank_address is NULL THEN
- NEW.rank_address := 30;
- END IF;
+ AND NEW.admin_level <= 4 AND NEW.osm_type = 'W'
+ THEN
+ RETURN NULL;
END IF;
- -- some postcorrections
- IF NEW.class = 'waterway' AND NEW.osm_type = 'R' THEN
- -- Slightly promote waterway relations so that they are processed
- -- before their members.
- NEW.rank_search := NEW.rank_search - 1;
- END IF;
+ SELECT * INTO NEW.rank_search, NEW.rank_address
+ FROM compute_place_rank(NEW.country_code,
+ CASE WHEN is_area THEN 'A' ELSE NEW.osm_type END,
+ NEW.class, NEW.type, NEW.admin_level,
+ (NEW.extratags->'capital') = 'yes',
+ NEW.address->'postcode');
- IF (NEW.extratags -> 'capital') = 'yes' THEN
- NEW.rank_search := NEW.rank_search - 1;
+ -- a country code make no sense below rank 4 (country)
+ IF NEW.rank_search < 4 THEN
+ NEW.country_code := NULL;
END IF;
END IF;
- -- a country code make no sense below rank 4 (country)
- IF NEW.rank_search < 4 THEN
- NEW.country_code := NULL;
- END IF;
-
--DEBUG: RAISE WARNING 'placex_insert:END: % % % %',NEW.osm_type,NEW.osm_id,NEW.class,NEW.type;
RETURN NEW; -- %DIFFUPDATES% The following is not needed until doing diff updates, and slows the main index process down
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
RETURN NEW;
END IF;
+ -- recompute the ranks, they might change when linking changes
+ SELECT * INTO NEW.rank_search, NEW.rank_address
+ FROM compute_place_rank(NEW.country_code,
+ CASE WHEN ST_GeometryType(NEW.geometry)
+ IN ('ST_Polygon','ST_MultiPolygon')
+ THEN 'A' ELSE NEW.osm_type END,
+ NEW.class, NEW.type, NEW.admin_level,
+ (NEW.extratags->'capital') = 'yes',
+ NEW.address->'postcode');
+
+
--DEBUG: RAISE WARNING 'Copy over address tags';
-- housenumber is a computed field, so start with an empty value
NEW.housenumber := NULL;
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