.. _mysql:

***************************************************
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::
    :depth: 3
    :backlinks: top


Introduction
============

The following methods connect to MySQL through OGR's `MySQL driver <http://www.gdal.org/ogr/drv_mysql.html>`__, 
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 <http://www.gdal.org/ogr/drv_mysql.html>`__ 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
=================

* :ref:`ogr` (MapServer OGR document)
* :ref:`vector` (MapServer Vector Data Access Guide)
* `MySQL wiki page <http://trac.osgeo.org/mapserver/wiki/MySQL>`__ (describes the deprecated mygis support)