Yesterday evening, a friend of mine had some issues with installing DBD::mysql, and asked if I had encountered the same issue. The problem, as the output from make test showed, was that certain symbols was missing:
# Tried to use 'DBD::mysql'. # Error: Can't load '/Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle' for module DBD::mysql: dlopen(/Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle, 2): Symbol not found: _is_prefix
Fair enough, this is related to a 64-bit issue with MySQL—at least with my Perl version, which is now:
Summary of my perl5 (revision 5 version 10 subversion 0) configuration:
Platform:
osname=darwin, osvers=9.5.0, archname=darwin-thread-multi-64int-2level
If you try to link to a x86_64 version of MySQL, then you get the above mentioned error. So, I downloaded an x86 version of MySQL and tried again. The output from perl Makefile.PL:
cflags (mysql_config) = -I/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/include -g -Os -arch i386 -fno-common -D_P1003_1B_VISIBLE -DSIGNAL_WITH_VIO_CLOSE -DSIGNALS_DONT_BREAK_READ -DIGNORE_SIGHUP_SIGQUIT -DDONT_DECLARE_CXA_PURE_VIRTUAL embedded (mysql_config) = libs (mysql_config) = -L/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/lib -lmysqlclient -lz -lm -lmygcc mysql_config (Users choice) = /Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/bin/mysql_config
That looks all good and nice, the right paths, versions and everything. So I compiled it OK, but when running make test again: (more…)
In case you are processing and working with geospatial data on MySQL, you may be interested in the following UDF (plugin) for MySQL 5.1: Koji Okumura from Oki Labs Japan has ported two functions from PostGIS into a MySQL UDF:
Since it's an UDF, it can be easily added to an already installed server. These two functions actually complement the improved precise GIS functions nicely (which provide a 2-dimensional DISTANCE() function).
You can download the UDF source tarball from here. Compiling it is pretty straightforward and requires a local copy of the MySQL 5.1 sources and the ususal build environment. For my testing, I used the 5.1 source tree that includes the additional precise geospatial functions. Below I just list the commands required to build and create the binary tarball and omit the lengthy output in between some of these commands:
$ bzr branch lp:~mysql/mysql-server/mysql-5.1-wl1326
$ cd mysql-5.1-wl1326
$ BUILD/compile-pentium-max
$ make bin-dist
You should now have a binary tarball mysql-5.1.26-rc-linux-i686.tar.gz that you can install or deploy by using the MySQL Sandbox, which I used for my further testing:
$ tar zxvf mysql-5.1.26-rc-linux-i686.tar.gz -C ~/opt/mysql
$ cd ~/opt/mysql
$ mv mysql-5.1.26-rc-linux-i686 5.1.26
$ make_sandbox 5.1.26
$ cd ~/sandboxes/msb_5_1_26
$ ./use
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.26-rc Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql [localhost] {msandbox} ((none)) >
Success! Let's see if the new GIS functions work:
mysql [localhost] {msandbox} ((none)) > SELECT DISTANCE (GEOMFROMTEXT('POINT(0 0)'),GEOMFROMTEXT('POINT(1000 1000)')) AS distance;
+-----------------+
| distance |
+-----------------+
| 1414.2135623731 |
+-----------------+
1 row in set (0.00 sec)
Now let's compile and install the UDF that provides the new spherical distance functions. Download the sources and extract the tarball in the same directory in which you branched the MySQL server sources:
$ tar zxvf mysql-udf-distance_spheroid-1.0.tar.gz
$ cp distance_spheroid/*.{cc,h} mysql-5.1-wl1326/sql
$ cd !$
$ g++ -DMYSQL_SERVER -shared -o udf_distance_spheroid.so -I../regex -I../sql -I../include spatial.cc calc_distance_spheroid.cc udf_distance_spheroid.cc
This will build the shared object udf_distance_spheroid.so that you now have to copy into the plugins directory of your server:
$ install -D udf_distance_spheroid.so ~/opt/mysql/5.1.26/lib/mysql/plugin
Now we have to load the UDF and make the new functions known to the server. Get back into your sandbox or fire up the commandline client again:
mysql [localhost] {msandbox} ((none)) > CREATE FUNCTION distance_sphere RETURNS REAL SONAME "udf_distance_spheroid.so";
mysql [localhost] {msandbox} ((none)) > CREATE FUNCTION distance_spheroid RETURNS REAL SONAME "udf_distance_spheroid.so";
Now you're ready to experiment with these new functions! For my test case, I calculated the distance between Hamburg, Germany (where I currently live) and Heidelberg, Germany (where I grew up) by providing the Lat/Lon coordinates as points:
mysql [localhost] {msandbox} ((none)) > SELECT DISTANCE_SPHERE(GEOMFROMTEXT('POINT(53.583333 9.983333)'),GEOMFROMTEXT('POINT(49.412222 8.71)')) AS 'Distance (m)';
+-------------------+
| Distance (m) |
+-------------------+
| 479037.4799112912 |
+-------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} ((none)) > SELECT DISTANCE_SPHEROID(GEOMFROMTEXT('POINT(53.583333 9.983333)'),GEOMFROMTEXT('POINT(49.412222 8.71)'),'SPHEROID["GRS_1980",6378137,298.257222101]') AS 'Distance (m)';
+-------------------+
| Distance (m) |
+-------------------+
| 479345.1884839106 |
+-------------------+
1 row in set (0.00 sec)
Interestingly, using the virtual ruler on Google Earth gave me a distance of 471881.44 meters for the same coordinates. Not sure where the difference comes from, I assume they are using a slighly different projection system? It would be interesting to see, if these results match what you would get out of PostGIS using the same queries.
In any case, these two functions may come in handy, if your application needs to calculate distances between two points on a map. So give it a try! If you have tested this functionality and want to give feedback to Koji, please reply to his post on the MySQL GIS Forum.

opensource: del.icio.us tag/opensource
Software
linux
distributed
HOWTO
gentoo
opensource
compiling