SQL SERVER – Geography Data Type – Calculating Distance Between Two Points on the Earth – Day 18 of 35

In this blog post we will learn about Geography Data Type.

Answer simple quiz at the end of the blog post and –
Every day one winner from the United States will get Joes 2 Pros Volume 3.

Geography Data Type

New to SQL Server 2008 are the spatial data types, called Geography and Geometry. The Geography data type can store information for areas and points on the earth. It also provides a built-in function to calculate distance and overlaps with other locations. This data type stores and handles calculations based on round-earth (or ellipsoidal) data, which relates to coordinate systems such as GPS and longitude-latitude.

Databases stored positional data for years, like the sample data below where we see two dedicated fields to store longitude and latitude for each JProCo location. This is our first goal, we will do very soon after some more explanation.

SQL SERVER - Geography Data Type - Calculating Distance Between Two Points on the Earth - Day 18 of 35 J2P_18_1

If you were to ask this table in a query what the longitude-latitude difference between Seattle and Boston is, it would have no idea. To the query, these are just arbitrary flat data values in a table. In order to turn each pair of numbers into a meaningful geographical point, we would have to extract this data into a custom application outside of SQL Server (e.g., into a C# application) and then run calculations using the customized app.

In other words, the true ellipsoidal nature of this geographic data wasn’t stored in the database prior to SQL Server 2008. The database couldn’t have differentiated our sample longitude-latitude data above from any other kind of information in the database. Now, thanks to SQL Server 2008, you no longer need a separate custom application, because both of these pieces of data can be stored in one Geography field. As well, the Geography type makes available all the built-in functionality, to perform calculations involving round-earth data, which would have been contained in a custom app.

Storing Latitude and Longitude

Prior to SQL Server 2008, float or decimal fields would be used to house latitude and longitude. Now you can store these, as well as other geospatial data, in one Geography field.

Let’s begin by looking at all the records in our Location table, as well as its design. We see typical location data – city, state, street – and the five JProCo office locations (Seattle, Spokane, Chicago, Boston, and Philadelphia). In this table, we will add fields for latitude and longitude, and then we’ll see how to combine those into one Geography field..

SQL SERVER - Geography Data Type - Calculating Distance Between Two Points on the Earth - Day 18 of 35 J2P_18_2

SQL SERVER - Geography Data Type - Calculating Distance Between Two Points on the Earth - Day 18 of 35 J2P_18_3

Let’s start by adding latitude and longitude fields and populate these fields using the values based on the code below:

ALTER TABLE Location
ADD Latitude FLOAT NULL
GO
ALTER TABLE Location
ADD Longitude FLOAT NULL
GO
UPDATE Location
SET Latitude = 47.455, Longitude = -122.231
WHERE LocationID = 1;
UPDATE Location
SET Latitude = 42.372, Longitude = -71.0298
WHERE LocationID = 2;
UPDATE Location
SET Latitude = 41.953, Longitude = -87.643
WHERE LocationID = 3;
UPDATE Location
SET Latitude = 47.668, Longitude = -117.529
WHERE LocationID = 4;
UPDATE Location
SET Latitude = 39.888, Longitude = -75.251
WHERE LocationID = 5;

Creating Geography as a Field in a Table

We’re now going to add another field called GeoLoc (short for geographical location), which will use the new Geography data type. We have the latitude and longitude fields populated with values for each JProCo location. The Geography field GeoLoc has also been added to the table, but is not yet populated.

SQL SERVER - Geography Data Type - Calculating Distance Between Two Points on the Earth - Day 18 of 35 J2P_18_4

SQL SERVER - Geography Data Type - Calculating Distance Between Two Points on the Earth - Day 18 of 35 J2P_18_5

Populating a Geography Data Type

Based on the two data points, latitude and longitude, we can generate the geospatial locations for the GeoLoc field. Use the Point-static function called GEOGRAPHY to pass in the latitude and longitude values along with a style specifier value (4326 is the standard which is used the most).

UPDATE Location
SET GetLoc = GEOGRAPHY::Point(Latitude , Longitude , 4326)

SQL SERVER - Geography Data Type - Calculating Distance Between Two Points on the Earth - Day 18 of 35 J2P_18_6

The style specifier we used to format our Geography value is also known as a spatial reference identifier or SRID and identifies which spatial reference system the coordinates belong to. The SRID 4326 represents WGS 84, which is the most commonly used systems and is used by many GPS systems.

Calculating Distance between two points on the earth

We have successfully combined latitude and longitude into the GeoLoc field. However, since the values in the GeoLoc column are a little cryptic to read by the human, we can create a variable and capture each city’s GeoLoc value into its respective variable.

SQL SERVER - Geography Data Type - Calculating Distance Between Two Points on the Earth - Day 18 of 35 J2P_18_7

STAsText( ) is one of the Spatial Type methods you can use with the Geography type. The STDistance( ) method calculates the shortest distance (in meters) between two Geography data points. To have STDistance( ) return the distance from Seattle to Boston in kilometers (KM), we have divided the returned value by 1000). Without this step, the result is just over 4 million meters (4,014,163 meters).  Calculating the distance from Seattle to Boston, which is just over 4,014 KM.

