MySQL

Revision:$Revision: 8472 $ Date:$Date: 2009-01-28 13:08:43 -0800 (Wed, 28 Jan 2009) $ Author:David Fawcett Contact:david.fawcett at moea.state.mn.us

Contents

• MySQL
  • Introduction
  • Create .ovf file
  • Test Connection with ogrinfo
  • Create MapServer Layer
  • More Information

Introduction

This method takes advantage of OGR’s MySQL driver and avoids the need to set up an ODBC connection.

This is the primitive MySQL support for point data, not the recently added MySQL spatial support found in GDAL/OGR 1.3.2 .

Support for this functionality is found in GDAL/OGR 1.2.6 and older on Windows and GDAL/OGR 1.3.2 on Linux.

The MySQL wiki page might contain additional information.

Needed

• 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 functinality

Create .ovf file

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>

• 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.

Test Connection with ogrinfo

# 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

Create MapServer Layer

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.

More Information

• OGR
• Vector Data