I want to create a road-network for use with pgRouting using OpenStreetMap data. I loaded a shapefile from GeoFabrik into a Postgres table (with PostGIS enabled). However, one problem I had was that the roads do not always end at intersections, so I decided to split them all at every intersection or crossing.

To identify all the interesections where roads crossed or intersected I used the following SQL (similar to a previous question):

CREATE TABLE split_points as
   ST_GeometryN(ST_Intersection(a.geom, b.geom),1) as geom      
   roads as a,
   roads as b
    ST_Touches(a.geom, b.geom)
    ST_Crosses(a.geom, b.geom)    
    AND a.gid != b.gid
   ST_Intersection(a.geom, b.geom);

I now want to split the roads using these points. I used the following approach:

CREATE TABLE split_roads as
    ST_GeomFromEWKB((ST_Dump(ST_Split(g.geom, blade.geom))).geom) As geom,
    generate_series(1,ST_NumGeometries((ST_Split(g.geom, blade.geom)))) as gid
    split_points as blade,
    roads as g
    ST_Intersects(g.geom, blade.geom);

The problem with this split approach is that the full road length remains in addition to all of the split pieces. To remove these un-split road geometries that were included I used the ST_Equals() function to identify them, and to delete them:

DELETE FROM split_roads USING roads
WHERE ST_Equals(split_roads.geom, roads.geom)

However, this approach does not remove all of the original unsplit geometries (although it does remove some of them). Is there a better approach for deletion (or overall) so that I have only the split geometries in a table?

  • According to documentation, ST_Split does not return the original, unsplit geometry. What is that extra '.geom' hanging out at the last closing parenthesis in the first line of your SELECT statement? Might be as simple as removing that.
    – Scro
    Oct 5, 2012 at 20:05
  • @Scro which .geom are you referring to? Can't spot it!
    – djq
    Oct 5, 2012 at 20:08
  • I guess technically, that would be the second line of the SELECT statement. Also I'm referring to the "split_roads" table creation. It's the line that ends with '))).geom) as geom'.
    – Scro
    Oct 5, 2012 at 20:19
  • hmm, I get an error when I do that. ERROR: function st_geomfromewkb(geometry_dump) does not exist LINE 4: ST_GeomFromEWKB((ST_Dump(ST_Split(g.geom, blade.geom))))... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
    – djq
    Oct 5, 2012 at 20:25
  • Did the entire line look like this: ST_GeomFromEWKB((ST_Dump(ST_Split(g.geom, blade.geom)))) As geom,
    – Scro
    Oct 5, 2012 at 20:38

6 Answers 6


Simple answer: Don't. You shouldn't do it that way.

From the OSM road Shapefiles, it is impossible to distinguish between intersections and over/underpasses. You'll create intersections that don't exist in reality if you split all seemingly crossing roads.

You'll need to get your hands dirty with the original OSM file, if you don't want to use existing tools such as osm2pgrouting (where the network is small enough) or osm2po.

  • 1
    Exactly. This is also another mistake some people do when they process navteq and teleatlas data. Underpass/Overpasses are a pain but a reality. Oct 8, 2012 at 19:10
  • 1
    Agree. Welcome to GIS where data is allways more or less bad Oct 9, 2012 at 12:59

Not a real solution to your problem, but try osm2po ... it creates perfect SQL code for routing in pgrouting: http://osm2po.de/

  • 1
    Thanks for the suggestion. I have tried osm2pgrouting but it requires more memory than my server has and it terminates without finishing.
    – djq
    Oct 1, 2012 at 22:09
  • 2
    @djq osm2po can handle much bigger files than osm2pgrouting. It can even load planet.osm
    – underdark
    Oct 4, 2012 at 16:27
  • ah, I originally thought osm2po was a typo. Is there a straightforward of installing it in ubuntu?
    – djq
    Oct 4, 2012 at 16:44
  • It's a ready compiled JAR file (java). Just run it as explained on the website. Oct 7, 2012 at 1:52

Another "not real solution to your problem", but our OSM converter splits at intersections while it converts from OSM to SHP. It is more efficient that way, since it can compare the ID of the nodes, rather than doing geometric calculations.


About your general problem, using pgRouting: I think @Uffer, @GisStudent and others that are showing how to use "OSC & etc.", they are right. Go following the clues of of "best practices" and "standards"...

About your question: "split roads into individual segments at intersections" or "how to remove all of the original unsplit geometries". I can help if you show here your results here, step-by-step...

First step: topology analysis

 CREATE TABLE split_points_topo as
    a.gid as gid_a, b.gid  as gid_b, ST_Relation(a.geom, b.geom) as DE9IM_code
    roads as a,
    roads as b
  WHERE a.gid != b.gid AND a.geom && b.geom;
 SELECT DISTINCT st_geometryType(geom) FROM roads;
 SELECT DISTINCT DE9IM_code FROM split_points_topo;
 -- list here the results o these two queries!  ... after we can continue.

One way to solve it algorithmically would be to add the start and end point of each whole road to the set of "intersections", so that you can be certain that every segment is between two intersections.


For anyone stumbling across this question, I found an answer here.

From linked answer:

You need to use Split lines by lines tool, and use the same file as input layer and split layer, as you can see below: enter image description here

You can find the tool under Processing tools -> Vector overlay -> Split with lines. enter image description here

Before running the tool: enter image description here After running the tool: enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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