[postgis-users] ST_Difference

Mon Apr 28 05:35:37 PDT 2014

HI Åsmund,

The first Thank You is for reply and the second is because you perfectly
understand my problem and you gave me the perfect answer !
Really, THANK  YOU !

2014-04-24 23:37 GMT+02:00 Åsmund Tokheim <asmundto at gmail.com>:

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

-- 
Pier Lorenzo Marasco
Via D.Moreni,2
50135 Firenze

cell/mobile   +39 329 35 37527
Casa/home  +39 055 011 71 20
skype pl.marasco
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140428/10cb19c4/attachment.html>