| Home | Products | Issue Tracker | Service Providers | FAQ | Twitter | Download

# MySQL

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

- MySQL

  - Introduction

  - Connecting to Spatial Data in MySQL

    - Requirements

    - Verify MySQL Support in OGR Build

    - Test Connection with ogrinfo

    - Create MapServer Layer

  - Connecting to non-Spatial Data in MySQL

    - Requirements

    - Create .ovf file

    - Test Connection with ogrinfo

    - Create MapServer Layer

  - More Information

## Introduction

The following methods connect to MySQL through OGR’s MySQL driver, thus avoiding the need to set up an ODBC connection.

## Connecting to Spatial Data in MySQL

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.

### Requirements

- MapServer compiled with OGR support

- OGR/GDAL version 1.3.2 or more recent compiled with MySQL support

### Verify MySQL Support in OGR Build

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

### Test Connection with ogrinfo

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

### Create MapServer Layer

    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.

## Connecting to non-Spatial Data in MySQL

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.

### Requirements

- 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 (MapServer OGR document)

- Vector Data (MapServer Vector Data Access Guide)

- MySQL wiki page (describes the deprecated mygis support)