Postgis: JOINS possible?

[Stefan Schwarzer]

Ok, ok, after some more searching I found something. With some  
tweaking and tuning I got something like this:

			DATA 'the_geom FROM (SELECT pop_total.y_2001 as y_2001,  
geometric_table.the_geom AS the_geom, pop_total.oid AS oid FROM  
pop_total, geometric_table WHERE geometric_table.id = pop_total.id)  
AS foo USING UNIQUE oid USING srid=-1'

Think this is alright....

Stef


> Hi there,
>
> I am only starting to understand the functionality of Postgis.  
> Although I got the first real-world examples running, I have  
> difficulties imaging something: As I am dealing with hundreds of  
> (for the moment still) shapefiles where only the dbf is different  
> (statistical data on national level), I wonder how to use postgis  
> to accomplish the same thing but only with one geometric table.
>
> What I wonna say is this: I guess I have to load only once the  
> shapefile, so that I can use my national statistics postgres tables  
> to link to this geometric table via a join, something like:
>
>                                     DATA  
> 'geometric_table .the_geom, pop_total.y_2001 from geometric_table,  
> pop_total WHERE geometric_table.id = pop_total.id'
>
> But this then suddenly results in multiple WHERE clauses:
>
> Warning: [MapServer Error]: prepare_database(): Error executing  
> POSTGIS DECLARE (the actual query) statement: 'DECLARE mycursor  
> BINARY CURSOR FOR SELECT y_2001::text,asbinary(force_collection 
> (force_2d(geometric_table.the_geom,  
> pop_total.y_2001)),'XDR'),OID::text from geometric_table, pop_total  
> WHERE geometric_table.geo_id = pop_total.id WHERE  
> geometric_table.the_geom, pop_total.y_2001 && setSRID('BOX3D(-180  
> -90,180 90)'::BOX3D, find_srid('','geometric_table, pop_total WHERE  
> geometric_table.geo_id = pop_total.id','geometric_table.the_geom,  
> pop_total.y_2001') )'
>
>
> Can anyone let me know how this works?
>
> Thanks a lot!

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20060217/849b37ed/attachment.htm>