I am trying to understand this code in pgRouting for creating topology:

   SELECT *, startpoint(the_geom), endpoint(the_geom)
   FROM road;
   SELECT row_number() OVER (ORDER BY foo.p)::integer AS id, 
      foo.p AS the_geom
   FROM (         
      SELECT DISTINCT road_ext.startpoint AS p FROM road_ext
      SELECT DISTINCT road_ext.endpoint AS p FROM road_ext
        ) foo
   GROUP BY foo.p;

   SELECT a.*, b.id as start_id, c.id as end_id
   FROM road_ext AS a
   JOIN node AS b ON a.startpoint = b.the_geom
   JOIN node AS c ON a.endpoint = c.the_geom;

From this code, can anyone explain this code a bit please. I am confused and want to know if it generates entries of individual road segments in separate rows even if I digitize some road segments into a single road.

Clearly, I want to know what way we need to digitise roads for creating a road network? Shall we need to split a long road into new segments at every connection to a small road or do the below code generates it automatically? Please help!!

As the code only says startpoint and endpoint, I dont get how it can identify the intermediate nodes where there are connecting roads.

For Example, in the below image enter image description here I digitised the long road in a single entry. Now if I run the above code, will it generates the individual line segments?


Yes, typically a road network needs to be split at every intersection. Also, the road will need attributes identifying the From and To number for the right and left side of the road.

| improve this answer | |
  • Do the above code split it if I digitise in a single entry? – GP92 Apr 23 '13 at 1:18
  • 1
    Np. It handles only start and endpoint. What you need is to split that long line from intersections if you can turn there. Offcourse tunnels and bridges intersect but you dont cut those. – simplexio Apr 23 '13 at 6:33

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.