I am working on a research to calculate the distance of firms (scattering around the nation) to several metropolises via selected national highway system.

Then I overlay the highway map with firms' location. I have a sample map that is zoomed in to Beijing's map that encompasses both highway and firms: enter image description here

The purple lines are national highway system captured by the city map (Beijing ) and black dots represent firms' location. My research wants to calculate the distance of firms to another major city (now as a test, I just need to calculate the distance of firms in Beijing to Shanghai.

I follow a post by underdark to calculate the shortest path from a point to another point via a selected path. I am a newbie to pgrouting and the programming. I have some trouble to generate nodes ID and calculate the shortest path.

This is the table of my highway data: enter image description here

And this is my coding to generate nodes ID:

        CREATE VIEW highway_ext AS
    SELECT *, ST_StartPoint(geom), ST_EndPoint(geom)
    FROM highway

   SELECT row_number() OVER (ORDER BY foo.p)::integer AS id, 
          foo.p AS geom
   FROM (         
      SELECT DISTINCT highway_ext.ST_StartPoint AS p FROM highway_ext
      SELECT DISTINCT highway_ext.ST_EndPoint AS p FROM highway_ext
   ) foo
   GROUP BY foo.p;

This is my codes to do a shortpath calc:

   CREATE TABLE network1 AS
   SELECT a.*, b.id as ST_start_id, c.id as ST_end_id
   FROM highway_view AS a
      JOIN node AS b ON a.ST_StartPoint = b.the_geom
      JOIN node AS c ON a.ST_EndPoint = c.the_geom;

      SELECT * FROM pgr_dijkstra('
   SELECT gid AS id, 
          ST_start_id::int4 AS source, 
          ST_end_id::int4 AS target, 
          shape_leng::float8 AS cost
   FROM network',

As a result, my nodes table is like nothing

Any idea how should I generate node ID and Edge ID? What else do I need to address them?

  • I used osm2pgrouting, which does this for you. I know that isn't what you asked, but it does work. – John Powell Jul 13 '16 at 10:57

1) never use geometry (inlcuding point) for joining. Too many floating point precision issues with that.

2) I see your geometries are MULTILINESTRING, bad never use this for routing your the_geom should be of type LINESTRING
- in PostGIS 2.0 ST_StartPoint / EndPoint will return NULL when applied to MULTILINESTRING and its just generally slower since pgrouting will just take the first geometry. If you really have MULTILINESTRING you should use ST_Dump to convert them to linestrings:

I'm going to assume that all your MULTIs are really a single wrapped in a multi, to be sure run this query and it shouldn't return anything

SELECT * FROM highways WHERE ST_NumGeometries(the_geom) > 1;

If you get any - dump these out so separate records

If you have all singles, do this to convert your column:

 ALTER TABLE highway 
   ALTER COLUMN the_geom geometry(LINESTRING, 4326) USING ST_GeometryN(the_geom,1);

For future to prevent this, when loading with shp2pgsql use the -S switch to bring in as single linestrings (it will error if you have true multis)

You should use pgr_createTopology to generate the node table and populate source and target.


ALTER TABLE highway ADD COLUMN source integer;
ALTER TABLE highway ADD COLUMN target integer;
SELECT pgr_createTopology('highway', 0.00001);

The 0.00001 is tolerance in units of your spatial ref sys. The pgr_createTopology does what you are trying to do - it will populate source and target (treating start and end points within tolerance as the same node) and create a nodes table highway_vertices_pgr

By the way this is all covered in our upcoming book which you can purchase draft now (we are about 75% done)


so you'll get updates on each draft we publish and final when completed.

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.