Skip site navigation (1) Skip section navigation (2)

Combine&Compare same table in Postgres

From: ar_gaeta <ar_gaeta(at)yahoo(dot)it>
To: pgsql-novice(at)postgresql(dot)org
Subject: Combine&Compare same table in Postgres
Date: 2011-06-09 10:10:33
Message-ID: 1307614233008-4472239.post@n5.nabble.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi all, I have 1 geometric table named "temp_sciami" with this structure:

gid integer, --> PRIMARY KEY
"SECTOR_ID" integer,
"FULL_DATE" timestamp without time zone,
"UTM_X" numeric(7,0),
"UTM_Y" numeric(7,0),
the_geom geometry --> POINT

and I want to find in this table the points that have:

a-same "SECTOR_ID"
b-"FULL_DATE" between +- interval '1 days' between each other
c-different "gid"
d-different couple of "gid"

I explain better with an example. I launch this SQL:

SELECT DISTINCT ON(T1.gid, T2.gid)
T1.gid as gid1, T2.gid as gid2
FROM temp_sciami T1, temp_sciami T2
WHERE
  T1."FULL_DATE" BETWEEN
  T2."FULL_DATE" - interval '1 days' AND
  T2."FULL_DATE" + interval '1 days' AND
  T1."SECTOR_ID" = T2."SECTOR_ID" AND
  T1.gid <> T2.gid;

And I get for example these rows:

gid1;gid2
1;3
1;9
2;5
2;6
2;8
2;328
2;1674
3;1
3;57
5;2

Now, I wouldn't like to have redundant couples of record. I mean, why I got
the couple (1;3) and (3;1), that represent the same combination?
Even if I put a GROUP BY clause I obtain always these duplicate couples of
rows.

Someone could help me on this trouble?
Hope to have been cleared enough.
I use PostgreSQL version 8.4.4 and Postgis version 1.4.

Thanks!

Riccardo


--
View this message in context: http://postgresql.1045698.n5.nabble.com/Combine-Compare-same-table-in-Postgres-tp4472239p4472239.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

pgsql-novice by date

Next:From: John DeSoiDate: 2011-06-09 14:23:49
Subject: Re: PL/pgSQL editor/debugger
Previous:From: John DeSoiDate: 2011-06-09 02:13:47
Subject: Re: PL/pgSQL editor/debugger

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group