.. _mysql:

***************************************************
MySQL
***************************************************

:Revision: $Revision$
:Date: $Date$
:Author: David Fawcett
:Contact: david.fawcett at moea.state.mn.us

.. contents::
    :depth: 2
    :backlinks: top


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 <http://trac.osgeo.org/mapserver/wiki/MySQL>`__ 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
=================

* :ref:`ogr` 
* :ref:`vector`

.. #### rST Link Section ####

.. _`MySQL spatial support found in GDAL/OGR 1.3.2`: http://www.gdal.org/ogr/drv_mysql.html