SELECT osm_type, osm_id, class, type, admin_level, rank_search, rank_address, min(place_id) AS place_id, min(parent_place_id) AS parent_place_id, country_code, get_address_by_language(place_id, -1, ARRAY['short_name:en-US','name:en-US','short_name:en','name:en','short_name','name','brand','official_name:en-US','official_name:en','official_name','ref','type']) AS langaddress, get_name_by_language(name, ARRAY['short_name:en-US','name:en-US','short_name:en','name:en','short_name','name','brand','official_name:en-US','official_name:en','official_name','ref','type']) AS placename, get_name_by_language(name, ARRAY['ref']) AS ref, avg(ST_X(centroid)) AS lon, avg(ST_Y(centroid)) AS lat, COALESCE(importance,0.75-(rank_search::float/40)) AS importance, ( SELECT max(p.importance*(p.rank_address+2)) FROM place_addressline s, placex p WHERE s.place_id = min(CASE WHEN placex.rank_search < 28 THEN placex.place_id ELSE placex.parent_place_id END) AND p.place_id = s.address_place_id AND s.isaddress AND p.importance is not null ) AS addressimportance, (extratags->'place') AS extra_place FROM placex WHERE place_id in (102769) AND ( placex.rank_address between 0 and 30 OR (extratags->'place') = 'city' ) AND linked_place_id is null GROUP BY osm_type, osm_id, class, type, admin_level, rank_search, rank_address, country_code, importance, langaddress, placename, ref, extratags->'place' UNION SELECT 'W' AS osm_type, osm_id, 'place' AS class, 'house' AS type, null AS admin_level, 30 AS rank_search, 30 AS rank_address, min(place_id) as place_id, min(parent_place_id) AS parent_place_id, country_code, get_address_by_language(place_id, housenumber_for_place, ARRAY['short_name:en-US','name:en-US','short_name:en','name:en','short_name','name','brand','official_name:en-US','official_name:en','official_name','ref','type']) AS langaddress, null AS placename, null AS ref, AVG(st_x(centroid)) AS lon, AVG(st_y(centroid)) AS lat, -0.1 AS importance, ( SELECT MAX(p.importance*(p.rank_address+2)) FROM place_addressline s, placex p WHERE s.place_id = min(blub.parent_place_id) AND p.place_id = s.address_place_id AND s.isaddress AND p.importance is not null ) AS addressimportance, null AS extra_place FROM ( SELECT osm_id, place_id, country_code, CASE WHEN startnumber != endnumber THEN ST_LineInterpolatePoint(linegeo, (housenumber_for_place-startnumber::float)/(endnumber-startnumber)::float) ELSE ST_LineInterpolatePoint(linegeo, 0.5) END as centroid, parent_place_id, housenumber_for_place FROM ( location_property_osmline JOIN (values (102769, -1)) AS housenumbers(place_id, housenumber_for_place) USING(place_id) ) WHERE housenumber_for_place>=0 AND 30 between 0 AND 30 ) as blub GROUP BY osm_id, place_id, housenumber_for_place, country_code order by importance desc