MySQL

Author:

David Fawcett

Contact:

david.fawcett at moea.state.mn.us

Author:

Jeff McKenna

Contact:

jmckenna at gatewaygeomatics.com

Last Updated:

2021-05-05

Introduction

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

More Information:

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 functionality

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.