[mapserver-users] ogr connect to mysql

[Sven Burbeck]

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
>

-- 
____________________________
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
____________________________