join/relate tables with CQL - advise needed

[Hannes Reuter]

Dear uDIG developers,

I have a question for you which eventually results in a request for uDIG
enhancement..

Situation:
uDIG connected to PostgisDB
1) point file loaded
2) another table loaded, both contain a common used id (table is 300MB),
this tables also contains a date column

QUESTION:
3) how do i write a CQL statement to join a) both tables and b) select
only one specific date.

>From the manual I understand that I should use a FILTER for where
functions and a Expression for a select function..
In postgis(aaaehhh in SQL) I would write something like (for a table or
view)
 
CREATE table myschema.V2007 AS
select myschema.station3.*,msmntval from
myschema.station3, myschema.sample_00007_day
WHERE
myschema.station3.eucode = myschema.sample_00007_day.eucode
AND extract (YEAR from myschema.sample_00007_day.sampledate) = 2007
AND extract (MONTH from myschema.sample_00007_day.sampledate) = 3
AND myschema.sample_00007_day.timestep = 3 ;

but in uDIG now ?


I was searching through the manuals, the email archives etc, but I'm
somehow clueless how to perform that join/relate.
Could anyone be so kind to share thoughts or codes to start with ?
Coming from an researchers perspective, this function would be quite a
needed feature.

hannes

PS.: I believe that I'm  probably not the first to ask that question.


_______________________________________________
User-friendly Desktop Internet GIS (uDig)
http://udig.refractions.net
http://lists.refractions.net/mailman/listinfo/udig-devel

[Jody Garnett-3]

So the quick answer is that the CQL syntax does not support the  
concept of "AS" - it is limited to just the concept of WHERE.

The long answer is that the ReShape operation can process one feature  
collection; and evaluate expressions to build up a new feature  
collection. For the AuScope remapping work we have made a function  
allow them to look up a value in a property file; and they also have  
the idea of "feature chaining" where they make an expression that at  
the end of the day will function as a join.

There work does exist now; I just have not had a chance to use it in  
uDig. I will try and hunt down some docs for you and report back. This  
work would be available for uDig 1.2.x as long as we add another  
couple of jars into the mix.

Cheers,
Jody

On 23/10/2009, at 1:56 AM, hannes wrote:

> Dear uDIG developers,
>
> I have a question for you which eventually results in a request for  
> uDIG
> enhancement..
>
> Situation:
> uDIG connected to PostgisDB
> 1) point file loaded
> 2) another table loaded, both contain a common used id (table is  
> 300MB),
> this tables also contains a date column
>
> QUESTION:
> 3) how do i write a CQL statement to join a) both tables and b) select
> only one specific date.
>
>> From the manual I understand that I should use a FILTER for where
> functions and a Expression for a select function..
> In postgis(aaaehhh in SQL) I would write something like (for a table  
> or
> view)
>
> CREATE table myschema.V2007 AS
> select myschema.station3.*,msmntval from
> myschema.station3, myschema.sample_00007_day
> WHERE
> myschema.station3.eucode = myschema.sample_00007_day.eucode
> AND extract (YEAR from myschema.sample_00007_day.sampledate) = 2007
> AND extract (MONTH from myschema.sample_00007_day.sampledate) = 3
> AND myschema.sample_00007_day.timestep = 3 ;
>
> but in uDIG now ?
>
>
> I was searching through the manuals, the email archives etc, but I'm
> somehow clueless how to perform that join/relate.
> Could anyone be so kind to share thoughts or codes to start with ?
> Coming from an researchers perspective, this function would be quite a
> needed feature.
>
> hannes
>
> PS.: I believe that I'm  probably not the first to ask that question.
>
>
> _______________________________________________
> User-friendly Desktop Internet GIS (uDig)
> http://udig.refractions.net
> http://lists.refractions.net/mailman/listinfo/udig-devel

_______________________________________________
User-friendly Desktop Internet GIS (uDig)
http://udig.refractions.net
http://lists.refractions.net/mailman/listinfo/udig-devel