I used the following sql for mySQL but I now need to use this for a Firebird database. I have searched and read the Firebird documentation but can't seem to locate an alternative. In Firebird 'radians' and 'limit' both are not supported. Has anyone successfully done similar in Firebird?
SELECT zip, ( 3959 * acos( cos( radians(38.6285426) ) * cos( radians( lat ) )
* cos( radians( lng ) - radians(-86.05296039999999) ) + sin( radians(38.6285426) ) * sin(radians(lat)) ) ) AS distance
FROM zipcodes
HAVING distance < 25
ORDER BY distance
LIMIT 0 , 20;
For anyone having a similar issue, here was my solution for Firebird that returns all zips codes within a certain mile radius of a Lat/long (Great Circle) in one query.
select zipcode from(
SELECT zipcode, ( 3959 * acos( cos( 38.6285426/57.2958 ) * cos( lat/57.2958 )
* cos( lon/57.2958 - -86.05296039999999/57.2958 ) + sin( 38.6285426/57.2958 ) * sin(lat/57.2958) ) ) AS distance
FROM zip_codes)
where distance < 20
ORDER BY distance