Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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
SELECT DISTINCT    
   ST_GeometryN(ST_Intersection(a.geom, b.geom),1) as geom      
FROM
   roads as a,
   roads as b
WHERE
    ST_Touches(a.geom, b.geom)
OR
    ST_Crosses(a.geom, b.geom)    
    AND a.gid != b.gid
GROUP BY
   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
SELECT     
    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
FROM    
    split_points as blade,
    roads as g
WHERE
    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
show 3 more comments

5 Answers 5

up vote 4 down vote accepted

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

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
1  
@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
add comment

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
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. –  Ragi Yaser Burhum Oct 8 '12 at 19:10
1  
Agree. Welcome to GIS where data is allways more or less bad –  simplexio Oct 9 '12 at 12:59
add comment

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
add comment

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
  SELECT     
    a.gid as gid_a, b.gid  as gid_b, ST_Relation(a.geom, b.geom) as DE9IM_code
  FROM
    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
add comment

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
add comment

Your Answer

 
discard

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.