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?