I'm trying to get some useful information out of openstreetmap data to choose where to rent an apartment in my city.
I used osm2pgrouting and osm2pgsql to get OSM data into my postgis database.So far I've managed to create a view (nearest_poi) with the id of the nearest neighbor of my POIs (some type of shops, bus stations and parks from OSM points and polygons), along with the category of the poi and the maximum distance I would like to be from that category (near_vertex_id, category, distance).
To be able to get the coverage in a single view I used pgr_drivingdistance from all the points of nearest_poi using the absolute maximum distance (max_distance is a table that stores the couple category-distance) and then I get rid of the points with distance greater than the maximum distance for that category:
CREATE OR REPLACE VIEW coverage_pts ASSELECT id, the_geom, min(distance) as distance, category, max_distanceFROM ( SELECT v.id, v.the_geom, di.agg_cost as distance, n.category, n.distance as max_distance FROM pgr_drivingdistance( 'SELECT gid as id, source, target, length_m as cost FROM ways', array(SELECT DISTINCT near_vertex FROM nearest_poi), (SELECT max(distance) FROM max_distances), false, false ) as di JOIN ways_vertices_pgr v ON di.node = v.id JOIN nearest_poi n ON di.from_v = n.near_vertex WHERE di.agg_cost
I used osm2pgrouting and osm2pgsql to get OSM data into my postgis database.So far I've managed to create a view (nearest_poi) with the id of the nearest neighbor of my POIs (some type of shops, bus stations and parks from OSM points and polygons), along with the category of the poi and the maximum distance I would like to be from that category (near_vertex_id, category, distance).
To be able to get the coverage in a single view I used pgr_drivingdistance from all the points of nearest_poi using the absolute maximum distance (max_distance is a table that stores the couple category-distance) and then I get rid of the points with distance greater than the maximum distance for that category:
CREATE OR REPLACE VIEW coverage_pts ASSELECT id, the_geom, min(distance) as distance, category, max_distanceFROM ( SELECT v.id, v.the_geom, di.agg_cost as distance, n.category, n.distance as max_distance FROM pgr_drivingdistance( 'SELECT gid as id, source, target, length_m as cost FROM ways', array(SELECT DISTINCT near_vertex FROM nearest_poi), (SELECT max(distance) FROM max_distances), false, false ) as di JOIN ways_vertices_pgr v ON di.node = v.id JOIN nearest_poi n ON di.from_v = n.near_vertex WHERE di.agg_cost