Haversine formula for SQL SERVER

Question

Guys, I have an Android app that uses Google Maps API. On the map, I get data from an SQL Server database. I need to create a KM radius where I only upload the database data that’s inside this radius. This based on the current geolocation of the user’s device.

I used Mysql, in it I could do it using this formula:

SELECT DISTINCT filiais.icon_logo_maps,
                filiais.latitude_fil,
                filiais.longitude_fil,
                filiais.nr_cnpj_filial,
                (6371 acos(cos(radians(latitude_dispositivo))cos(radians(filiais.latitude_fil))cos(radians(longitude_dispositivo) - radians(filiais.longitude_fil))sin(radians(latitude_dispositivo))sin(radians(filiais.latitude_fil)) AS distance
  FROM _using.produto
       INNER JOIN _using.filiais ON _using.produto.cnpj_filial = _using.filiais.nr_cnpj_filial
 WHERE _using.produto.subcat = 'mercad'
HAVING distance <= 7;

How I could use this same formula for SQL Server?

In SQL SERVER I find the following error:

com.microsoft.sqlserver.jdbc.Sqlserverexception: Column name invalid Distance.

Answer 1

To SQL Server 2008 or higher there is the type GEOGRAPHY that "translates" the latitude and longitude in a location. Once this is done you can calculate it before using for its validation.

WITH relacao AS (
  SELECT DISTINCT f.icon_logo_maps,
                  f.latitude_fil,
                  f.longitude_fil,
                  f.nr_cnpj_filial,
                  CAST('POINT(' + p.latitude_dispositivo + ' ' + p.longitude_dispositivo + ')' AS GEOGRAPHY).STDistance(
                  CAST('POINT(' + f.latitude_fil + ' ' + f.longitude_fil + ')' AS GEOGRAPHY)) AS distance
    FROM _using.produto p
         INNER JOIN _using.filiais f ON p.cnpj_filial = f.nr_cnpj_filial
   WHERE p.subcat = 'mercad'
)
SELECT r.icon_logo_maps,
       r.latitude_fil,
       r.longitude_fil,
       r.nr_cnpj_filial
  FROM relacao r
 WHERE r.distance <= 7;

POINT

Applies to: SQL Server (SQL Server 2008 up to current version), Windows Azure SQL Database (initial version up to current version).

Builds an instance of GEOGRAPHY which represents an instance of Point of its latitude and longitude values and a SRID (spatial reference ID).