[postgis-users] Erase from PostGIS line table using polygon table (like Erase in ArcGIS)

[mdomnita]

I have two sets of data: a line table (roads) and a polygon table
(localities)

Both sets are very large and contain thousands of geometries.

I need all portions of geometries from the line table that lay outside the
polygon table (in my case all the parts of roads outside localities);

I tried something like:

create table roadsdifference as (select
 r.osm_id as osm_id, ST_Difference(r.geom,c.geom) geom
 from
roads r join localities c on ST_Crosses(c.geom,r.geom));


And it works if each road joins only one locality. In case I have roads
crossing two or more localities I get partly overlapping multigeometries,
one for each locality (I know this is how a join works).

I also tried:

create table localitiesunion as select st_union(geom) geom from localities;

create table roadsdifference as (select
 r.osm_id as osm_id, ST_Difference(r.geom,c.geom) geom
 from
roads r join localitiesunion c on ST_Crosses(c.geom,r.geom));


But this option either takes forever or throws a topology error and crashes.

As I said, I have about 50k locality polygons and 5 mil. road linestrings.

Using the Erase function in ArcGIS does the job ok but I have to convert the
tables to shapefiles, process them, convert them back etc. and this is a lot
of time-consuming manual work. I would like an automated solution that I can
include in a script (or an .sql file) and that does not assume buying an
expensive software.

Any other ways or ideas on how to do this?




--
View this message in context: http://postgis.17.x6.nabble.com/Erase-from-PostGIS-line-table-using-polygon-table-like-Erase-in-ArcGIS-tp5005691.html
Sent from the PostGIS - User mailing list archive at Nabble.com.