Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.*, as start_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?

share|improve this question
up vote 2 down vote accepted

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.

share|improve this answer
Do the above code split it if I digitise in a single entry? – Jeevan Patnaik Apr 23 '13 at 1:18
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 posting your answer, you agree to the privacy policy and terms of service.

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