I have a polygon layer named scale_polygons_v4. I want to get ids of overlapping polygons. I have tried the code below:

select distinct s1.id,s2.id from scale_polygons_v4 s1 
inner join scale_polygons_v4 s2
on s1.id!=s2.id and s1.kind=s2.kind and st_overlaps(s1.geom,s2.geom)

The results are correct, but with duplicate comparisons it returns for example:

10029161,10011031
10011031,10029161

There are multiple such cases I only want to get one comparison.

up vote 4 down vote accepted

You can use least() and greatest() for this:

select distinct least(s1.id, s2.id), greatest(s1.id, s2.id) 
from scale_polygons_v4 s1 
  join scale_polygons_v4 s2
    on s1.id <> s2.id 
   and s1.kind = s2.kind 
   and st_overlaps(s1.geom,s2.geom);

Another option is to do this in the join condition:

select distinct s1.id, s2.id 
from scale_polygons_v4 s1 
  join scale_polygons_v4 s2
    on s1.id > s2.id 
   and s1.kind = s2.kind 
   and st_overlaps(s1.geom,s2.geom);

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.