1

I have tried to calculate Isochrones in 500m walking distance along paths around planned subway stations with pgrouting.

For the setup I used an OSM-road.shp clipped to the borders of Vienna and a point shp. with the 14 stations which I want to analyse.

I worked my way through several tutorial since I am new to pgrouting for setting up a street-network and finding the closest nodes from the stations.

So far so good…but when I to calculate the isochrones with pgr_DrivingDistance the output shows very few results.

Either I set up the network wrong or I have been using the DrivingDistance function wrong. It seems like the function is not able to use every path.

How do I improve the query?

PostgreSQL 9.5.11 Pgrouting 2.4.1

CREATE OR REPLACE VIEW road_ext AS
  SELECT *, ST_StartPoint(geom), ST_EndPoint(geom) 
   FROM road;

CREATE TABLE node AS
 SELECT row_number() OVER (ORDER by foo.p)::integer AS id,
    foo.p AS geom
FROM (
    SELECT DISTINCT road_ext.ST_StartPoint AS p FROM road_ext
    UNION
    SELECT DISTINCT road_ext.ST_EndPoint AS p FROM road_ext
   ) foo
  GROUP BY foo.p;

I had to change the geometry type since OSM provided multistring

ALTER TABLE road
 ALTER COLUMN geom TYPE geometry(LineString, 31256)
 USING ST_GeometryN(geom, 1);

Setup network

CREATE TABLE network AS 
 SELECT a.*, b.id as start_id, c.id as end_id
  FROM road_ext AS a
    JOIN node AS b ON a.ST_Startpoint = b.geom
    JOIN node AS c ON a.ST_EndPoint = c.geom;


ALTER TABLE network add column len_km double precision

ALTER TABLE network add column time_walking double precision

UPDATE network set len_km = (ST_Length(geom))/1000 

UPDATE network set time_walking  = ((60/5::double precision)*len_km)

Setup the point shp

ALTER TABLE ubahnpointsmgi
  ADD Column nearest_node integer


--Node in clostest distance is enough accuracy
CREATE TABLE temp AS
 SELECT 
   DISTINCT ON (a.gid) a.gid, b.id AS start_id,
    ST_Distance(a.geom, b.geom) AS dist
    FROM ubahnpointsmgi a, node b
    ORDER BY a.gid, ST_Distance(a.geom, b.geom), b.id

UPDATE ubahnpointsmgi
 SET nearest_node =
  (SELECT start_id
    FROM temp
    WHERE temp.gid = ubahnpointsmgi.gid)

Calculating Line geometry

CREATE TABLE ISO_500 AS
 SELECT seq, cost, agg_cost, node, edge, l.geom FROM pgr_drivingDistance(
        'SELECT gid as id, start_id as source, end_id as target, len_km as 
         cost FROM network',
        41217,0.5, false) AS foo JOIN network l ON l.gid = foo.edge

Calculating Point geometry

CREATE TABLE ISO_500 AS
  SELECT seq, cost, node, edge, l.geom FROM pgr_drivingDistance(
        'SELECT gid as id, start_id as source, end_id as target, len_km as 
         cost FROM network',
        41217,0.5, false) AS foo LEFT JOIN node l ON l.id = foo.node

Output Table:enter image description here
Output QGIS:enter image description here

1

The problem with the calculation was the network based on the osm.shp. After a closer look on the lines I realised that a lot of the lines provided from osm a very long segments. Therefore fewer nodes were created and fewer connection between the paths were available.

The solution for my problem was to use a road network which uses shorter line segments with way more connections between them. Luckily such a shp is provided by the open government data for Vienna. If such data would have been not available, I would have to break up the long lines of the OSM data in order to generate shorter segments.

In the image below the results for the same point can be seen. Sadly there are not as many paths in general in the new network in comparison to the OSM shp., but this can be handled with a simple merge of the missing paths.

enter image description here

  • If you use OSM , you should import with osm2pgrouting for example, which is supposed to "fix" issues like this one. – dkastl Mar 29 '18 at 9:24
  • I tried osm2pgrouting but I wasn´t able to get it to work. I posted it here Link..maybe you know what the problem was – K_Man Mar 29 '18 at 10:45

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.