[postgis-users] Strange Left Join behaviour
Arnaud Vandecasteele
arnaud.sig at gmail.com
Mon Dec 29 06:46:58 PST 2014
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>
More information about the postgis-users
mailing list