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
5
Medium Priority
?
946 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
[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
  • Learn & ask questions
  • 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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 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…

610 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