MySQL Forums
Forum List  »  GIS

[help] distance function using the great-circle distance formula
Posted by: Christophe Naslain
Date: September 22, 2008 09:59AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
[help] distance function using the great-circle distance formula
10263
September 22, 2008 09:59AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.