[postgis-users] ST_Difference

[Åsmund Tokheim]

Hi

I'm not sure if I completely understand what you are trying to achieve, but
I'll give it a try anyways. In your second sql query, you don't seem to be
using the unione table you created, so it seems to me that you are not
taking the union of the polygons after all. Also the OR-ing of st_crosses
and st_contains with st_intersects is redundant as any geometry crossing or
containing another geometry must also intersect that geometry. In addition,
st_difference will leave the geometry unmodified if it is completely
outside of the other geometries, so you shouldn't need the coalesce and
intersects-combo either.

As far as I understand this might be closer to what you want:
SELECT st_difference(geom, (
  SELECT st_union(geom) FROM rt_201
)) AS geom
FROM u_rt_801


Åsmund


On Thu, Apr 24, 2014 at 3:56 PM, Pier Lorenzo Marasco
<pl.marasco at gmail.com>wrote:

> Hi everyone,
>
> I'm a little bit bogged down using ST_Difference. Probably there is
> something that I'm doing wrong, but I'm novice and I don't know what I'm in
> wrong .
> I've two tables, the first one represents some lines (linestring 3d ) and
> the second one represent some houses (polygon 3d). I need to to cut away
> all the line's parts that fall inside the polygons (in 2d), keeping all the
> lines that doesn't touch anything.
>
> CREATE TABLE unione AS SELECT St_Union(geom) as geom FROM rt_201;
> CREATE TABLE results AS SELECT NEXTVAL('serial'),
> COALESCE(ST_Difference(a.geom,b.geom),a.geom) as geom FROM u_rt_801 as a
> LEFT JOIN  rt_201 as b ON ST_Crosses(a.geom, b.geom) OR ST_Intersects
> (a.geom, b.geom) OR ST_Contains (a.geom, b.geom)
>
> Some lines intersect more than one object; to solve this problem,
> following some advice, I've made an union of all the polygons.
> More or less everything is working but at the end I've some lines that are
> not cutted. In the result I notice that, most of the time, the wrong lines
> are all the lines that don't cross all the polygon and that have a small
> part outside the polygon. Obviously there are some cases that contradict
> this theory (lines completely inside, lines crossing completely
> polygons...). In the fake results, most of the time, there are two lines;
> one is the correct one and the other one is the original one. Is that
> correlated to the "COALESCE" option ?
> In Qgis everything is working as expected...
> Tnx,
>
> L.
>
> here the original files...
> ​
>  rt_201.dbf<https://docs.google.com/file/d/0B1ZgaWuX8IaDY3dkWVh1blpXV1k/edit?usp=drive_web>
> ​​
>  rt_201.shp<https://docs.google.com/file/d/0B1ZgaWuX8IaDU19OR1FkNktkaHc/edit?usp=drive_web>
> ​​
>  rt_201.shx<https://docs.google.com/file/d/0B1ZgaWuX8IaDbHN4N3NDcFRCUXc/edit?usp=drive_web>
> ​​
>  u_rt_801.dbf<https://docs.google.com/file/d/0B1ZgaWuX8IaDNTc3U0dpVmt1N00/edit?usp=drive_web>
> ​​
>  u_rt_801.shp<https://docs.google.com/file/d/0B1ZgaWuX8IaDWUxQa0pFQ3RCajg/edit?usp=drive_web>
> ​​
>  u_rt_801.shx<https://docs.google.com/file/d/0B1ZgaWuX8IaDdEx6R2daekNMTjQ/edit?usp=drive_web>
> ​
>
> --
> P.L. Marasco
>
>
> _______________________________________________
> 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/20140424/9d698b90/attachment.html>