New UDF for MySQL 5.1 provides GIS functions distance_sphere() and distance_spheroid()
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:
- distance_sphere(point, point): Returns linear distance in meters between two lat/lon points. Uses a spherical earth and radius of 6370986 meters. Faster than distance_spheroid(), but less accurate. Only implemented for points
- distance_spheroid(point, point, spheroid): Returns linear distance between two lat/lon points given a particular spheroid. Currently only implemented for points.
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.