SQL SERVER - Geography Data Type - Calculating Distance Between Two Points on the Earth - Day 18 of 35 J2P_18_8

Question 18

The STDistance function of the Geography Data type calculates the distance between two points in …

  1. Feet
  2. Meters
  3. Kilometers
  4. Miles
  5. Units

Do not forget to participate in special question over here: Pluralsight Giving Away Free Subscription to Quiz Participants

Rules:

Please leave your answer in the comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from the United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answering.
Please check my facebook page for winners name and correct answer.
Winner from United States will get Joes 2 Pros Volume 3.
The contest is open till next blog post shows up at which is next day GTM+2.5.

Reference:  Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Sparse Data and Space Used by Sparse Data – Day 17 of 35
Next Post
SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – The Clustered Index – Simple Understanding – Day 19 of 35

Related Posts

93 Comments. Leave new

  • the answer is 2: Meters.
    Pinal told us the answer in the select statement alias
    SELECT @Seattle.STDistance(@Boston) AS [Distance in Meters]

    I’m going to have to play with spacial data in sql server. seems cool!

    Reply
  • Q 20) SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Introduction to Page Split – Day 20 of 35

    A.) 2 Meters

    Winner from USA: Fatah Khasse

    Winner from India: Sudhir Chawla

    I thank you all for participating here. The permanent record of this update is posted on facebook page.

    Reply
  • The correct option is option #2

    Meters

    The STDistance( ) method calculates the shortest distance (in meters) between two Geography data points.

    Country of Residence: USA

    Reply
  • you repeated yourself more than a lot, made the article bad.

    Reply
  • STDistance returns distance between two points in meter.
    Country India

    Reply
  • Thank you soo much!!!!!
    this is perfect

    Reply
  • Istiaque Chowdhury
    February 9, 2013 7:11 pm

    The correct answer:2(Meters)
    Country:Bangladesh

    Reply
  • this is very awesome :) thanx y’ve solved many probs 4 me :)

    Reply
  • You Are a Hero! Thanks for the awesome tutorial

    Reply
  • vishal mehta
    May 22, 2014 8:55 pm

    Hello , i am finding distance between 2 cities but i get little bit difference in output while i use mathematical formula instead of geography distance function . the reason behind to use mathematical formula because geography distance function takes time to execute methods. so can i get actual output as same as Geography does. below are the sample which shows difference .

    DECLARE @lat1 FLOAT, @lat2 FLOAT, @lon1 FLOAT, @lon2 FLOAT,@radius FLOAT

    SET @lat1 = 30.4382553
    SET @lon1 = -84.2807312
    SET @lat2 = 26.6930000
    SET @lon2 = -81.9910000
    SET @radius = 3959 — 3959 for miles, 6371 for KM

    SELECT ACOS(SIN(PI()*@lat1/180.0)*SIN(PI()*@lat2/180.0)+COS(PI()*@lat1/180.0) *COS(PI()*@lat2/180.0)*COS(PI()*@lon2/180.0-PI()*@lon1/180.0))* @radius

    DECLARE @g geography;
    DECLARE @h geography;
    SET @g = geography::STGeomFromText(‘LINESTRING(-84.2807312 30.4382553, -84.2807312 30.4382553)’, 4326);
    SET @g = geography::STGeomFromText(‘POINT(-81.9910000 26.6930000)’, 4326);
    SET @h = geography::STGeomFromText(‘POINT(-84.2807312 30.4382553)’, 4326);
    SELECT @g.STDistance(@h)/ 1609.34;

    Reply
  • THIS IS AWSOME. I looked at about 30 other SQL formula’s to perform this task. Nothing worked. I was using the internet to check the distances. Nothing even came close.

    This process is dead on accurate. THANK YOU SO MUCH

    T.MARK FINK

    Reply
  • Dear

    how can we calculate real road distance using sql server. this example cover only air distance.

    for example, 16 KM is distance between my office and home. but this example is showing around 10.

    please need your support

    Reply

Leave a Reply

Menu