2

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.