si_carter_987654321 wrote:

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

Not quite the same, but this provides me with all the locations within 'DIST' of
the of the selected target outcode. In this case 'SA5'. First stage filters on a
square DIST wide, so that the complex calculations are only done on postcodes
within range, and the second stage just returns the entry with the shortest
DIST. DIST is in miles ...

This does need FB2 to work, but I think you will find that the speed improvement
using this will justify the change.

> WITH
> Z AS
> ( SELECT r.ID, r.OUTCODE, r.LAT * 0.0174532925199 AS LAT_D, r.LNG * 0.0174532925199 AS LNG_D,
> ( SELECT LNG * 0.0174532925199 FROM OUTCODEPOSTCODES WHERE OUTCODE = 'SA5') AS LNG_H,
> ( SELECT LAT * 0.0174532925199 FROM OUTCODEPOSTCODES WHERE OUTCODE = 'SA5') AS LAT_H
> FROM OUTCODEPOSTCODES r
> WHERE OUTCODE <> 'SA5'
> ),
>
> Y AS
> ( SELECT FIRST 1 OUTCODE, SQRT( POWER((LNG_D - LNG_H) * COS( (LAT_D + LAT_H) / 2.0000) ,2) + POWER((LAT_D - LAT_H) ,2) ) * 3958.50 AS DIST
> FROM Z
> ORDER BY DIST
> )
>
> SELECT OUTCODE, DIST FROM Y
> WHERE DIST < 20

--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php