0

I am having trouble finding a workflow for my roads layer in PostGIS.

I am using: PostgreSQL 9.6; PostGIS v. 2.3; pgRouting v. 2.4; pgAdmin 4

I've not used a spatial database before, and only know basic SQL, but I know my way around QGIS. I've been trying to follow Anita Graser's pgRouting blog - which is helpful, but a bit out of date.

So my question is, how do I use the pgr_drivingDistance function to create a catchment area, or a set of nodes based on a cost attribute (distance)?

Do I need to use the function pgr_createTopology function first? I did try out this function, but I was not really sure what it did to my data.

Secondly, when using the pgr_drivingDistance function, I couldn't find any results (again, I'm new to databases).

Here is the code I used:

SELECT * FROM pgr_drivingDistance('
  SELECT gid AS id,
      start_id::int4 AS source,
      end_id::int4 AS target,
      descript00::float8 as cost
  FROM network',
  2000,
  1000,
  false,
  false)

I tried doing a join, as per Anita's blog to visualise the results:

SELECT * FROM node JOIN (SELECT * FROM pgr_drivingDistance(' SELECT gid AS id, start_id::int4 AS source, end_id::int4 AS target, descript00::float8 as cost FROM network', 2000, 1000, false, false)) AS route ON node.id = route.seq

But I still am not sure how to visualise this in QGIS v2.16, or even if these are the correct steps to take.

2

First of all, the best place to get started with pgRouting is the pgRouting Workshop. It doesn't contain a pgr_drivingDistance example, but pgRouting functions work usually in a similar way.

If you need to use pgr_createTopology or not, depends on your data. If your data already has a network topology with source and target information, then you don't need to run the function.

pgRouting functions ususally return a set of records with only a few attributes, usually without geometry column. So by joining your original table with the query result, you can retreive all the available attributes including the geoemtry, which you will need in QGIS to display the result.

1

After a lot of time reading other blogs I finally figured out a way that works for my ordnance survey data. I've posted it below. The data came in a 3D format, so I had to force it to be 2D (that's why I couldn't build the topology) then merge the lines together.

ALTER TABLE tq_roadlink
ALTER COLUMN geom TYPE geometry(LineString)
    USING ST_Force2D(geom); 
ALTER TABLE tq_roadlink
ALTER COLUMN geom TYPE geometry(LineString,27700)
USING ST_LineMerge(geom);
ALTER TABLE tq_roadlink ADD COLUMN source integer;
ALTER TABLE tq_roadlink ADD COLUMN target integer;
SELECT pgr_createTopology('tq_roadlink', 0.001, 'geom', 'gid');

Once the topology is created, then use QGIS pgRouting plugin.

0

Unfortunately I can only offer camaraderie!

I am exactly in the same position. I have little experience working with databases(let alone create open-source, routing databases) as well.

At this moment I have followed the same guide and am stuck at same spot.

For me, it made sense that the function: shortest_path(as seen in the guide) is outdated. So next, I used the supposed replaced function: pgr_drivingdistance but gives me the same error(doesn't exist). This error led me to search the same problem as PolyGeo. Below is a piece of code you can execute in your sql command to find your current functions available in your database:

SELECT p.proname FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid WHERE n.nspname = 'public' AND p.proname LIKE 'pgr%' ORDER BY p.proname;

To be frank, Anita Graser(aka underdark) published these guides from 2011 and only I assumed there would be tweaks due to changes with new PGROUTING releases.

With that said I am attempting to create the catchment areas in hopes of creating the isocrhones(please see https://anitagraser.com/2011/02/12/drive-time-isochrones/). For reference, I have followed the workshop steps to create and import my data(please see http://workshop.pgrouting.org/2.2.10/en/index.html)

Best of luck PolyGeo! I will update this post with solutions(alternatives) as I find them.

-Chris

  • @T.O.6 not PolyGeo, didn't look at who posted – ckohler4692 Jun 30 '17 at 18:36

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.