0

I am trying to get points which are reachable in given amount of drive time from a point. I have a table of hospitals points. And I have OSM map data of an area which is converted to work with pgrouting using osm2pgrouting tool.

Can someone suggest me what is the right way to achieve this?

Approach I am thinking is as bellow.

  1. Find the alphashape polygon for given N minutes.
  2. Use the polygon to search for points inside that polygon.

I also want to show drive time for each individual hospital. I am not getting how to do this.

So far I have done this. I have updated the ways table by adding one more column as 'time' and updated the values by calculating approximate amount of time to cover the edge.

 UPDATE ways SET time = round(((length_m * 60) / 12000)::NUMERIC, 2);

Calculating alphashapes for some time like bellow.

SELECT 1 As id, ST_SetSRID(pgr_pointsAsPolygon( $$SELECT dd.seq AS id,
 ST_X(v.the_geom) AS x, ST_Y(v.the_geom) As y FROM pgr_drivingDistance($sub$SELECT gid As id, 
source, target, time AS cost FROM ways$sub$,(SELECT n.id FROM ways_vertices_pgr AS n 
ORDER BY ST_SetSRID(ST_Point(77.54653690,12.97587840),4326) <-> n.the_geom LIMIT 1) ,  4, true ) AS dd 
INNER JOIN ways_vertices_pgr AS v ON dd.node = v.id$$), 4326) As geom;

And then using st_within to find points inside the polygon.

Can someone suggest me how and what is the correct way to achieve my requirement?

I am using POSTGIS="2.4.2 r16113" and pgr (2.5.2,v2.5.2,60585f1f7,master,1.58.0)

Improve this question
5
  • 1
    deleted my answer, I just understood the actual issue ,) this is actually a nasty problem, especially to get the actual actual driving time to the hospitals. my advice would be to include nodes for each hospital (approximated as closest points on the closest edge maybe) in the routing graph in the first place, but that is also not exactly trivial; check out this answer for an approach. – geozelot Apr 19 '18 at 10:56
  • @ThingumaBob Is the approach I am trying is right one? I haven't noticed your answer. Can you put it as comment, so that I can refer or get some idea? – Aparichith Apr 19 '18 at 11:20
  • my answer wasn't actually helping, I missed the point where you have a second table with hospitals. the alpha shape might exclude some possible hospitals, plus there is no way to get the actual driving time to them; distance is measured to the nodes, i.e. road junctions, and hospitals are most likey situated partially along the edges. – geozelot Apr 19 '18 at 11:50
  • I can add some buffer to alphashape polygon to include the hospitals that are on the edge of polygons right? – Aparichith Apr 19 '18 at 12:21
  • you should rather use ST_DWithin instead, but still, the nasty part remains. there is the pgr_withPointsDD function that is designed to do just what you want; it comes shipped already, but is still not officially implemented. here, you basically need to provide an extra table (as query) with edge_ids for edges that are closest to each hospital, and the fraction of line length where the projected (closest point) position of a hospital is located. see if you can make use of it maybe. – geozelot Apr 19 '18 at 21:29

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.