Solved

convert function from MSSQL to MYSQL

Posted on 2010-08-24
5
904 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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

948 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now