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