avoid duplicates with lateral auto join

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

avoid duplicates with lateral auto join

Olivier Leprêtre

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

 

 

 


Garanti sans virus. www.avast.com

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: avoid duplicates with lateral auto join

Darafei "Komяpa" Praliaskouski
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 <[hidden email]>:

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

 

 

 


Garanti sans virus. www.avast.com
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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:[hidden email]] 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 <[hidden email]>:

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://ipmcdn.avast.com/images/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif

Garanti sans virus. www.avast.com

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Loading...