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 ;