Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:


CREATE DEFINER=`root`@`localhost` FUNCTION `CALCULATE_DISTANCEE`(Lati varchar(20),Longi varchar(20),MyLat decimal, MyLon decimal) RETURNS int(11)
    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;

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;
share|improve this question

closed as unclear what you're asking by ypercubeᵀᴹ, RolandoMySQLDBA, Kin, Max Vernon, Phil Apr 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
up vote 0 down vote accepted

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)) 
share|improve this answer

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.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.