Solved

convert function from MSSQL to MYSQL

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PL/SQL query 14 50
Updating variable table 9 17
SQL: export into csv/sqlcmd method and field config 3 19
SQL JOIN + SUBQUERY? 3 14
Creating and Managing Databases with phpMyAdmin in cPanel.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 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

22 Experts available now in Live!

Get 1:1 Help Now