Hi all,
Finally I've been able to do what I was looking for.
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.
Here is my SQL query :
*************************
WITH diff_A_From_B AS(
SELECT A.id,
ST_Difference(
A.the_geom, B.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,0)
GROUP BY A.id
),
A_NOTIN_B_AND_DIFF AS(
SELECT A.id, A.the_geom
FROM A
WHERE A.id NOT IN (SELECT id FROM diff_A_From_B)
UNION ALL
SELECT * diff_A_From_B
)
SELECT * FROM A_NOTIN_B_AND_DIFF;
*************************
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 !
Have a good day
Arnaud
On Tue, Dec 30, 2014 at 9:27 AM, Arnaud Vandecasteele <arnaud.sig at gmail.com>
wrote:
> Hi Andy,
>
> Thanks for your help, unfortunately, you solution doesn't seem to work.
> 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.
> By using the "LEFT OUTER JOIN" I should get the objects from A that does
> not share any any space with B.
> Am I wrong ?
>
> Can I perform in a single SQL query the same QGIS Geoprocessing Difference
> operation ?
>
> Thanks
>
> Arnaud
>
>
>
> On Mon, Dec 29, 2014 at 7:06 PM, Andy Colson <andy at squeakycode.net> wrote:
>
>> 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 at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>
>
>
> --
> --------------------------------------------------------------------
> Arnaud Vandecasteele
> SIG - WebMapping - Spatial Ontology - GeoCollaboration
>
> Web Site
> http://geotribu.net/
> http://about.me/arnaud_vandecasteele
>
>
--
--------------------------------------------------------------------
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/20141230/3e2b5ccc/attachment.html>