-- Add all names from the place nodes that deviate from the name
-- in the relation with the prefix '_place_'. Deviation means that
-- either the value is different or a given key is missing completely
- SELECT hstore(array_agg('_place_' || key), array_agg(value)) INTO extra_names
- FROM each(location.name - result.name);
- {% if debug %}RAISE WARNING 'Extra names: %', extra_names;{% endif %}
+ IF result.name is null THEN
+ SELECT hstore(array_agg('_place_' || key), array_agg(value))
+ INTO result.name
+ FROM each(location.name);
+ ELSE
+ SELECT hstore(array_agg('_place_' || key), array_agg(value)) INTO extra_names
+ FROM each(location.name - result.name);
+ {% if debug %}RAISE WARNING 'Extra names: %', extra_names;{% endif %}
- IF extra_names is not null THEN
- result.name := result.name || extra_names;
+ IF extra_names is not null THEN
+ result.name := result.name || extra_names;
+ END IF;
END IF;
{% if debug %}RAISE WARNING 'Final names: %', result.name;{% endif %}
CREATE OR REPLACE FUNCTION find_associated_street(poi_osm_type CHAR(1),
- poi_osm_id BIGINT)
+ poi_osm_id BIGINT,
+ bbox GEOMETRY)
RETURNS BIGINT
AS $$
DECLARE
location RECORD;
parent RECORD;
+ result BIGINT;
+ distance FLOAT;
+ new_distance FLOAT;
+ waygeom GEOMETRY;
BEGIN
FOR location IN
SELECT members FROM planet_osm_rels
FOR i IN 1..array_upper(location.members, 1) BY 2 LOOP
IF location.members[i+1] = 'street' THEN
FOR parent IN
- SELECT place_id from placex
+ SELECT place_id, geometry
+ FROM placex
WHERE osm_type = upper(substring(location.members[i], 1, 1))::char(1)
and osm_id = substring(location.members[i], 2)::bigint
and name is not null
and rank_search between 26 and 27
LOOP
- RETURN parent.place_id;
+ -- Find the closest 'street' member.
+ -- Avoid distance computation for the frequent case where there is
+ -- only one street member.
+ IF waygeom is null THEN
+ result := parent.place_id;
+ waygeom := parent.geometry;
+ ELSE
+ distance := coalesce(distance, ST_Distance(waygeom, bbox));
+ new_distance := ST_Distance(parent.geometry, bbox);
+ IF new_distance < distance THEN
+ distance := new_distance;
+ result := parent.place_id;
+ waygeom := parent.geometry;
+ END IF;
+ END IF;
END LOOP;
END IF;
END LOOP;
END LOOP;
- RETURN NULL;
+ RETURN result;
END;
$$
LANGUAGE plpgsql STABLE;
{% if debug %}RAISE WARNING 'finding street for % %', poi_osm_type, poi_osm_id;{% endif %}
-- Is this object part of an associatedStreet relation?
- parent_place_id := find_associated_street(poi_osm_type, poi_osm_id);
+ parent_place_id := find_associated_street(poi_osm_type, poi_osm_id, bbox);
IF parent_place_id is null THEN
parent_place_id := find_parent_for_address(token_info, poi_partition, bbox);
RETURN location.place_id;
END IF;
- parent_place_id := find_associated_street('W', location.osm_id);
+ parent_place_id := find_associated_street('W', location.osm_id, bbox);
END LOOP;
END IF;
SELECT place_id FROM placex
WHERE bbox && geometry AND _ST_Covers(geometry, ST_Centroid(bbox))
AND rank_address between 5 and 25
+ AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
ORDER BY rank_address desc
LOOP
RETURN location.place_id;
SELECT place_id FROM placex
WHERE bbox && geometry AND _ST_Covers(geometry, ST_Centroid(bbox))
AND rank_address between 5 and 25
+ AND ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon')
ORDER BY rank_address desc
LOOP
RETURN location.place_id;
-- If extratags has a place tag, look for linked nodes by their place type.
-- Area and node still have to have the same name.
- IF bnd.extratags ? 'place' and bnd_name is not null THEN
+ IF bnd.extratags ? 'place' and bnd.extratags->'place' != 'postcode'
+ and bnd_name is not null
+ THEN
FOR linked_placex IN
SELECT * FROM placex
WHERE (position(lower(name->'name') in bnd_name) > 0
AND placex.osm_type = 'N'
AND (placex.linked_place_id is null or placex.linked_place_id = bnd.place_id)
AND placex.rank_search < 26 -- needed to select the right index
- AND placex.type != 'postcode'
AND ST_Covers(bnd.geometry, placex.geometry)
LOOP
{% if debug %}RAISE WARNING 'Found type-matching place node %', linked_placex.osm_id;{% endif %}
FROM placex
WHERE osm_type = 'R' and class = 'boundary' and type = 'administrative'
and admin_level < NEW.admin_level and admin_level > 3
- and rank_address > 0
+ and rank_address between 1 and 25 -- for index selection
+ and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') -- for index selection
and geometry && NEW.centroid and _ST_Covers(geometry, NEW.centroid)
ORDER BY admin_level desc LIMIT 1
LOOP
FROM placex,
LATERAL compute_place_rank(country_code, 'A', class, type,
admin_level, False, null) prank
- WHERE class = 'place' and rank_address < 24
+ WHERE class = 'place' and rank_address between 1 and 23
and prank.address_rank >= NEW.rank_address
+ and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') -- select right index
and geometry && NEW.geometry
and geometry ~ NEW.geometry -- needed because ST_Relate does not do bbox cover test
and ST_Relate(geometry, NEW.geometry, 'T*T***FF*') -- contains but not equal
LATERAL compute_place_rank(country_code, 'A', class, type,
admin_level, False, null) prank
WHERE prank.address_rank < 24
+ and rank_address between 1 and 25 -- select right index
+ and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') -- select right index
and prank.address_rank >= NEW.rank_address
and geometry && NEW.geometry
and geometry ~ NEW.geometry -- needed because ST_Relate does not do bbox cover test
LATERAL compute_place_rank(country_code, 'A', class, type,
admin_level, False, null) prank
WHERE osm_type = 'R'
+ and rank_address between 1 and 25 -- select right index
+ and ST_GeometryType(geometry) in ('ST_Polygon','ST_MultiPolygon') -- select right index
and ((class = 'place' and prank.address_rank = NEW.rank_address)
or (class = 'boundary' and rank_address = NEW.rank_address))
and geometry && NEW.centroid and _ST_Covers(geometry, NEW.centroid)
NEW.importance := null;
SELECT wikipedia, importance
- FROM compute_importance(NEW.extratags, NEW.country_code, NEW.osm_type, NEW.osm_id)
+ FROM compute_importance(NEW.extratags, NEW.country_code, NEW.rank_search, NEW.centroid)
INTO NEW.wikipedia,NEW.importance;
{% if debug %}RAISE WARNING 'Importance computed from wikipedia: %', NEW.importance;{% endif %}
{% if debug %}RAISE WARNING 'finding street for % %', NEW.osm_type, NEW.osm_id;{% endif %}
NEW.parent_place_id := null;
- is_place_address := coalesce(not NEW.address ? 'street' and NEW.address ? 'place', FALSE);
+ is_place_address := not token_is_street_address(NEW.token_info);
-- We have to find our parent road.
NEW.parent_place_id := find_parent_for_poi(NEW.osm_type, NEW.osm_id,
SELECT p.country_code, p.postcode, p.name FROM placex p
WHERE p.place_id = NEW.parent_place_id INTO location;
- IF is_place_address THEN
+ IF is_place_address and NEW.address ? 'place' THEN
-- Check if the addr:place tag is part of the parent name
SELECT count(*) INTO i
FROM svals(location.name) AS pname WHERE pname = NEW.address->'place';
IF linked_place is not null THEN
-- Recompute the ranks here as the ones from the linked place might
-- have been shifted to accommodate surrounding boundaries.
- SELECT place_id, osm_id, class, type, extratags,
+ SELECT place_id, osm_id, class, type, extratags, rank_search,
centroid, geometry,
(compute_place_rank(country_code, osm_type, class, type, admin_level,
(extratags->'capital') = 'yes', null)).*
SELECT wikipedia, importance
FROM compute_importance(location.extratags, NEW.country_code,
- 'N', location.osm_id)
+ location.rank_search, NEW.centroid)
INTO linked_wikipedia,linked_importance;
-- Use the maximum importance if one could be computed from the linked object.
ELSE
-- No linked place? As a last resort check if the boundary is tagged with
-- a place type and adapt the rank address.
- IF NEW.rank_address > 0 and NEW.extratags ? 'place' THEN
+ IF NEW.rank_address between 4 and 25 and NEW.extratags ? 'place' THEN
SELECT address_rank INTO place_address_level
FROM compute_place_rank(NEW.country_code, 'A', 'place',
NEW.extratags->'place', 0::SMALLINT, False, null);
{% endif %}
END IF;
- IF NEW.postcode is null AND NEW.rank_search > 8 THEN
+ IF NEW.postcode is null AND NEW.rank_search > 8
+ AND (NEW.rank_address > 0
+ OR ST_GeometryType(NEW.geometry) not in ('ST_LineString','ST_MultiLineString')
+ OR ST_Length(NEW.geometry) < 0.02)
+ THEN
NEW.postcode := get_nearest_postcode(NEW.country_code, NEW.geometry);
END IF;
projects / nominatim.git / blobdiff