david.fawcett at moea.state.mn.us
jmckenna at gatewaygeomatics.com
- Last Updated
The following methods connect to MySQL through OGR’s MySQL driver, thus avoiding the need to set up an ODBC connection.
This section describes how to display a spatial MySQL table (meaning that the table has a column of type geometry) in MapServer. OGR’s MySQL driver was expanded in OGR version 1.3.2 to support access to MySQL spatial tables.
MapServer compiled with OGR support
OGR/GDAL version 1.3.2 or more recent compiled with MySQL support
You can verify that your local build of OGR contains MySQL support by using the ogrinfo commandline utility, and making sure that “MySQL” is returned:
ogrinfo --formats Supported Formats: -> "ESRI Shapefile" (read/write) -> "MapInfo File" (read/write) ... -> "PostgreSQL" (read/write) -> "MySQL" (read/write) ...
MySQL connection strings in OGR are in the following format:
Therefore an example ogrinfo command would be:
> ogrinfo MYSQL:test,user=root,password=mysql,port=3306
which should return a list of all of your tables in the ‘test’ database:
INFO: Open of `MYSQL:test,user=root,password=mysql,port=3306' using driver `MySQL' successful. 1: province (Polygon)
and you can return a summary of the MySQL spatial layer:
> ogrinfo MYSQL:test,user=root,password=mysql,port=3306 province -summary INFO: Open of `MYSQL:test,user=root,password=mysql,port=3306' using driver `MySQL' successful. Layer name: province Geometry: Polygon Feature Count: 48 Extent: (-13702.315770, 3973784.599548) - (1127752.921471, 4859616.714055) Layer SRS WKT: PROJCS["ED50_UTM_zone_30N", ... FID Column = OGR_FID Geometry Column = SHAPE id: Real (2.0) ...
LAYER NAME "spain_provinces_mysql_spatial" TYPE POLYGON STATUS DEFAULT CONNECTIONTYPE OGR CONNECTION "MySQL:test,user=root,password=mysql,port=3306" DATA "SELECT SHAPE,admin_name from province" LABELITEM "admin_name" CLASS NAME "Spain Provinces" STYLE COLOR 240 240 240 OUTLINECOLOR 199 199 199 END LABEL COLOR 0 0 0 FONT sans TYPE truetype SIZE 8 POSITION AUTO PARTIALS FALSE OUTLINECOLOR 255 255 255 END END END # layer
The DATA parameter is used to perform the SQL select statement to access your table in MySQL. The geometry column is required in the select statement; in the above example the SHAPE column is the geometry column in the province table.
This section describes how to display a non-spatial MySQL table (meaning the table does not have a column of type geometry) in MapServer.
Support for this functionality is found in GDAL/OGR 1.2.6 and older on Windows and GDAL/OGR 1.3.2 on Linux.
MySQL database containing a table with fields containing x and y coordinates
.ovf file, a small xml file you will create
MapServer compiled with OGR version supporting this functionality
Here is the .ovf file named aqidata.ovf
<OGRVRTDataSource> <OGRVRTLayer name="aqidata"> <SrcDataSource>MYSQL:aqiTest,user=uuuuu,password=ppppp,host=22.214.171.124,port=3306,tables=testdata</SrcDataSource> <SrcSQL>SELECT areaID, x, y, sampleValue FROM testdata</SrcSQL> <GeometryType>wkbPoint</GeometryType> <GeometryField encoding="PointFromColumns" x="x" y="y"/> </OGRVRTLayer> </OGRVRTDataSource>
If you look at the connection string in <SrcDataSource>
The MySQL database name is ‘aqiTest’
‘testdata’ is the table containing the coordinate data
host and port are for MySQL server
Use the GeometryField element to tell OGR which fields store the x and y coordinate data. Mine are simply named x and y.
# usr/local/bin/ogrinfo /maps/aqidata.ovf
ERROR 4: Update access not supported for VRT datasources. Had to open data source read-only. INFO: Open of `/maps/aqidata.ovf' using driver `VRT' successful. 1: aqidata (Point)
Don’t worry about the error, this is just telling you that it is a read-only driver. If it really bugs you, call ogrinfo with the -ro (read only) flag.
To see the actual data
# usr/local/bin/ogrinfo /maps/aqidata.ovf -al
LAYER NAME "MyAqi" STATUS DEFAULT TYPE POINT CONNECTIONTYPE OGR CONNECTION "aqidata.ovf" DATA "aqidata" CLASS NAME "MyClass" STYLE SYMBOL 'circle' SIZE 15 COLOR 0 255 0 END END END
DATA in the LAYER definition should be the same as the name attribute of the OGRVRTLayer element in the ovf file.
For this to draw, you need to have a SYMBOLSET defined in your mapfile and have a symbol called ‘circle’ in your symbols.sym file.