Project

General

Profile

1 9403 aaronmk
mailto:bboyle@email.arizona.edu/Brad_Boyle/2013-5-16/BIEN+DB+meeting+outstanding+issues
2
---
3
1) I finally found the missing function geodistkm. I implemented it directly as a stored function in database geoscrub in MySQL on nimoy:
4
5
mysql> show function status like "geodist%";
6
+----------+-----------+----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
7
| Db       | Name      | Type     | Definer         | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
8
+----------+-----------+----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
9
| 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  |
10
+----------+-----------+----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
11
1 row in set (0.01 sec)
12
13
mysql> show create function GeoDistKm;
14
+-----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
15
| Function  | sql_mode | Create Function                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | character_set_client | collation_connection | Database Collation |
16
+-----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
17
| GeoDistKm |          | CREATE DEFINER=`admin`@`localhost` FUNCTION `GeoDistKm`( lat1 FLOAT, lon1 FLOAT, lat2 FLOAT, lon2 FLOAT ) RETURNS float
18
BEGIN
19
  DECLARE pi, q1, q2, q3 FLOAT;
20
  DECLARE rads FLOAT DEFAULT 0;
21
  SET pi = PI();
22
  SET lat1 = lat1 * pi / 180;
23
  SET lon1 = lon1 * pi / 180;
24
  SET lat2 = lat2 * pi / 180;
25
  SET lon2 = lon2 * pi / 180;
26
  SET q1 = COS(lon1-lon2);
27
  SET q2 = COS(lat1-lat2);
28
  SET q3 = COS(lat1+lat2);
29
  SET rads = ACOS( 0.5*((1.0+q1)*q2 - (1.0-q1)*q3) );
30
  RETURN 6378.388 * rads;
31
END | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
32
+-----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
33
1 row in set (0.00 sec)
34
---