SqlServer - ATAN2 bearing calculation fails

[mmmries]

I couldn't find any existing issues filed for this. Sorry if it is a duplicate.

I'm using SqlServer at work (grudgingly) and using the standard SomeModel.near method results in this error:

ActiveRecord::StatementInvalid: TinyTds::Error: 'ATAN2' is not a recognized built-in function name.

It looks like it is used to calculate the bearing. I am able to work around the problem by using something like:

MyModel.near('eiffel tower', 20.0, select_bearing: false)

But I had to dig through the source a bit for this. Maybe we should add some documentation about sql server or else change the default select_bearing option to be false when using TinyTds?

[alexreisner]

SqlServer is not officially supported but if you want to write something up on the issues around using it I think that'd be a good candidate for a wiki page (which is coming soon).

[mmmries]

Thanks. I'll keep an eye out for when the wiki happens and see if I can contribute that way.

[ttilberg]

I came across this issue today and worked around it as described below. This is old, but I wanted to leave a note here since this is directly relevant to this issue.

I'm not sure if Alex would be open to a pull request to add such functionality, but generally speaking, the are two changes that should happen to the bearing related sql make this more smooth in SQL Server:

• references to ATAN2 should be ATN2 when SQL Server
• references to MOD(a,b) should be rewritten as A % B

For my quick fix, I just overwrote the method. But if it were desirable to improve SQL Server support for this gem, here are my notes:

There are already methods for detecting postgres and sqlite, so adding sqlserver detection wouldn't be too intrusive (reference /tinytds|mssql|sqlserver/). Being that the bearing SQL is just a string, it would be easy to template the ATAN2 function from a variable, like sql_atan2 (not sure where this would be best implemented). Regarding modulo, I see that postgres also supports the a % b syntax, but I'm not sure what else this gem supports or it's impact. It could be reasonable to simply rewrite the SQL using the % operator.

Here is how I rewrote the method to accommodate SQL server. I added this to my initializers and it seems to work.

https://gist.github.com/ttilberg/fe5ac60351ad33b865550c08857f612f