export_csv.sql

\copy (WITH offices AS (SELECT ogc_fid as office_id, COALESCE(name, 'Office '||ogc_fid) as office_name, pgrouting_node as source_node, ST_X(ST_Centroid(wkb_geometry)) as lon, ST_Y(ST_Centroid(wkb_geometry)) as lat FROM pois WHERE building = 'office'), cafes AS (SELECT ogc_fid as cafe_id, COALESCE(name, 'Cafe '||ogc_fid) as cafe_name, pgrouting_node as target_node, ST_X(ST_Centroid(wkb_geometry)) as lon, ST_Y(ST_Centroid(wkb_geometry)) as lat FROM pois WHERE amenity = 'cafe'), costs AS (SELECT start_vid, end_vid, agg_cost FROM pgr_dijkstraCost('SELECT id, source, target, length_m as cost, length_m as reverse_cost FROM ways', (SELECT array_agg(DISTINCT source_node) FROM offices), (SELECT array_agg(DISTINCT target_node) FROM cafes), directed := false) WHERE agg_cost <= 1000 AND agg_cost >= 0), valid_offices AS (SELECT start_vid as source_node FROM costs GROUP BY start_vid HAVING count(DISTINCT end_vid) > 5) SELECT o.office_name as source_office, o.lon as source_lon, o.lat as source_lat, c.cafe_name as target_cafe, c.lon as target_lon, c.lat as target_lat, costs.agg_cost as walking_distance_m FROM offices o JOIN valid_offices vo ON o.source_node = vo.source_node JOIN costs ON o.source_node = costs.start_vid JOIN cafes c ON c.target_node = costs.end_vid) TO 'kepler_connections.csv' WITH CSV HEADER;