[postgis-users] avoid duplicates with lateral auto join

[Olivier Leprêtre]

Very smart Darafei, thanks !

I was not aware of the ctid column

 

De : postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] De la part de Darafei "Kom?pa" Praliaskouski
Envoyé : dimanche 28 mai 2017 16:30
À : PostGIS Users Discussion
Objet : Re: [postgis-users] avoid duplicates with lateral auto join

 

Add something like 

where o.ctid < t1.ctid 

to the inner query.
Also, the query doesn't need to be lateral join,

select 
a.name,
b.name
from t1 a, t1 b
where ST_Intersects(a.geom, b.geom) 
and a.ctid < b.ctid;

should work the same.

 

вс, 28 мая 2017 г. в 15:18, Olivier Leprêtre <o.lepretre at gmail.com>:

Hi,

 

Doing a lateral auto join return cross results like A,B and B,A. Is there a simple trick to avoid that ?

 

To explain : in such query

 

select o.name,nam1 from 

t1 o,

lateral (select o.name,t1.nam nam1 from t1

where st_overlaps(o.geom,t1.geom)) lat

order by o.name

 

I get results like

 

polygon1       polygon2

polygon2       polygon1

 

because polygon1 overlap polygon2 and conversely.

 

how could I get only one of those pairs ?

 

Thanks,

 

Olivier

 

 

 

 


 <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient> https://ipmcdn.avast.com/images/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif

Garanti sans virus.  <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient> www.avast.com 

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users



---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170528/bc9d5f59/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.gif
Type: image/gif
Size: 7810 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170528/bc9d5f59/attachment.gif>