<div dir="ltr"><div><div><div>Hi all,<br><br>Finally I've been able to do what I was looking for.<br>The tricks is to combine two SQL Queries inside a With and to use a NOT IN to exclude the objects from A that intersects B.<br></div>Here is my SQL query :<br><br>*************************<br>WITH diff_A_From_B AS(<br> SELECT A.id,<br> ST_Difference(<br> A.the_geom, B.the_geom <br> ), As the_geom<br> FROM A <br> LEFT JOIN B<br> ON A.the_geom && B.the_geom<br> AND ST_Intersects(A.the_geom, B.the_geom,0)<br> GROUP BY A.id<br>), <br>A_NOTIN_B_AND_DIFF AS(<br> SELECT A.id, A.the_geom<br> FROM A<br> WHERE A.id NOT IN (SELECT id FROM diff_A_From_B)<br> UNION ALL <br> SELECT * diff_A_From_B <br>)<br>SELECT * FROM A_NOTIN_B_AND_DIFF; <br>*************************<br><br></div>It looks like to do what I was looking for. If someone find a better way to do this, I will be happy to try !<br><br></div><div>Have a good day<br><br></div>Arnaud<br><div><div><br></div></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Tue, Dec 30, 2014 at 9:27 AM, Arnaud Vandecasteele <span dir="ltr"><<a href="mailto:arnaud.sig@gmail.com" target="_blank">arnaud.sig@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div><div><div><div><div><div>Hi Andy,<br><br></div>Thanks for your help, unfortunately, you solution doesn't seem to work.<br></div></div>From my point of view, it's normal perfectly that the intersection return null, this is why I've used a "LEFT OUTER JOIN" to return the objects from A that do not intersect B.<br></div>By using the "LEFT OUTER JOIN" I should get the objects from A that does not share any any space with B.<br></div>Am I wrong ?<br><br></div>Can I perform in a single SQL query the same QGIS Geoprocessing Difference operation ? <br><div><div><div><br></div><div>Thanks<span class="HOEnZb"><font color="#888888"><br><br>Arnaud<br></font></span></div><div><div><br><br></div></div></div></div></div><div class="gmail_extra"><div><div class="h5"><br><div class="gmail_quote">On Mon, Dec 29, 2014 at 7:06 PM, Andy Colson <span dir="ltr"><<a href="mailto:andy@squeakycode.net" target="_blank">andy@squeakycode.net</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div>On 12/29/2014 8:46 AM, Arnaud Vandecasteele wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hey all,<br>
<br>
It's been a couple of days that I'm trying to compute the difference<br>
(like the geoprocessing difference with QGIS) between two geometry<br>
(polygon) tables. These tables (let's call them A and B) contain<br>
polygons where :<br>
- polygon from A can be intersected by one or more polygon from B<br>
- polygon from A can have no spatial relationship with polygon from B<br>
(no intersection)<br>
<br>
To realize this difference I've joined the two tables with a "LEFT OUTER<br>
JOIN" so even if I don't have any intersection between A and B I should<br>
get the geometry from A.<br>
<br>
Below it's an example of the SQL query :<br>
******************************<u></u>***<br>
SELECT A.id,<br>
COALESCE(<br>
ST_Difference(<br>
A.the_geom,<br>
ST_Union(B.the_geom)<br>
),<br>
A.the_geom<br>
) As the_geom<br>
FROM A<br>
LEFT JOIN B ON A.the_geom && B.the_geom<br>
AND ST_Intersects(A.the_geom, B.the_geom)<br>
GROUP BY A.id;<br>
******************************<u></u>***<br>
<br>
The difference between objects from table A that are intersected by one<br>
or more objects from B is correct. But I don't get the objects from A<br>
that are not intersected by B. I don't understand why as I use a "LEFT<br>
OUTER JOIN" and the COALESCE function.<br>
<br>
Could you please tell me what I'm doing wrong or what I don't understand ?<br>
<br>
Thanks for your help.<br>
<br>
Arnaud<br>
<br>
<br>
</blockquote>
<br></div></div>
I'm assuming ST_Intersects will return null if B.the_geom is null. Try:<span><br>
<br>
LEFT JOIN B ON A.the_geom && B.the_geom<br></span>
AND (B.the_geom is null or ST_Intersects(A.the_geom, B.the_geom))<br>
<br>
-Andy<br>
<br>
<br>
<br>
______________________________<u></u>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-<u></u>bin/mailman/listinfo/postgis-<u></u>users</a><br>
</blockquote></div><br><br clear="all"><br></div></div><span class="">-- <br><div><div dir="ltr">--------------------------------------------------------------------<br>Arnaud Vandecasteele<br>SIG - WebMapping - Spatial Ontology - GeoCollaboration<br><br>Web Site<br><a href="http://geotribu.net/" target="_blank">http://geotribu.net/</a><br><a href="http://about.me/arnaud_vandecasteele" target="_blank">http://about.me/arnaud_vandecasteele</a><br><br></div></div>
</span></div>
</blockquote></div><br><br clear="all"><br>-- <br><div class="gmail_signature"><div dir="ltr">--------------------------------------------------------------------<br>Arnaud Vandecasteele<br>SIG - WebMapping - Spatial Ontology - GeoCollaboration<br><br>Web Site<br><a href="http://geotribu.net/" target="_blank">http://geotribu.net/</a><br><a href="http://about.me/arnaud_vandecasteele" target="_blank">http://about.me/arnaud_vandecasteele</a><br><br></div></div>
</div>