# convert function from MSSQL to MYSQL

Hi Guys

Can anyone help me here.

I have this function for MSSQL. But need it converted to MYSQL.

--'=================================================================================
--' Distance Calculator
--'=================================================================================
--' This function calculates the distance between two latitude/logitude
--' coordinates. Disance can be returned as Kilometers or Miles.
--'
--' This function was designed for Microsoft SQL Server 2005
--'
--' Accepts:
--'      Lat1 = Latitude of point one (decimal, required)
--'      Lon1 = Longitude of point one (decimal, required)
--'      Lat2 = Latitude of point two (decimal, required)
--'      Lon2 = Longitude of point two (decimal, required)
--
--'
--' Provided by: http://www.zip-codes.com
--'
--'=================================================================================

CREATE function [Distance]( @lat1 decimal(12, 6) , @long1 decimal(12, 6) , @lat2 decimal(12, 6) , @long2 decimal(12, 6))
returns decimal(12,2)

as

begin

if @lat1 = @lat2 and @long1 = @long2
begin
return 0
end

declare @Ans as float
declare @Miles as decimal(12,2)

set @Ans = 0
set @Miles = 0

if @lat1 is null or @lat1 = 0 or @long1 is null or @long1 = 0 or @lat2 is
null or @lat2 = 0 or @long2 is null or @long2 = 0

begin

return ( @Miles )

end

set @Miles = 3959 * ATAN(SQRT(1 - SQUARE(@Ans)) / @Ans)

return ( @Miles )

end
LVL 1
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Hi Tim,

Here's a rewrite of the function for MySQL 5.1.  I confirmed it is syntactically correct, and will return a value with valid inputs (I cannot vouch for how it will behave with garbage).  If you are using an earlier version of MySQL, even 5.0, there may be slight differences in syntax.

``````delimiter \$\$
CREATE function Distance( lat1 decimal(12, 6) , long1 decimal(12, 6) , lat2 decimal(12, 6) , long2 decimal(12, 6))
returns decimal(12,2)
deterministic
begin
declare Miles decimal(12,2);

if (lat1 = lat2 and long1 = long2) then return 0;
end if;

set Ans = 0;
set Miles = 0;

if (lat1 is null or lat1 = 0 or long1 is null or long1 = 0 or lat2 is null or lat2 = 0 or long2 is null or long2 = 0) then
return ( Miles );
end if;

set Miles = 3959 * ATAN(SQRT(1 - (Ans * Ans)) / Ans);
return Miles;
end\$\$
delimiter ;
``````
Author Commented:
Thanks so much Danyell.

I am getting an error though when executing it

Column count of mysql.proc is wrong. Expected 20, found 16. Created with MySQL 50045, now running 50146. Please use mysql_upgrade to fix this error.

Do you know what this means?
Commented:
Yes, I know what it means.  It means you are running an older version of MySQL, *or* you're running a version of MySQL that wasn't properly upgraded -- the binary code / executables were updated, but the system database schema (database: mysql) was not updated.

try running this command:

And consult the documentation (man page or http://dev.mysql.com/doc/refman/5.1/en/mysqlcheck.html) to learn how to use this utility to update and/or repair your system-tables schema.
Commented:
Tim,

Were you able to repair your MySQL installation?

Also... I can vouch for the correctness of the translated function, and this has become a new thread about fixing/updating your MySQL installation.  Can we call the original issue solved?  I'm happy to help with your MySQL upgrade/update issues but perhaps that should be a separate problem?

Let me know + thanks!

Experts Exchange Solution brought to you by