Haversine formula on a Firebird database

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;

score 4 · Answer 1 · answered Aug 03 '16 at 05:43

4

The radians function in mySQL "returns the argument X, converted from degrees to radians". You don't need builtin function to do that, it's rather simple math: radians = degrees × π / 180º. You could create an convenience view with calculated columns for deg-to-rad conversion, to make the query easier to read. BTW, Firebird has builtin function for retrieving π value.

Instead of LIMIT Firebird supports ROWS syntax:

SELECT <columns> FROM ...
   [WHERE ...]
   [ORDER BY ...]
   ROWS <m> [TO <n>]

answered Aug 03 '16 at 05:43

ain

22,394
3
54
74

While I completely appreciate your answer, I probably should have detailed my question a little more. Yes I understand the math and yes it's simple math. mySQL returns all my zipcodes within 25 miles with that one simple query. My question I guess should have been doesn't firebird have a similar option without having to do some separate additional functions and calculations and make things more complex than they need to be in order to get the same result. – Jeff Ready Aug 03 '16 at 15:10
IMO creating an view which has `lat` and `lng` as radians is pretty simple and makes the final query nicer too (instead of `radians(nn.mmm)` you have just `lat` or `lng`). But you could also create an UDF for radians function if you prefer that... but you can't use the MySQL query as is in Firebird, no. – ain Aug 03 '16 at 15:22
Sidenote: If you use Firebird 3, you can also create a UDF without having to create a native library to provide the function. – Mark Rotteveel Aug 06 '16 at 07:04

score 1 · Accepted Answer · edited Aug 06 '16 at 07:03

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

Haversine formula on a Firebird database

2 Answers2