?
Solved

convert function from MSSQL to MYSQL

Posted on 2010-08-24
5
Medium Priority
?
953 Views
Last Modified: 2012-05-10
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
--'
--' © 2005 Zip-Codes.com, All Rights Reserved
--'=================================================================================

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 @DegToRad as float
declare @Ans as float
declare @Miles as decimal(12,2)

set @DegToRad = 57.29577951
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 @Ans = SIN(@lat1 / @DegToRad) * SIN(@lat2 / @DegToRad) + COS(@lat1 / @DegToRad ) * COS( @lat2 / @DegToRad ) * COS(ABS(@long2 - @long1 )/@DegToRad)

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

return ( @Miles )

end
0
Comment
Question by:tim_carter
  • 3
  • 2
5 Comments
 
LVL 2

Expert Comment

by:danyell
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 DegToRad, Ans float;
	declare Miles decimal(12,2);

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


	set DegToRad = 57.29577951;
	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 Ans = SIN(lat1 / DegToRad) * SIN(lat2 / DegToRad) + COS(lat1 / DegToRad ) * COS( lat2 / DegToRad ) * COS(ABS(long2 - long1 )/DegToRad);
	set Miles = 3959 * ATAN(SQRT(1 - (Ans * Ans)) / Ans);
	return Miles;
end$$
delimiter ;

Open in new window

0
 
LVL 1

Author Comment

by:tim_carter
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

by:danyell
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:

mysqlcheck --check-upgrade mysql

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

by:
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

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question