[mapserver-users] PostGIS layer with JOIN: fails on queryByPoint

[Marco Foi]

Hi all,
I'm having a problem with a layer coming from a PostGIS connection.
This layer has a JOIN relating the geometry table 
with a second table holding text data:

[...]
DATA 'the_geom FROM (SELECT 
thegeom_elementi_puntuali.*, colate.* FROM 
thegeom_elementi_puntuali LEFT JOIN colate ON 
thegeom_elementi_puntuali.uc_lege=colate.uc_lege) 
AS foo USING UNIQUE gid USING SRID=3004'
[...]

The layer works perfectly to retrieve data but 
FAILS when is queryed using queryByPoint

The problems seems to be the presence of two 
fields with the same name: "uc_lege".
I tried to fully qualify the names of the field in this way:

[...]
DATA 'the_geom FROM (SELECT 
thegeom_elementi_puntuali.gid, 
thegeom_elementi_puntuali.the_geom,thegeom_elementi_puntuali.uc_lege, 
colate.* FROM thegeom_elementi_puntuali LEFT JOIN 
colate ON 
thegeom_elementi_puntuali.uc_lege=colate.uc_lege) 
AS foo USING UNIQUE gid USING SRID=3004'
[...]

but still the problem arises:

Fatal error: [MapServer Error]: 
msPOSTGISLayerGetShape(): Error executing POSTGIS 
SQL statement (in FETCH ALL): DECLARE mycursor2 
BINARY CURSOR FOR SELECT 
"gid"::text,"uc_lege"::text,"uc_lege"::text,"acronym"::text,"lava"::text,"interval"::text,"formation"::text,"lithosom"::text,"synthem"::text,"supersynthem"::text,"lithology"::text,"dist_morph"::text,"year"::text,"eff_rate"::text,"add_info"::text,asbinary(force_collection(force_2d(the_geom)),'NDR') 
from (SELECT 
thegeom_elementi_puntuali.gid,thegeom_elementi_puntuali.the_geom,thegeom_elementi_puntuali.uc_lege, 
colate.* FROM thegeom_elementi_puntuali LEFT JOIN 
colate ON 
thegeom_elementi_puntuali.uc_lege=colate.uc_lege) 
AS foo WHERE gid = 71 -ERROR: column reference 
"uc_lege" is ambiguous at character 56 More Help: 
in /var/www/apache2-default/wrift/index.php on line 84

Any suggestion? (and WHY DOES THIS HAPPEN??)

Marco Foi


Dip. di Scienze della Terra "A.Desio"
Sezione di Geologia e Paleontologia
http://www.gp.terra.unimi.it
Università degli Studi di Milano
Via.Luigi Mangiagalli n.34 Cap.20133 Milano
Laboratorio Informatico - Tel. 02- 503 15 503
E-mail marco.foi at unimi.it  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapserver-users/attachments/20081021/d0b249f7/attachment-0001.html