Haversine formula for SQL SERVER

Question

Haversine formula for SQL SERVER

Navigation

#1 by (1 votes)

1

Well, I have an Android app that uses Google Maps API. On the map, I get data from a SQL Server database. I need to create a radius in KM in which I just load the data from the bank that are within this radius. This is based on the current geolocation of the user's device.

I used MySQL, in it I could do this 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 could I use this same formula for SQL Server?

In SQL SERVER I encounter the following error:

com.microsoft.sqlserver.jdbc.SQLServerException: Column Name 'distance' is invalid.

java android sql-server web-service google-maps

asked by anonymous 08.09.2017 / 03:14

1 answer

Disable and keep hover in click Calculator in JavaScript

score 1 · Accepted Answer

For SQL Server 2008 or higher there is GEOGRAPHY type that "translates" latitude and longitude into a location. Once you have done this you can calculate it before using it for your 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 to current version), Windows Azure SQL Database (initial release to current version).

Constructs an instance of GEOGRAPHY that represents an instance of Point of its latitude and longitude values and an SRID (spatial reference ID).