On 12/29/2014 8:46 AM, Arnaud Vandecasteele wrote:
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
I'm assuming ST_Intersects will return null if B.the_geom is null. Try: LEFT JOIN B ON A.the_geom && B.the_geom AND (B.the_geom is null or ST_Intersects(A.the_geom, B.the_geom)) -Andy _______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
