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.