FAQ
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.

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJun 9, '11 at 10:26a
activeJun 9, '11 at 10:26a
posts1
users1
websitepostgresql.org
irc#postgresql

1 user in discussion

Ar_gaeta: 1 post

People

Translate

site design / logo © 2022 Grokbase