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
Can someone suggest me what is the right way to achieve this?
Approach I am thinking is as bellow.
- Find the alphashape polygon for given N minutes.
- 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