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

[Nicolas Ribot]

Hi,

You could add a distinct clause after st_difference to filter out duplicate
results.
It may be enough to compare geometries bbox instead of real geometries,
depending on your dataset:

create table roadsdifference as (
with diff 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))
) select distinct on (geom::box2d) geom, osm_id
from diff;

Avoid unioning all geometries: index will not work well in this case.

Nicolas


On 20 February 2014 14:49, mdomnita <mdomnita at gmail.com> wrote:

> 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
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140220/7c1ce94b/attachment.html>