# Latitude and Longtitude Distance Calculation [closed]

I'm using MySQL and I am trying to calculate the distance in meters between two different longitude, latitude coordinates. I wrote a stored function in MySQL but it calculates just 1 for each row in select query. There is my stored function in the following segment:

``````DELIMITER \$\$

CREATE DEFINER=`root`@`localhost` FUNCTION `CALCULATE_DISTANCEE`(Lati varchar(20),Longi varchar(20),MyLat decimal, MyLon decimal) RETURNS int(11)
BEGIN
declare Lat decimal(12,12);
declare Lon decimal(12,12);
declare DistanceLat decimal(12,12);
declare DistanceLon decimal(12,12);
declare A decimal(12,12);
declare R decimal(12,12);
declare D decimal(12,12);
declare DistanceAsMeter decimal(12,12);
select CAST(Lati as Decimal(12,4)) into Lat;
select CAST(Longi as Decimal(12,4)) into Lon;

set DistanceLat = (MyLat - Lat) * PI() / 180;
set DistanceLon = (MyLon - Lon) * PI() / 180;

select SIN(DistanceLat/2) * SIN(DistanceLat/2) + COS(Lat*PI()/180) * COS(MyLat*PI() / 180) * SIN(DistanceLat / 2) * SIN(DistanceLat / 2) into A;
Select (2*ATAN2(SQRT(A),SQRT(1 - A))) into R;
select (RadiusOfWorld * R) into D;
select D*'1000' into DistanceAsMeter;
RETURN DistanceAsMeter;
END
``````

And also I am calling the function like this:

``````select *,`brain_db`.`CALCULATE_DISTANCEE`(stop.Stop_lat,stop.Stop_lon,'-157.818079','21.260340') AS Distance from stop;
``````
-

## closed as unclear what you're asking by ypercubeᵀᴹ, RolandoMySQLDBA, Kin, Max Vernon, PhilApr 30 '14 at 2:12

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

I might be missing something, but to calculate distance for 2 (long,lat) pairs, don't you need to call the function for both in the select rather than just for the '-157.818079','21.260340' you've hardcoded? – Phil Apr 16 '14 at 17:34
Yes, it must be hardcoded because, it's coming from outside of application and static for each row. – Kaan KILIC Apr 16 '14 at 17:38
What is your question? – Andriy M Apr 16 '14 at 19:12
I want to calculate distance between 2 different cordinaates. – Kaan KILIC Apr 16 '14 at 19:18
FWIW, this calculation is hack #36 in the book "SQL Hacks". – zx81 Apr 16 '14 at 22:09

Here's a really simple way to get the distance between two lat/lng pairs. It's not exact, but it's pretty close.

Replace lat1, lng1, lat2 and lng2 with your columns.

``````SELECT 1609.34 * 3959 * ACOS(
)
``````
-

1. `DECIMAL(12,12)` means 12 digits, where ALL 12 are behind a floating point (so basically, 0.*). To see an example, execute `SELECT CAST(23.45 AS DECIMAL(12,12));` and see what output does it give you.
2. I suggest that you change DECIMAL definitions to `DECIMAL(9,6)` which means 9 digits, where 6 are after the point. I think that would suffice for your calculations.
4. Why are you using `SELECT xx INTO yy` for setting variables? Why don't you just use `SET yy = xx;`?
5. Your function returns `INT`, which means that it will cancel everything after the floating point. Is this what you need?