Cheers Lester will check this out.

Si

On 15 August 2011 21:00, Lester Caine <lester@...> wrote:

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


[Non-text portions of this message have been removed]