[mapserver-users] GetFeatureInfo using MySQL join - additional info

[Dejan Gambin]

I was finally successful with getting data from joined table through GetFeatureInfo. But:

1. It works only for PostgreSQL connection, not with MySQL - is it supposed to be so in MapServer 6? I saw the JOIN documentation changed and there is no MySQL mentioned any more/
2. I get nicely returned data from one-to-many join, using templates (thanks to Ted Spradley post). But gml returns only the main data, not the joined data. Is it supposed to be so?

Thanks very much

regards, dejan
On 13. ruj. 2011., at 09:00, Dejan Gambin wrote:

> Some additional info:
> 
> Since I need this data only for GetFeatureRequest, it seems like I should use JOIN within a LAYER object. So I did the following:
> 
> LAYER
> ...
>   DATA "my"
>   ...
>   ...
>   DUMP TRUE
>   TEMPLATE "template.html"
>   HEADER "template_header.html"
>   FOOTER "template_footer.html"
>   JOIN
>     NAME "test"
>     CONNECTION "MySQL:gis,host=localhost,user=gis,password=gis,tables=katastar_vodnjan"
> #    CONNECTION "PG:host=localhost user=postgres password=runda711 dbname=vodnjan port=5432"     
>     CONNECTIONTYPE ogr
>     TABLE "katastar_vodnjan"
>     FROM "KO_KC"
>     TO "broj_ko_kc"
>     TYPE ONE-TO-MANY
>     TEMPLATE "template_join.html"
> #	HEADER "template_header.html"
> #	FOOTER "template_footer.html"    
>   END  
> 
> I tried this with joined data from MySQL and from Postgres. No success. The interesting thing is, when I do:
> 
> shp2img -m my.map -o image.png -l "mylayer" -all_debug 3
> 
> I get:
> 
> [Tue Sep 13 08:58:04 2011].271467 msLoadMap(): 0.029s
> [Tue Sep 13 08:58:04 2011].356888 msDrawMap(): Layer 0 (katastar_vodnjan_poligon), 0.083s
> [Tue Sep 13 08:58:04 2011].356943 msDrawMap(): Drawing Label Cache, 0.000s
> [Tue Sep 13 08:58:04 2011].356956 msDrawMap() total time: 0.085s
> [Tue Sep 13 08:58:04 2011].399386 msSaveImage() total time: 0.042s
> [Tue Sep 13 08:58:04 2011].402773 msPOSTGRESQLJoinClose() already close or never opened.
> [Tue Sep 13 08:58:04 2011].402821 shp2img total time: 0.161s
> 
> Does anybody  know what is it? It happens for MySQL and Postgres database join
> 
> thanks very much
> 
> dejan
> 
> 
> On 12. ruj. 2011., at 14:03, Dejan Gambin wrote:
> 
>> Hi,
>> 
>> After solving my compile problem (thanks to thomas), I need to do GetFeatureInfo to a shape layer joined to MySQL table (non-spatial join).
>> 
>> I compiled MapServer with MySQL support and everything seems ok, but "mapserv -v" doesn't return "INPUT=MYSQL". Is it supposed to anyway? Or it is enough to be compiled with gdal/ogr that supports MySQL?
>> 
>> Because - when I do this:
>> 
>> ogrinfo -sql "SELECT * FROM my LEFT JOIN 'MYSQL:gis,host=localhost,user=gis,password=gis'.katastar_vodnjan ON my.KO_KC=katastar_vodnjan.broj_ko_kc" /var/www/data/vodnjan/my.shp
>> 
>> I get all the attributes from shape (dbf) and MySQL, so everything seems fine
>> 
>> But using thi:
>> 
>> LAYER # katastar vodnjan poligoni
>>   NAME "mylayer"
>>   STATUS ON
>>   CONNECTIONTYPE ogr  
>>   CONNECTION "/var/www/data/vodnjan/my.shp"
>>   DATA "SELECT * FROM my LEFT JOIN 'MYSQL:gis,host=localhost,user=gis,password=gis'.katastar_vodnjan ON my.KO_KC=katastar_vodnjan.broj_ko_kc"
>>   TYPE POLYGON  
>> ...
>> 
>> I don't get any information with GetFeatureInfo. The request is like:
>> 
>> http://www.coin.hr/cgi-bin/mywms61?SERVICE=WMS&VERSION=1.1.1&REQUEST=GetFeatureInfo&BBOX=5403800,4981350,5403980,4981460&SRS=EPSG:31275&WIDTH=400&HEIGHT=300&LAYERS=katastar_vodnjan_poligon&STYLES=&FORMAT=image/png&TRANSPARENT=true&QUERY_LAYERS=mylayer&INFO_FORMAT=gml&X=200&Y=100
>> 
>> Using the same request with regular mapfile:
>> 
>> LAYER # katastar vodnjan poligoni
>>   NAME "mylayer"
>>   STATUS ON
>>   DATA "my"
>>   TYPE POLYGON  
>> ...
>> 
>> returns one feature and its attributes from the dbf (tha is OK)
>> 
>> I have also tried with JOIN clause in LAYER part, but this doesn't work either. Do I have to use owf? Am I doing something wrong?
>> 
>> regards, dejan
>> _______________________________________________
>> mapserver-users mailing list
>> mapserver-users at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/mapserver-users
> 
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapserver-users/attachments/20110913/fcd5acc4/attachment-0001.html