Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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 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?

share|improve this question
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 '12 at 20:05
@Scro which .geom are you referring to? Can't spot it! – djq Oct 5 '12 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 '12 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 '12 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 '12 at 20:38
up vote 5 down vote accepted

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

share|improve this answer
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 '12 at 22:09
@djq osm2po can handle much bigger files than osm2pgrouting. It can even load planet.osm – underdark Oct 4 '12 at 16:27
ah, I originally thought osm2po was a typo. Is there a straightforward of installing it in ubuntu? – djq Oct 4 '12 at 16:44
It's a ready compiled JAR file (java). Just run it as explained on the website. – GIS Student Oct 7 '12 at 1:52

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.

share|improve this answer
Exactly. This is also another mistake some people do when they process navteq and teleatlas data. Underpass/Overpasses are a pain but a reality. – Ragi Yaser Burhum Oct 8 '12 at 19:10
Agree. Welcome to GIS where data is allways more or less bad – simplexio Oct 9 '12 at 12:59

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.

share|improve this answer

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.
share|improve this answer

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.

share|improve this answer

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.