Remove duplicate comparisons in postgres?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
1
down vote

favorite

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.

edited 22 mins ago

Colin 't Hart

6,30582332

asked 1 hour ago

user3052682

1061

New contributor

add a commentÂ |Â

up vote
1
down vote

favorite

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.

edited 22 mins ago

Colin 't Hart

6,30582332

asked 1 hour ago

user3052682

1061

New contributor

add a commentÂ |Â

up vote
1
down vote

favorite

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.

edited 22 mins ago

Colin 't Hart

6,30582332

asked 1 hour ago

user3052682

1061

New contributor

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.

postgresql distinct

edited 22 mins ago

Colin 't Hart

6,30582332

asked 1 hour ago

user3052682

1061

New contributor

edited 22 mins ago

Colin 't Hart

6,30582332

asked 1 hour ago

user3052682

1061

New contributor

edited 22 mins ago

Colin 't Hart

6,30582332

edited 22 mins ago

Colin 't Hart

6,30582332

edited 22 mins ago

Colin 't Hart

6,30582332

asked 1 hour ago

user3052682

1061

New contributor

asked 1 hour ago

user3052682

1061

asked 1 hour ago

user3052682

1061

New contributor

user3052682 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a commentÂ |Â

1 Answer
1

active

oldest

votes

up vote
2
down vote

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);

answered 38 mins ago

a_horse_with_no_name

36.9k771109

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

user3052682 is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f220092%2fremove-duplicate-comparisons-in-postgres%23new-answer', 'question_page');

);

Post as a guest

Name

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
2
down vote

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);

answered 38 mins ago

a_horse_with_no_name

36.9k771109

add a commentÂ |Â

up vote
2
down vote

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);

answered 38 mins ago

a_horse_with_no_name

36.9k771109

add a commentÂ |Â

up vote
2
down vote

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);

answered 38 mins ago

a_horse_with_no_name

36.9k771109

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);

answered 38 mins ago

a_horse_with_no_name

36.9k771109

answered 38 mins ago

a_horse_with_no_name

36.9k771109

answered 38 mins ago

a_horse_with_no_name

36.9k771109

answered 38 mins ago

a_horse_with_no_name

36.9k771109

add a commentÂ |Â

user3052682 is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

user3052682 is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky