Home | Docs | Issue Tracker | FAQ | Download | |
Author: | David Fawcett |
---|---|
Contact: | david.fawcett at moea.state.mn.us |
Author: | Jeff McKenna |
Contact: | jmckenna at gatewaygeomatics.com |
Last Updated: | 2010-07-29 |
Contents
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.
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:
MYSQL:database,host=yourhost,user=youruser,password=yourpass,tables=yourtable
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.
Here is the .ovf file named aqidata.ovf
<OGRVRTDataSource>
<OGRVRTLayer name="aqidata">
<SrcDataSource>MYSQL:aqiTest,user=uuuuu,password=ppppp,host=192.170.1.100,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>
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
ogrinfo returns
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.