[postgis-users] Difference between line and polygon layer

[Matei Domnita]

Hello,

I am not very experienced in PostGIS and I have a problem which seems to
take forever to solve.

I have a line layer (roads) and a polygon layer(localities) and I want to
select all parts of the roads that are outside localities.

First, I removed all the roads that are completely inside or completely
outside my polygons so the only ones remaining are the ones that cross the
polygons.

Than I tried two approaches.

First:
create table polyunion as select ST_Multi(ST_Collect(geom)) from poly;
(ST_Union gave topology errors or took forever)

create table rddifference as (select
 ST_Difference(r.geom,c.geom) as geom
 from roa r join polyunion c on ST_Intersects(c.geom,r.geom));

This one is running for three days now and does not seem to end soon.


The second one, without doing the union on the polygons:

create table rddifference2 as (select
 ST_Difference(r.geom,c.geom) as geom
 from roadstoprocess r join localities c on ST_Intersects(c.geom,r.geom));

This one joins each of the lines with each intersecting polygon and returns
the difference between the line and each intersecting polygon. Therefore
when the line intersects ten polygons I get ten geometries containing
different parts from the same input line.

Do you have any suggestions on how I could do this thing in a faster/better
way?

Thanks
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20131028/4a7da369/attachment.html>