Created
July 14, 2015 18:28
-
-
Save yangkun/fdf9b25f9c1a331947eb to your computer and use it in GitHub Desktop.
[sql] get distance lat-lng
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE OR REPLACE FUNCTION calc_distance( | |
| pLat1 NUMBER, | |
| pLon1 NUMBER, | |
| pLat2 NUMBER, | |
| pLon2 NUMBER) | |
| RETURN NUMBER | |
| IS | |
| -- r is the spherical radius of earth in Kilometers | |
| cSpherRad CONSTANT NUMBER := 6367; | |
| -- The spherical radius of earth in miles is 3956 | |
| a NUMBER; | |
| vLat NUMBER; | |
| vLat1Rad NUMBER; | |
| vLat2Rad NUMBER; | |
| vLon NUMBER; | |
| vLon1Rad NUMBER; | |
| vLon2Rad NUMBER; | |
| BEGIN | |
| /* | |
| Most computers require the arguments of trigonometric functions to be | |
| expressed in radians. To convert lon1, lat1 and lon2,lat2 from | |
| degrees,minutes, seconds to radians, first convert them to decimal | |
| degrees. To convert decimal degrees to radians, multiply the number | |
| of degrees by pi/180 = 0.017453293 radians/degrees. | |
| */ | |
| vLat1Rad := pLat1 * 0.017453293; | |
| vLat2Rad := pLat2 * 0.017453293; | |
| vLon1Rad := pLon1 * 0.017453293; | |
| vLon2Rad := pLon2 * 0.017453293; | |
| vLon := vLon2Rad - vLon1Rad; | |
| vLat := vLat2Rad - vLat1Rad; | |
| a := POWER(SIN(vLat/2),2) + COS(vLat1Rad) * COS(vLat2Rad) * POWER(SIN(vLon/2),2); | |
| /* | |
| The intermediate result c is the great circle distance in radians. | |
| Inverse trigonometric functions return results expressed in radians. | |
| To express c in decimal degrees, multiply the number of radians by | |
| 180/pi = 57.295780 degrees/radian. | |
| The great circle distance d will be in the same units as r. | |
| */ | |
| RETURN ROUND(cSpherRad * 2 * ATAN2(SQRT(a), SQRT(1-a)),1); | |
| EXCEPTION | |
| WHEN OTHERS THEN | |
| RETURN 999; | |
| END; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment