[postgis-users] Strange Left Join behaviour

[Arnaud Vandecasteele]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20141230/edf01210/attachment.html>