Jeff Ready - 1 year ago 51

MySQL Question

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 Source

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