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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | John DeSoi | 2011-06-09 14:23:49 | Re: PL/pgSQL editor/debugger |
Previous Message | John DeSoi | 2011-06-09 02:13:47 | Re: PL/pgSQL editor/debugger |