[postgis-users] OT: SQL problem/challenge

[Birgit Laggner]

Hi Stephen,

I don't know if this is feasible with your data, but if you absolutely
want to avoid the use of temporary tables, you could simply replace the
A, B and C in the WHERE clause of your not working SQL query by the
corresponding VALUES clauses.

Regards,

Birgit.


On 05.06.2010 16:33, Stephen Woodbridge wrote:
> Stephen Woodbridge wrote:
>> 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)
>>   )
>
> With a little more work and I was able to get this to work with temp
> tables, although I would like to avoid temp tables if possible.
>
> create temp table A on commit drop as
>   select gid, aa from
>     (values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7)) as A (gid, aa);
>
> create temp table B on commit drop as
>   select gid, aa from
>     (values (7,7),(6,6),(8,8),(9,9),(10,10),(11,11),(12,12)) as B
> (gid, aa);
>
> create temp table C on commit drop as
>   select gid, aa from
>     (values (12,12),(11,11),(13,13),(14,14),(15,15),(16,16),(1,1)) as
> C (gid, aa);
>
> select * from (
>   select * from A where gid not in ((select gid from A intersect
> select gid from B))
>     union all
>   select * from B where gid not in (
>        ( select gid from A intersect select gid from B
>          union
>          select gid from B intersect select gid from C
>        )
>      )
>   union all
>   select * from C where gid not in ((select gid from B intersect
> select gid from C))
> ) as foo;
>
> So I assume, that in 8.4 that I can convert the temp tables to WITH
> clauses preceding the select. Is this the best that can be done with 8.3?
>
> -Steve
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>