[postgis-users] Strange Left Join behaviour

[Arnaud Vandecasteele]

Hey all,

It's been a couple of days that I'm trying to compute the difference (like
the geoprocessing difference with QGIS) between two geometry (polygon)
tables. These tables (let's call them A and B) contain polygons where :
 - polygon from A can be intersected by one or more polygon from B
 - polygon from A can have no spatial relationship with polygon from B (no
intersection)

To realize this difference I've joined the two tables with a "LEFT OUTER
JOIN" so even if I don't have any intersection between A and B I should get
the geometry from A.

Below it's an example of the SQL query :
*********************************
SELECT  A.id,
        COALESCE(
            ST_Difference(
                A.the_geom,
                ST_Union(B.the_geom)
            ),
            A.the_geom
    ) As the_geom
FROM A
LEFT JOIN B ON A.the_geom && B.the_geom
AND ST_Intersects(A.the_geom, B.the_geom)
GROUP BY A.id;
*********************************

The difference between objects from table A that are intersected by one or
more objects from B is correct. But I don't get the objects from A that are
not intersected by B. I don't understand why as I use a  "LEFT OUTER JOIN"
and the COALESCE function.

Could you please tell me what I'm doing wrong or what I don't understand ?

Thanks for your help.

Arnaud


-- 
--------------------------------------------------------------------
Arnaud Vandecasteele
SIG - WebMapping - Spatial Ontology - GeoCollaboration

Web Site
http://geotribu.net/
http://about.me/arnaud_vandecasteele
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20141229/e624ae64/attachment.html>