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. _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
