Cheers Lester will check this out. Si
On 15 August 2011 21:00, Lester Caine <[email protected]> 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] ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
