MySQL
'''WARNING: this page was created for the deprecated 'mygis' support in !MapServer. Please see the [http://www.mapserver.org/input/vector/mysql.html MapServer MySQL] document for updatodate instructions'''
The official MySQL with !MapServer doc lives at: http://www.mapserver.org/input/vector/mysql.html
This page should provide information about using data sources from a non-spatially enabled MySQL server. It should work with both 3.x and 4.x versions of MySQL. If you have any questions or suggestions, contact the author (Attila Csipa).
Using a mysql data source means that BOTH spatial and feature data are stored in mysql tables. If you want shapefiles (.SHP) for spatial data and mysql for feature data (usually stored in the .DBF files), you might want to take a look at JoinDatasources
== Installing ==
-
Get the source from CVS.
-
Get Shapelib (http://www.cpan.org/modules/by-module/Geo/ -> Geo-Shapelib-X)
-
Make sure you have mysql server and client libraries installed (you might also need the mysql-dev packages to compile), and that their version matches.
-
Do everything as in a regular build but add the '--with-mygis' when doing ./configure
-
Convert your existing data (.SHP files, .DXF, MapInfo, etc.) source to OpenGIS Simple Features compliant database in mysql
-
Change .map file(s) to suit the new data sources
== Converting SHP data into MySQL based OpenGIS SFSQL ==
The easiest way to do this is to use the shp2mysql.pl perl script supplied with !MapServer (if you do not have it, chances are that you are not using a recent and complete version). To use this conversion script you MUST install the perl binding for shapelib (http://www.water.hut.fi/pub/Grid/Shape-0.04.tar.gz). First, you have to edit the database access parameters in the shp2mysql file itself. After that, simply type
shp2mysql.pl /path/to/shp/file/my_shape_file
to convert the shape file as a new layer. If all goes well, you should see a progress indicator and after a while the shape file will be converted. You should repeat this step for all shape files/layers you wish to use.
The converter will also automatically convert all data from .dbf files to the MySQL database's feature table, but note that the fields will be prefixed with "f_" to avoid name collisions with existing data columns. Thus, if you have a 'color' field, it will become 'f_color'.
== Altering the .map file to use sql data sources ==
In the .map file use the following syntax (use 'bin' or 'num' in the last field depending on stored data type - binary or numerical):
LAYER
CONNECTIONTYPE mygis
NAME "Telefon"
CONNECTION "hostname:user:password:database:bin:"
DATA "geometry from TELEFON_LINE feature, TELEFON_LINE_bin geometry"
FILTER "feature.myattrib=22"
STATUS ON
TYPE LINE
CLASS
COLOR 55 65 0
END
END
In this example, hostname, user, password and database specify the connection parameters to the MySQL database. Do not forget to add bin: to the end of the string (This specifies binary data access) ! The two tables used in the above example are thus TELEFON_LINE and TELEFON_LINE_bin. Same thing for an annotation layer but note that the labelitem and classitems are columns of the feature table !
LAYER
CONNECTIONTYPE mygis
CONNECTION "host:user:password:database:bin:"
DATA "geometry from TEKSTULICE_TEXT feature, TEKSTULICE_TEXT_bin geometry"
NAME "tekstulice"
STATUS ON
TYPE ANNOTATION
LABELITEM "txt"
CLASSITEM "angle"
LABELANGLEITEM "angle"
CLASS
COLOR -1 -1 -1
LABEL
COLOR 6 6 24
SHADOWCOLOR -1 38 38
SHADOWSIZE 1 1
TYPE TRUETYPE
FONT verdana
SIZE 10
POSITION CC
PARTIALS FALSE
BUFFER 2
END
END
END
That's it, all the other MapServer features can be used in conjuction with this.
== FAQ ==
=== How does this work ? ===
Through a dedicated !MapServer module written in C.
=== How do I use it ? ===
See the Installing chapter above.
=== I tried that, but configure says there is no such option ! ===
Make sure you have a recent version of Mapserver. The MyGIS? module has been added on 2003. 03. 15. in version 3.7
Mapserver dies with the following -
Warning: [MapServer Error]: msMYGISLayerOpen?(): msMYGISLayerOpen? called but unimplemented! mapserver not compiled with MYGIS support
As it says, you have not compiled MYGIS support. Currently NONE of the binary distributions come with MYGIS in them, you HAVE to compile it yourself.
=== I have downloaded the windows version and it does not work ===
If you downloaded the windows binary (in fact, any binary) chances are that it was not compiled with mysql support.
=== I have MySQL 4.x installed and the compile fails, or Mapserver dies randomly ===
Get the newest version from CVS, these problems have been addressed
=== There is no shp2mysql.pl file in my Mapserver distribution ! ===
Get the newest version from CVS.
=== How do I convert my shapefile data to mysql ? ===
See 'converting .shp files' in the installchapter.
=== Fields I had in my SHP files are lost ! ===
Not lost, but renamed. All field names are prefixed with "f_" to avoid name collisions with existing columns. If you are unsure what you have in the database you can always do an 'EXPLAIN [FEATURE_TABLE]' to see the columns/fields available.
=== What is the data format used for storage ? ===
The data is encoded in a format called OpenGIS Simple Feature format, though it is not (yet) fully compliant. It supports both numerical and binary (WKB) storage types in little and big endian encodings.
=== I have a Mac and Mapserver times out (or takes very long time to load, why is that ? ===
The module converts between little-endian and big-endian (PC and Mac) data transparently. However, this conversion is VERY slow in any real world application. The solution is to use a data set which is in a native format and thus needs no conversion.
=== The query faild but no error message is given ! ===
In some cases debugging is hard, indeed. Your best bet is to turn on mysql logging (usually in mysql.ini) and then check mysql.log to see the complete and exact query that fails.
=== Why use MySQL instead of postgis + postgreSQL (or eventually postgis + MySQL when it becomes available) ? ===
Stored procedures (which will be used by postgis) will appear in mysql 4.1 at the earliest, but it looks like this particular feature may slip to even later, since it recently disappeared from the 4.1 features list (but this might be an error in the list itself, tho, I'm not on the mysql dev lists). Second, maybe even more important that this is NOT a mysql implementation of postgis, but rather a separate approach. As you maybe noticed my connector implementation is based on almost pure ansi sql, so with minimal changes it can be used practically with ANY sql database. It IS slower than postgis, but even with postgis-mysql 4.1 available, this solution is an option because it does not require to jump on a relatively unstable new mysql, does not require to install postgis if you do not already have it and, finally, it does not require additional knowledge beside plain sql to use and modify. So basically it is a 'sql-lite' connector - compatible with practically everything and easy to use, the downside being slower than a regular postgis approach.
=== I heard the mapMyGIS? module does not work with MySQL 4.0 ? ===
CVS builds after 22.01.2004 should work just fine with all 3.x and 4.x versions of MySQL
== Known limitations ==
If you are NOT using shp2mysql, please note that
- OpenGIS data in binary format must be stored as GEOMETRY_COLLECTION
- OpenGIS data in numerical format must have 2 dimensions/points per entry (but entries can be constructed from multiple items that have 2 points, even complex polygons !)
- Untested under Windows
Other than that
- Slow
- Buggy
== Required shp2mysql.pl edits ==
use Shape qw/:all/; becomes Use Geo::Shapelib qw/:all/;
$shape = new Shape $ARGV[0]; becomes $shape = new Geo::Shapelib $ARGV[0];
== Windows HOWTO ==
If you have any questions or suggestions for the Windows HOWTO, contact (BartVanDenEijnden).
- Install ActiveState Perl (http://www.activestate.com/Products/Download/Register.plex?id=ActivePerl). I use version 5.8.0.804.
- Install the ppm for DBI from http://ppm.activestate.com/PPMPackages/zips/8xx-builds-only/Windows/DBI.zip, followed by "ppm install DBI.ppd" in a DOS prompt
- Install Shapelib from http://www.cpan.org/modules/by-module/Geo/Geo-Shapelib-0.06.tar.gz, here comes the trouble, there is no ppm for Geo-Shapelib, only for Geo-Shapefile.
