mailto:bboyle@email.arizona.edu/Brad_Boyle/2013-5-16/BIEN+DB+meeting+outstanding+issues

---

1) I finally found the missing function geodistkm. I implemented it directly as a stored function in database geoscrub in MySQL on nimoy:

mysql> show function status like "geodist%";

+----------+-----------+----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

| Db       | Name      | Type     | Definer         | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |

+----------+-----------+----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

| geoscrub | GeoDistKM | FUNCTION | admin@localhost | 2011-09-23 17:14:24 | 2012-07-24 19:01:55 | DEFINER       |         | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |

+----------+-----------+----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+

1 row in set (0.01 sec)

mysql> show create function GeoDistKm;

+-----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

| Function  | sql_mode | Create Function                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | character_set_client | collation_connection | Database Collation |

+-----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

| GeoDistKm |          | CREATE DEFINER=`admin`@`localhost` FUNCTION `GeoDistKm`( lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT ) RETURNS float

BEGIN

  DECLARE pi, q1, q2, q3 FLOAT;

  DECLARE rads FLOAT DEFAULT 0;

  SET pi = PI();

  SET lat1 = lat1 * pi / 180;

  SET lon1 = lon1 * pi / 180;

  SET lat2 = lat2 * pi / 180;

  SET lon2 = lon2 * pi / 180;

  SET q1 = COS(lon1-lon2);

  SET q2 = COS(lat1-lat2);

  SET q3 = COS(lat1+lat2);

  SET rads = ACOS( 0.5*((1.0+q1)*q2 - (1.0-q1)*q3) );

  RETURN 6378.388 * rads;

END | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |

+-----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

1 row in set (0.00 sec)

---