+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
+--
+-- Copy data from linked items (POIs on ways, addr:street links, relations).
+--
+CREATE OR REPLACE FUNCTION find_parent_place_for_poi(poi_osm_type CHAR(1),
+ poi_osm_id BIGINT,
+ poi_partition SMALLINT,
+ near_centroid GEOMETRY,
+ addr_street TEXT,
+ addr_place TEXT,
+ fallback BOOL = true)
+ RETURNS BIGINT
+ AS $$
+DECLARE
+ parent_place_id BIGINT DEFAULT NULL;
+ location RECORD;
+ parent RECORD;
+BEGIN
+ --DEBUG: RAISE WARNING 'finding street for % %', poi_osm_type, poi_osm_id;
+
+ -- Is this object part of an associatedStreet relation?
+ FOR location IN
+ SELECT members FROM planet_osm_rels
+ WHERE parts @> ARRAY[poi_osm_id]
+ and members @> ARRAY[lower(poi_osm_type) || poi_osm_id]
+ and tags @> ARRAY['associatedStreet']
+ LOOP
+ FOR i IN 1..array_upper(location.members, 1) BY 2 LOOP
+ IF location.members[i+1] = 'street' THEN
+ --DEBUG: RAISE WARNING 'node in relation %',relation;
+ FOR parent IN
+ SELECT place_id from placex
+ WHERE osm_type = 'W' 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;
+ END LOOP;
+ END IF;
+ END LOOP;
+ END LOOP;
+
+ parent_place_id := find_parent_for_address(addr_street, addr_place,
+ poi_partition, near_centroid);
+ IF parent_place_id is not null THEN
+ RETURN parent_place_id;
+ END IF;
+
+ IF poi_osm_type = 'N' THEN
+ -- Is this node part of an interpolation?
+ FOR parent IN
+ SELECT q.parent_place_id
+ FROM location_property_osmline q, planet_osm_ways x
+ WHERE q.linegeo && near_centroid and x.id = q.osm_id
+ and poi_osm_id = any(x.nodes)
+ LIMIT 1
+ LOOP
+ --DEBUG: RAISE WARNING 'Get parent from interpolation: %', parent.parent_place_id;
+ RETURN parent.parent_place_id;
+ END LOOP;
+
+ -- Is this node part of any other way?
+ FOR location IN
+ SELECT p.place_id, p.osm_id, p.rank_search, p.address,
+ coalesce(p.centroid, ST_Centroid(p.geometry)) as centroid
+ FROM placex p, planet_osm_ways w
+ WHERE p.osm_type = 'W' and p.rank_search >= 26
+ and p.geometry && near_centroid
+ and w.id = p.osm_id and poi_osm_id = any(w.nodes)
+ LOOP
+ --DEBUG: RAISE WARNING 'Node is part of way % ', location.osm_id;
+
+ -- Way IS a road then we are on it - that must be our road
+ IF location.rank_search < 28 THEN
+ --DEBUG: RAISE WARNING 'node in way that is a street %',location;
+ return location.place_id;
+ END IF;
+
+ SELECT find_parent_place_for_poi('W', location.osm_id, poi_partition,
+ location.centroid,
+ location.address->'street',
+ location.address->'place',
+ false)
+ INTO parent_place_id;
+ IF parent_place_id is not null THEN
+ RETURN parent_place_id;
+ END IF;
+ END LOOP;
+ END IF;
+
+ -- Still nothing, just use the nearest road
+ IF fallback THEN
+ SELECT place_id FROM getNearestRoadFeature(poi_partition, near_centroid) INTO parent_place_id;
+ --DEBUG: RAISE WARNING 'Checked for nearest way (%)', parent_place_id;
+ END IF;
+
+ RETURN parent_place_id;
+END;
+$$
+LANGUAGE plpgsql STABLE;
+
+-- Try to find a linked place for the given object.
+CREATE OR REPLACE FUNCTION find_linked_place(bnd placex)
+ RETURNS placex
+ AS $$
+DECLARE
+ relation_members TEXT[];
+ rel_member RECORD;
+ linked_placex placex%ROWTYPE;
+ bnd_name TEXT;
+BEGIN
+ IF bnd.rank_search >= 26 or bnd.rank_address = 0
+ or ST_GeometryType(bnd.geometry) NOT IN ('ST_Polygon','ST_MultiPolygon')
+ THEN
+ RETURN NULL;
+ END IF;
+
+ IF bnd.osm_type = 'R' THEN
+ -- see if we have any special relation members
+ SELECT members FROM planet_osm_rels WHERE id = bnd.osm_id INTO relation_members;
+ --DEBUG: RAISE WARNING 'Got relation members';
+
+ -- Search for relation members with role 'lable'.
+ IF relation_members IS NOT NULL THEN
+ FOR rel_member IN
+ SELECT get_rel_node_members(relation_members, ARRAY['label']) as member
+ LOOP
+ --DEBUG: RAISE WARNING 'Found label 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
+ --DEBUG: RAISE WARNING 'Linked label member';
+ RETURN linked_placex;
+ END LOOP;
+
+ END LOOP;
+ END IF;
+ END IF;
+
+ IF bnd.name ? 'name' THEN
+ bnd_name := make_standard_name(bnd.name->'name');
+ IF bnd_name = '' THEN
+ bnd_name := NULL;
+ END IF;
+ 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
+ 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;
+ END LOOP;
+ END IF;
+
+ -- Name searches can be done for ways as well as relations
+ IF bnd.osm_type in ('W','R') and bnd_name is not null THEN
+ --DEBUG: RAISE WARNING 'Looking for nodes with matching names';
+ FOR linked_placex IN
+ SELECT placex.* from placex
+ WHERE make_standard_name(name->'name') = bnd_name
+ AND placex.rank_address = bnd.rank_address
+ AND placex.osm_type = 'N'
+ AND placex.rank_search < 26 -- needed to select the right index
+ AND st_covers(geometry, placex.geometry)
+ LOOP
+ --DEBUG: RAISE WARNING 'Found matching place node %', linkedPlacex.osm_id;
+ RETURN linked_placex;
+ END LOOP;
+ END IF;
+
+ RETURN NULL;
+END;
+$$
+LANGUAGE plpgsql STABLE;
+
projects / nominatim.git / blobdiff