[postgis-users] ST_Difference

[Pier Lorenzo Marasco]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140424/17403854/attachment.html>