Latitude and Longtitude Distance Calculation [closed]

Question

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 RadiusOfWorld 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;

    select 6378.137 into RadiusOfWorld;
    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;

Answer 1

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( 
        COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * COS( RADIANS(lng2) - RADIANS(lng1) )
        + SIN(RADIANS(lat1)) * SIN(RADIANS(lat2)) 
       )

Answer 2

Okay, few notions about your procedure

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.
3. Why are you escaping all your numbers?
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?

If you change all your decimal definitions properly (even the input ones), I think that your function should return proper result then (altough without the floating point). After that unquote your numbers (there is no need to quote them, even in your select query).

Let me know how it works out.