[firebird-support] help translating MySQL to ISQL

[si_carter_987654321]

Hi,

I use the following 2 stored procedures in MySQL to find nearest
postcode to the postcode that is input.  Could anybody help translate
these to ISQL for me as I am really struggling.

I am currenlty using FB 1.5.x but am about to upgrade to latest
version if that helps.

kind regards

Si



/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE,
SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER' */ $$
CREATE DEFINER=`heaven_mysql`@`%` FUNCTION `GeoDistKM`( lat1 FLOAT,
lon1 FLOAT, lat2 FLOAT, lon2 FLOAT ) RETURNS float
BEGIN
 DECLARE pi, q1, q2, q3 FLOAT;
 DECLARE rads FLOAT DEFAULT 0;
 SET pi = PI();
 SET lat1 = lat1 * pi / 180;
 SET lon1 = lon1 * pi / 180;
 SET lat2 = lat2 * pi / 180;
 SET lon2 = lon2 * pi / 180;
 SET q1 = COS(lon1-lon2);
 SET q2 = COS(lat1-lat2);
 SET q3 = COS(lat1+lat2);
 SET rads = ACOS( 0.5*((1.0+q1)*q2 - (1.0-q1)*q3) );
 RETURN 6378.388 * rads;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of function `GetDistance`
--

DROP FUNCTION IF EXISTS `GetDistance`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE,
SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER' */ $$
CREATE DEFINER=`heaven_mysql`@`%` FUNCTION `GetDistance`(
 lat1  numeric (9,6),
 lon1  numeric (9,6),
 lat2  numeric (9,6),
 lon2  numeric (9,6)
) RETURNS decimal(10,5)
   READS SQL DATA
BEGIN
 DECLARE  x  decimal (20,10);
 DECLARE  pi  decimal (21,20);
 SET  pi = 3.14159265358979323846;
 SET  x = sin( lat1 * pi/180 ) * sin( lat2 * pi/180  ) + cos(
 lat1 *pi/180 ) * cos( lat2 * pi/180 ) * cos(  abs( (lon2 * pi/180) -
 (lon1 *pi/180) ) );
 SET  x = acos( x );
 RETURN  ( 1.852 * 60.0 * ((x/pi)*180) ) / 1.609344;
END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;