[mapserver-users] ogr connect to mysql

[Sven Burbeck]

Hi Albert,
stupid me!

It was indeed simple like that.
I didn't read the f.. manual properly. So I didn't realize, that the 
tables need to be addressed in both, the connection, as well as the 
SQL-statement.

Thanks a lot for your hint!

Sven



Am 11.10.2011 10:37, schrieb Albert Rovira:
> Hi,
>
> This is an  OGR feature that by default get all database metadata  ...
>
> You can disable it adding the table parameter on OGR call (see 
> http://www.gdal.org/ogr/drv_mysql.html)
>
> I don't know how can you get this result with mapserver.
>
>
>
>
> Al 11/10/2011 8:32, En/na Sven Burbeck ha escrit:
>> Hi list,
>> unfortunately I'm still facing the same problem, so I would like to 
>> give it a trial once more.
>>
>> Isn't there anybody who at least has an idea, what the problem could be?
>>
>> cheers,
>> Sven
>>
>>
>> Am 07.10.2011 10:31, schrieb Sven Burbeck:
>>> Hi group,
>>> I have a question about ogr-connect to MySql database.
>>>
>>> Yesterday I encountered that my MySQL-DB logged several SQL-Requests 
>>> for a single (simple) MapServer request.
>>> My mapfile stores a SQL-Statement like that:
>>> ##
>>> SELECT centroid(b.geom), b.id, a.name, date(b.measure_date) as date, 
>>> b.value as wert
>>> FROM locality a RIGHT JOIN result b ON a.id = b.id
>>> ##
>>>
>>> However, my MySQL-DB logs the following:
>>> ##
>>> 111007  7:24:22    19 Connect   user_xy at localhost on myDB
>>>                    19 Query     SHOW TABLES
>>>                    19 Query     DESCRIBE `table_1`
>>>                    19 Query     SELECT srid FROM geometry_columns 
>>> WHERE f_table_name = 'table_1'
>>>                    19 Query     DESCRIBE `table_2`
>>>                    19 Query     SELECT srid FROM geometry_columns 
>>> WHERE f_table_name = 'table_2'
>>>                    19 Query     DESCRIBE `table_3`
>>>                    19 Query     SELECT srid FROM geometry_columns 
>>> WHERE f_table_name = 'table_3'
>>>                    19 Query     DESCRIBE `table_4`
>>>                    19 Query     SELECT srid FROM geometry_columns 
>>> WHERE f_table_name = 'table_4'
>>>            ...
>>>            ...
>>>                    19 Query     SELECT centroid(b.geom), b.id, 
>>> a.name, date(b.measure_date) as date, b.value as wert FROM locality 
>>> a RIGHT JOIN result b ON a.id = b.id
>>>                    19 Quit
>>> ##
>>>
>>> I wondered where all these "SHOW TABLES", "DESCRIBE table..." came 
>>> from, since the results are completely unnecessary in order to 
>>> retrieve the final "SELECT centroid(b.geom)..." resultset, aren't they?
>>>
>>> So I did a simple OGR check.
>>> First for the table I would like to retrieve the "geom" column from:
>>> ##
>>> ogrinfo MYSQL:myDB,user=user_xy,password=pwd_xy,port=3306 result 
>>> -summary
>>> INFO: Open of `MYSQL:myDB,user=user_xy,password=pwd_xy,port=3306'
>>>       using driver `MySQL' successful.
>>>
>>> Layer name: result
>>> Geometry: Polygon
>>> Feature Count: 1680
>>> Extent: (6.034624, 47.396320) - (15.028760, 55.023074)
>>> Layer SRS WKT:
>>> GEOGCS["GCS_WGS_1984",
>>>     DATUM["WGS_1984",
>>>         SPHEROID["WGS_1984",6378137,298.257223563]],
>>>     PRIMEM["Greenwich",0],
>>>     UNIT["Degree",0.017453292519943295]]
>>> FID Column = some_column_with_integers
>>> Geometry Column = geom
>>> ...
>>> ##
>>>
>>> Next check was just for an ogrinfo-connect with the sql-statement 
>>> attached directly:
>>> ##
>>> ogrinfo MYSQL:myDB,user=user_xy,password=pwd_xy,port=3306 result 
>>> -sql 'SELECT centroid(b.geom), b.id, a.name, date(b.measure_date) as 
>>> date, b.value as wert FROM locality a RIGHT JOIN result b ON a.id = 
>>> b.id'
>>> ##
>>>
>>> T my surprise all these ogrinfo requests invoked the described bulk 
>>> of SQL-statements. Apparently it doesn't matter if I use table-join 
>>> or not.
>>>
>>> So my question is:
>>> Does Mapserver connect to MySQL using the "ogrinfo" request and is 
>>> there any way to suppress all these (in my opinion) unnecessary 
>>> "Describe table ... / SELECT srid..." queries?
>>>
>>> These are the versions I use:
>>> MapServer version 5.6.5
>>> GDAL 1.8.0, released 2011/01/12
>>> mysql  Ver 14.14 Distrib 5.5.10, for Linux (x86_64) using readline 5.1
>>>
>>>
>>> Any help would be appreciated!
>>>
>>> thanks in advance,
>>> Sven
>>>
>>
>
>
> -- 
>
> Albert Rovira Brugués
> Nexus Geografics 902 102255
> http://www.nexusgeografics.com
>
> |Abans d'imprimir aquest missatge pensa en el medi ambient.|
>
> |La informació inclosa en aquest missatge és CONFIDENCIAL. Si vostè 
> llegeix aquest missatge i no és el destinatari indicat, l'informem que 
> està totalment prohibida qualsevol utilització, divulgació, 
> distribució i/o reproducció d'aquesta comunicació, total o parcial, 
> sense autorització expressa en virtut de la legislació vigent. Si ha 
> rebut aquest missatge per error, li preguem que ens ho notifiqui 
> immediatament per aquesta via i procedeixi a la seva eliminació junt 
> amb els fitxers annexes sense llegir-lo ni gravar-lo. |
>

-- 
____________________________
Bundesamt für Strahlenschutz (BfS)
SW 2.1
Rosastr. 9
D-79098 Freiburg
phone: ++49 (0)3018 333 6741
fax: ++49 (0)3018 10 333 6741
____________________________

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20111011/7e34005c/attachment.htm>