Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# convert function from MSSQL to MYSQL

Posted on 2010-08-24
Medium Priority
946 Views
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
0
Question by:tim_carter
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 2

Expert Comment

ID: 33514501
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 ;
``````
0

LVL 1

Author Comment

ID: 33514923
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?
0

LVL 2

Expert Comment

ID: 33515187
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.
0

LVL 2

Accepted Solution

danyell earned 2000 total points
ID: 33524727
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!
0

LVL 1

Author Closing Comment

ID: 33531957
Thanks Alot. I had to repair some tables, and to upgrade them
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
What we learned in Webroot's webinar on multi-vector protection.
Viewers will learn how the fundamental information of how to create a table.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
###### Suggested Courses
Course of the Month9 days, 17 hours left to enroll