Version 4 (modified by 15 years ago) ( diff ) | ,
---|
MySQL Spatial Support
The official document is at: http://www.mapserver.org/input/vector/mysql.html
Author: Mike Hostetler, A Mountain Top, LLC
MySQL Spatial + Mapserver + OGR
We were able to successfully render an output image from Mapserver with data stored in MySQL Spatial using OGR. There are many methods described around the web on how to successfully accomplish there, here's what we learned:
- As of 2009/07/31, MyGIS support is deprecated and shouldn't be used.
- The method of using OGR with a .ovf file didn't work for us, we were never able to find out why.
Step 1: Create your DSN
The first step necessary is to craft your OGR DSN. Documentation on how to craft this is available here: http://trac.osgeo.org/mapserver/wiki/VirtualSpatialData
For MySQL Specifically, the DSN should be formatted as:
MYSQL:##DATABASE##,host=##HOST##,user=##USERNAME##,pass=##PASSWORD##,tables=##TABLE##,##TABLE##,##TABLE##
Step 2: Test your DSN
After creating your DSN, test it using the utility ogrinfo
.
ogrinfo -ro -al MYSQL:##DATABASE##,host=##HOST##,user=##USERNAME##,pass=##PASSWORD##,tables=##TABLE## -sql 'SELECT geom FROM ##TABLE##'
Step 3: Edit your mapfile
After testing and verifying that OGR can connect and display your data in MySQL, you can drop your DSN and Data statement right into your mapfile. Specify the CONNECTIONTYPE as OGR.
LAYER NAME "##LAYERNAME##" STATUS OFF CONNECTIONTYPE OGR CONNECTION 'MYSQL:##DATABASE##,user=##USERNAME##,pass=##PASSWORD##,tables=##TABLE##,##TABLE##,##TABLE##' DATA 'SELECT geom AS the_geom FROM ##TABLE##' TYPE POINT METADATA "WMS_TITLE" "title" "WMS_ABSTRACT" "QRAs" "WMS_SRS" "EPSG:4326 EPSG:900913" "WMS_EXTENT" "-180 -90 180 90" END CLASS COLOR 255 0 0 SIZE 20 END END
Reference: TABLE Schema and Data
Here's the table structure that was used.
-- -- Table structure for table `oc_ds_data` -- CREATE TABLE IF NOT EXISTS `##TABLE##` ( `id` int(10) unsigned NOT NULL auto_increment, `geom` geometry NOT NULL, `field0` varchar(255) default NULL, `field1` varchar(255) default NULL, `field2` varchar(255) default NULL, `field3` varchar(255) default NULL, `field4` varchar(255) default NULL, `field5` varchar(255) default NULL, `field6` varchar(255) default NULL, `field7` varchar(255) default NULL, `field8` varchar(255) default NULL, `field9` varchar(255) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; INSERT INTO `##TABLE##` (`id`, `geom`, `field0`, `field1`, `field2`, `field3`, `field4`, `field5`, `field6`, `field7`, `field8`, `field9`) VALUES (1, GeomFromText('POINT(-105.08148 39.75366)'), ' One', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), (2, GeomFromText('POINT(-105.07148 39.85366)'), ' Two', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), (3, GeomFromText('POINT(-105.06148 39.95366)'), ' Three', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), (4, GeomFromText('POINT(-105.05148 40.05366)'), ' Four', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), (5, GeomFromText('POINT(-105.04148 40.15366)'), ' Five', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);