Hi,
I have a challenging SQL problem, that I thought some of you might be
able and interested to try and solve.
My problem is that I need to compute some number of separate and costly
queries as represented VALUES clauses below. And I need to eliminate the
overlaps in the adjacent sub-selects, but not globally from the results.
So in this example
gid 6 and 7 would get eliminated from A and B and
gid 11 and 12 would get eliminated from B and C but
gid 1 would not get eliminated.
Sadly I'm currently working on Postgresql 8.3.9 other wise I think using
the WITH clause in 8.4 for would help.
So at this point I'm kind of thinking that the only way I can do this
with 8.3 is to "create temp table leg1 as select ..." and then work
with the temp tables. This will be running in a plpgsql function it that
matters.
I would be interested in ideas/examples for both 8.3 and 8.4.
Thanks,
-Steve
Here is a piece of not working SQL:
select *
from (
select gid, aa from
(values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7)) as A (gid, aa)
union all
select gid, aa from
(values (7,7),(6,6),(8,8),(9,9),(10,10),(11,11),(12,12)) as B (gid, aa)
union all
select gid, aa from
(values (12,12),(11,11),(13,13),(14,14),(15,15),(16,16),(1,1)) as C
(gid, aa)
) as foo
-- the where clause does not work, specifically the sub-select
-- this does global elimination,
-- and it needs to be locally between sub-selects
where gid not in (
(select gid from A intersect select gid from B)
union
(select gid from B intersect select gid from C)
)
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
