Jeff Ready Jeff Ready - 4 months ago 23
MySQL Question

Haversine formula on a Firebird database

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;

Answer

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