+ -- remove internal and derived names
+ result.address := result.address - '_unlisted_place'::TEXT;
+ SELECT hstore(array_agg(key), array_agg(value)) INTO result.name
+ FROM each(p.name) WHERE key not like '\_%';
+
+ result.class := p.class;
+ result.type := p.type;
+ result.country_code := p.country_code;
+ result.rank_address := p.rank_address;
+ result.centroid_x := ST_X(p.centroid);
+ result.centroid_y := ST_Y(p.centroid);
+
+ -- Names of linked places need to be merged in, so search for a linkable
+ -- place already here.
+ SELECT * INTO location FROM find_linked_place(p);
+
+ IF location.place_id is not NULL THEN
+ result.linked_place_id := location.place_id;
+
+ IF location.name is not NULL THEN
+ {% if debug %}RAISE WARNING 'Names original: %, location: %', result.name, location.name;{% endif %}
+ -- 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
+ 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;
+ END IF;
+ END IF;
+
+ {% if debug %}RAISE WARNING 'Final names: %', result.name;{% endif %}
+ END IF;
+ END IF;
+
+ RETURN result;
+END;
+$$
+LANGUAGE plpgsql STABLE;
+
+
+CREATE OR REPLACE FUNCTION find_associated_street(poi_osm_type CHAR(1),
+ poi_osm_id BIGINT,
+ bbox GEOMETRY)
+ RETURNS BIGINT
+ AS $$
+DECLARE
+ location RECORD;
+ member JSONB;
+ parent RECORD;
+ result BIGINT;
+ distance FLOAT;
+ new_distance FLOAT;
+ waygeom GEOMETRY;
+BEGIN
+{% if db.middle_db_format == '1' %}
+ 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
+ FOR parent IN
+ 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
+ -- 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;
+
+{% else %}
+ FOR member IN
+ SELECT value FROM planet_osm_rels r, LATERAL jsonb_array_elements(members)
+ WHERE planet_osm_member_ids(members, poi_osm_type::char(1)) && ARRAY[poi_osm_id]
+ and tags->>'type' = 'associatedStreet'
+ and value->>'role' = 'street'
+ LOOP
+ FOR parent IN
+ SELECT place_id, geometry
+ FROM placex
+ WHERE osm_type = (member->>'type')::char(1)
+ and osm_id = (member->>'ref')::bigint
+ and name is not null
+ and rank_search between 26 and 27
+ LOOP
+ -- 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 LOOP;
+{% endif %}
projects / nominatim.git / blobdiff