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 AS SELECT id, the_geom, min(distance) as distance, category, max_distance FROM ( 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<=n.distance ) coverage GROUP by id, the_geom, category, max_distance
Finally I get the points covered by all the services using:
SELECT * FROM ( SELECT id, the_geom, count(category) AS tot_services FROM coverage_pts GROUP by id, the_geom ) AS hottest_point WHERE tot_services = 20 --I have 20 categories
All is working fine except the last query takes about 10 seconds for 427 POIs on my 2008 MacBook Pro. When I try to visualize the points in QGIS, the refresh time when panning/zooming is very high.
Is there something I can do to speed up the operations? I also would like to keep it as "compact" as possible (avoid to break data into multiple views by category, the only alternative I thought of).