[help] distance function using the great-circle distance formula

[Christophe Naslain]

Hi,

Waiting for MySQL to support OpenGIS distance functionnality (http://forge.mysql.com/wiki/GIS_Functions WL#1326 seems to be for the server 6.x), I'm trying to create a stored function to calculate distance between two GPS waypoints.

I've written this function based on the great-circle distance formula :

DELIMITER $$

DROP FUNCTION IF EXISTS `test`.`distance` $$
CREATE FUNCTION distance(a POINT, b POINT) RETURNS double
DETERMINISTIC
COMMENT 'Spatial distance function using the great-circle distance formula (in km)'
RETURN ( 6378
* acos(
sin( radians(X(GeomFromText(a))) ) * sin( radians(X(GeomFromText(b))) )
+ cos( radians(X(GeomFromText(a))) ) * cos( radians(X(GeomFromText(b))) )
* cos( radians(Y(GeomFromText(b))) - radians(Y(GeomFromText(a))) )
)
) $$

DELIMITER ;



but unfortunately, this does not returns the expected result bt NULL.

Example:

mysql> SELECT distance(GeomFromText('POINT(11.11111 22.22222)'),GeomFromText('POINT(33.33333 44.44444)'));
+---------------------------------------------------------------------------------------------+
| distance(GeomFromText('POINT(11.11111 22.22222)'),GeomFromText('POINT(33.33333 44.44444)')) |
+---------------------------------------------------------------------------------------------+
| NULL |
+---------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

I've tested with another small function and I found that the X(GeomFromText(a)) and Y(GeomFromText(a)) returns NULL instead of the X or Y number of the POINT.

See this small test:

mysql> DELIMITER $$
mysql> DROP FUNCTION IF EXISTS `test`.`showlong` $$
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION showlong(pt1 POINT) RETURNS varchar(255)
-> DETERMINISTIC
-> COMMENT 'Tests'
-> RETURN X(GeomFromText(pt1)) $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> SELECT showlong(GeomFromText('POINT(11.11111 22.22222)'));

+----------------------------------------------------+
| showlong(GeomFromText('POINT(11.11111 22.22222)')) |
+----------------------------------------------------+
| NULL |
+----------------------------------------------------+
1 row in set (0.00 sec)

Does anyone knows why X(GeomFromText(pt1)) returns NULL in my last example and not 11.11111 ?

Running X(GeomFromText()) out of the stored function works well:

mysql> SELECT X(GeomFromText('Point(11.11111 22.22222)'));
+---------------------------------------------+
| X(GeomFromText('Point(11.11111 22.22222)')) |
+---------------------------------------------+
| 11.11111 |
+---------------------------------------------+
1 row in set (0.00 sec)

Best

Chris