Splitting polylines using regions with PostGIS

Question

I was wondering if it is possible to split a polyline on polygon boundaries (regions) in PostGIS, the picture below is of what we have at the moment and showing where I need the polylines split:

Lets call the regions road_blocks and the lines road_cl

If you can copy the data from the road_block under the newly created polyline to the new polyline that would be a real bonus.

Answer 1

CREATE TABLE new_road_cl AS
  SELECT ST_Intersection(r.geom, b.geom) AS geom,
         b.attr1, b.attr2, b.attr3,
         r.attr1, r.attr2, r.attr3
  FROM road_blocks b, road_cl r
  WHERE ST_Intersects(r.geom, b.geom);

Answer 2

Nathan,

1) Make sure your geometry is valid - invalid geometries are a major cause of these kind of errors. SELECT ST_IsValid(geom);

2) Which version of geos are you running? Do a

SELECT postgis_full_version();

GEOS 3.2+ runs into fewer of these topological exception issues.

To Adam - Paul decided one day he was too lazy to type the_geom :) -- so he proclaimed "from now on the default will be -- geom". We all stamped our feet and said -- NO until 2.0

So geog is default for geography and geom will be the default in 2.0 for geometry.