2 replies to this topic

[Haynesmic]

alright, i've got a website that provides a list of shooting ranges in different states, and im trying to make a MySQL Stored Procedure which will calculate and return the distance between the two sets of latitude and longitude that are passed to it... the code i have is the following:

DELIMITER $$

CREATE PROCEDURE northeastranges.asdf(IN lat1 INT,IN lon1 INT,IN lat2 INT,IN lon2 INT)
    BEGIN
	SELECT ( 3959 * ACOS( COS( RADIANS(lat1) ) * COS( RADIANS( lat2 ) ) * COS( RADIANS( lon2 ) - RADIANS(lon1) ) + SIN( RADIANS(lat1) ) * SIN( RADIANS( lat2 ) ) ) );
    END$$
    
DELIMITER ;

there definately seems to be a logical error in the code, because the following data provides the responses beside them:

CALL asdf(42.20264,-71.43606,42.25390,-71.45835) 
Yeilds: 0
CALL asdf(42.253909,-71.45835,42.711545,-71.16700) 
Yeilds: 69.097585086449
CALL asdf(42.151363,-71.42172,42.166938,-71.36069)
Yeilds: 0

None of which are correct...

can anyone offer me any insight as to whats going wrong here?

[Arctic Fire]

Your variables being set to INT is one problem. I'm not sure what the correct answer is, but this is what I got when I ran the code.

SET @lat1 = 42.20264;

SET @lon1 = -71.43606;

SET @lat2 = 42.25390;

SET @lon2 = -71.45835;

SELECT ( 3959 * ACOS( COS( RADIANS(@lat1) ) * COS( RADIANS( @lat2 ) ) * COS( RADIANS( @lon2 ) - RADIANS(@lon1) ) + SIN( RADIANS(@lat1) ) * SIN( RADIANS( @lat2 ) ) ) );

I got 3.7210235452627.

[Haynesmic]

Arctic Fire said:

Your variables being set to INT is one problem. I'm not sure what the correct answer is, but this is what I got when I ran the code.

SET @lat1 = 42.20264;

SET @lon1 = -71.43606;

SET @lat2 = 42.25390;

SET @lon2 = -71.45835;

SELECT ( 3959 * ACOS( COS( RADIANS(@lat1) ) * COS( RADIANS( @lat2 ) ) * COS( RADIANS( @lon2 ) - RADIANS(@lon1) ) + SIN( RADIANS(@lat1) ) * SIN( RADIANS( @lat2 ) ) ) );

I got 3.7210235452627.

your post got me thinking.... it was throwing the incorrect result because they're int's.. they dont support decimal places... everything just got turnicated, changed them to floats and i got the correct result