SQL Calculate Distance between Two Points

In this post, I am sharing how to calculate distance between two GEO locations given geographical points (latitude and longitude) of the locations.

Below is a function that takes latitude and longitude of the locations and returns the distance between the locations in miles

Parameters:

• lat1: Latitude of Source Location
• long1: Longitude of Destination Locations
• lat2: Latitude of Source Location
• long2: Longitude of Destination Locations

CREATE FUNCTION dbo.udf_GetDistance(@lat1 float, @long1 float, @lat2 float, @long2 float)
RETURNS FLOAT
AS
BEGIN   
    DECLARE @DToR as float
    DECLARE @Ans as float
    DECLARE @Miles as float

    SET @DToR= 57.29577951
    SET @Ans = 0
    SET @Miles = 0

    IF @lat1 IS NULL OR @lat1 = 0 OR @long1 IS NULL OR @long1 = 0 OR @lat2 IS NULL OR @lat2 = 0 OR @long2 IS NULL OR @long2 = 0
    BEGIN
        RETURN ( @Miles )
    END
    SET @Ans = SIN(@lat1 / @DToR) * SIN(@lat2 / @DToR) + COS(@lat1 / @DToR) * COS( @lat2 / @DToR) * COS(ABS(@long2 - @long1 )/@DToR)
    SET @Miles = 3959 * ATAN(SQRT(1 - SQUARE(@Ans)) / @Ans)   
    RETURN ( @Miles )
END

To calculate distance we need geographical points of the locations that we can get from google API's.

For intro refer, https://developers.google.com/maps/documentation/geocoding/intro

Using below link, we would be taking Geo points of North-East and South-West of Delhi and will pass to the function to get the distance between two.

http://maps.googleapis.com/maps/api/geocode/json?address=delhi&sensor=false

Let's pass the latitude and longitude of both that we get using above link to the function and see the result.

SELECT dbo.udf_GetDistance(28.88225340, 77.34169940, 28.42219750, 76.85382840) as Miles

RESULT

Sequence in SQL Server 2012

Microsoft introduced Sequence object in SQL Server 2012 release.

A Sequence is a user defined, schema bound object that will generate a sequence of numeric values (in ascending or descending order) according to specification. Unlike identity columns, a sequence is created independent of any table. A few interesting differences between the two are;

• A Sequence object is independent of any table, whereas the Identity column property is table specific
• Unlike Identity, you can generate a new sequence value before using it in an insert Statement
• You can define both the minimum & maximum values, as well as cycling & cache size options for a sequence
• Unlike Identity, a sequence object will increment its value only when it is explicitly called

Let's create a sequence object and see how it works.

CREATE SEQUENCE dbo.emp_sequence AS INT
START WITH 1
INCREMENT BY 1

We have created a sequence with name emp_sequence where the starting value will be 1 and next value will be incremented by 1.

Now let's fetch first value for the sequence.

SELECT NEXT VALUE FOR dbo.emp_sequence as first_emp

RESULT

Check the output where first value is fetched as 1.

Now let's fetch next value for the sequence

SELECT NEXT VALUE FOR dbo.emp_sequence as next_emp

RESULT

The next value is fetched as 2 which is incremented by 1 from the previous value.

Now let's see how a sequence object can be used with table and how can act as alternative to identity.

For this, we are going to use the same sequence we created in the earlier example, so lets first reset the initial value of sequence

ALTER SEQUENCE dbo.emp_sequence
RESTART WITH 1
INCREMENT BY 1;

Now lets create a temp table employee where we will insert the data in emp_no column using sequence

CREATE TABLE #employee (emp_no int, emp_name VARCHAR(10));
INSERT INTO #employee VALUES
  (NEXT VALUE FOR dbo.emp_sequence, 'Ajay')
, (NEXT VALUE FOR dbo.emp_sequence, 'Vijay')
, (NEXT VALUE FOR dbo.emp_sequence, 'Sanjay')
SELECT * FROM #employee
DROP TABLE #employee

RESULT

Check the output where emp_no is generated sequentially as 1,2,3 using sequence.

In the script we have also dropped the temp table, but the current value of sequence has been incremented to 3.

Let's run the same script again

CREATE TABLE #employee (emp_no int, emp_name VARCHAR(10));
INSERT INTO #employee VALUES
  (NEXT VALUE FOR dbo.emp_sequence, 'Ajay')
, (NEXT VALUE FOR dbo.emp_sequence, 'Vijay')
, (NEXT VALUE FOR dbo.emp_sequence, 'Sanjay')
SELECT * FROM #employee
DROP TABLE #employee

RESULT

Check the output where emp_no is generated as 4,5,6 using the same script. This is because the value of sequence was 3.