by

SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Geography Data Type – Calculating Distance Between Two Points on the Earth – Day 18 of 35

Answer simple quiz at the end of the blog post and -
Every day one winner from India will get Joes 2 Pros Volume 3.
Every day one winner from 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 have been storing positional data for years, like the sample data below where we see two dedicated fields to store longitude and latitude for each JProCo location. Please note that your if you ran the SQLArchChapter5.0Setup.sql script  then the Location table in the figure below does not currently have the latitude and longitude fields. This is our first goal we will do very soon after some more explanation.

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 geographical 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, two 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). Into this table we will add fields for latitude and longitude, and then we’ll see how to combine those into one Geography field..

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.

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)

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 system 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.

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.

Note: If you want to setup the sample JProCo database on your system you can watch this video. For this post you will want to run the SQLArchChapter5.0Setup.sql script from Joes 2 Pros Volume 3.

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 comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.
A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Winner from United States will get Joes 2 Pros Volume 3.
Winner from India will get Joes 2 Pros Volume 3.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

Reference:  Pinal Dave (http://blog.SQLAuthority.com)

About these ads

92 thoughts on “SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – Geography Data Type – Calculating Distance Between Two Points on the Earth – Day 18 of 35”

  2. Correct answer is option #3.

    As stated clearly in the article above, STDistance( ) returns the distance in kilometers (KM)

    Thanks.

    Country – India

    Reply

  4. The STDistance function of the Geography Data type calculates the distance between two points in Kilometers.Clearly, the best way to take advantage of the Geography Data Type is to use STDistance, as it returns meaningful set of numbers that can be understood by anyone..
    Coon Rapids, MN
    USA

    Reply

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

    Feet
    Meters
    Kilometers
    Miles
    Units

    Solution:
    As per the definition and example the STDistance() method calculates the distance between two points in meters.

    The answer is 2nd option.

    Nagaraj Ejanthkar
    USA

    Reply

  9. Correct answer is No. 2.
    The STDistance( ) method calculates the shortest distance (in meters) between two Geography data points.
    Rene Castro
    El Salvador

    Reply

  10. option 2 is the right answer

    The STDistance function of the Geography Data type calculates the distance between two points in “Meters”

    The return measurement depends upon the Spatial Reference Identifiers (SRIDs) of our geography data type. The default is 4326 which is in meters. There’ a table in the DB we can check-

    Select * from sys.spatial_reference_systems

    Regards
    Santosh.S
    Bangalore, India

    Reply

  14. Pingback: SQL SERVER – Win a Book a Day – Contest Rules – Day 0 of 35 Journey to SQLAuthority

  25. As in description “The STDistance( ) method calculates the shortest distance (in meters) between two Geography data points.” Its very clear it stores in Meters.
    #Answer 2) Meters.

    Thanks…
    Rajneesh Verma
    (INDIA)

    Reply

  29. The Correct Answer is Option – #2

    The STDistance() function used to calculate distance in Meters.

    Thanks,
    Narendra(India)

    Reply

  30. Correct Answer option #2

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

    Cochin,INDIA

    Reply

  36. Answer is : Option (2) Meters

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

    – GVPrabu || Bangalore || india

    Reply

  44. Answer: 2 [Based on your explanation ]

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

    Sreeram
    Indian

    Reply

  46. the correct answers is option 2 ie..

    2. Meters

    because as you mentioned that “The STDistance( ) method calculates the shortest distance (in meters) between two Geography data points”

    Reply

  48. Ans:2

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

    Partha
    India

    Reply

  53. Hi Sir,

    The correct answer is 2. Meters.

    By Default the STDistance function of the Geography Data type calculates the distance between two points in Meters.

    We can calculate the other measurements by dividing the returned values accordingly.

    as explained in the examples to calculate values in Kilo Meters we are dividing the value by 1000.

    P.Anish Shenoy,
    INDIA,Bangalore,Karnataka

    Reply

  55. The Correct Answer is :
    2.Meters
    Explanation:
    The STDistance( ) method calculates the shortest distance (in meters) between two Geography data points. The returned value will be in Meters. We can convert it to KMs by dividing by 1000.

    Country :
    India

    Reply

  56. Correct Answer is Option 2 – Meters

    Explanation: It is there in your blog that ‘The STDistance( ) method calculates the shortest distance (in meters) between two Geography data points.’

    Country – INDIA (Gujarat)

    Reply

  59. Answer is #2

    2. Meters

    The article had mentioned that the STDistance( ) method calculates the shortest distance (in meters) and if we want some other unit of measure we’d have to do some math on the data.

    USA

    Reply

  63. The STDistance function of the Geography Data type calculates the distance between 2 points in meters.

    The correct answer is option 2.

    David
    USA

    Reply

  67. The correct option is #2.

    Answer

    The STDistance function of the Geography Data type calculates the distance between two points in Meters (by default).

    Explanation

    Returns the shortest distance between a point in a geography instance and a point in another geography instance.

    Syntax:
    .STDistance ( other_geography )

    Here other_geography is the instance from which to measure the distance between the instance on which STDistance() is invoked.

    If other_geography is an empty set, STDistance() returns null.

    Diljeet Kumari
    Country : INDIA

    Reply

  70. Hi
    the answer to this question appear in follow lines in this page
    “The STDistance( ) method calculates the shortest distance (in meters) between two Geography data points”

    the correct option is 2 – “meters”

    Leonardo
    from: Chile

    Reply

  71. Hi Pinal,

    Challenge:
    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

    Correct Answer:
    The correct answer is choice #2: Meters

    Explanation:
    The STDistance( ) function will calculate the distance (shortest distance) between two Geography data points. The resultant distance will be returned in meters.

    Country:
    United States

    Thanks for the knowledge!

    Regards,

    Bill Pepping

    Reply

  72. Hi Pinal Sir,

    The Correct Answer for the above question is Option 2 ) Meters

    Explanation:

    As Explained by you “The STDistance( ) method calculates the shortest distance (in meters) between two Geography data points”

    Hence by default it calculates the distance between two geographical points in meters.

    DILIP KUMAR JENA
    Country : INDIA

    Reply

  73. Answer : option 2

    As per the definition and example the STDistance() method calculates the distance between two points in “meters”.

    Country : USA

    Reply

  76. The STDistance function of the Geography Data type calculates the distance between two points in …
    The correct answer is #2. Meters.

    Everyone but America basically use them…hmm.

    You have to then take those silly metters and calculate the number of inches it is from Seattle to Bangalore.

    Reply

  78. STDistance Function calcultes distance in Meters only.

    As stated in blog, if we need difference in Kilometers divided the returned value by 1000.

    so, correct answer is option : 2. Meters

    Chennai, Tamilnadu, India

    Reply

  79. Hello Pinal,
    Your explanation is superb.

    Answer: 2

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

    Country: India

    Reply

  81. Hi pinal,

    while i’m trying to work out the geography datatype with workspace i got an error.
    can you pls explain this.I’m using sql server 2008.

    Msg 6522, Level 16, State 1, Line 1
    A .NET Framework error occurred during execution of user-defined routine or aggregate “geography”:
    System.FormatException: 24201: Latitude values must be between -90 and 90 degrees.
    System.FormatException:
    at Microsoft.SqlServer.Types.SqlGeography.Point(Double latitude, Double longitude, Int32 srid)
    .
    The statement has been terminated.

    with regards,
    ShaliniMeyyappan
    India.

    Reply

  82. 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

  85. Pingback: SQL SERVER – SSQL Architecture Basics – Core Architecture Concepts – Book Available for SQL Server Certification Journey to SQLAuthority

  86. Pingback: SQL SERVER – Clustered Index and Primary Key – Contest Win Joes 2 Pros Combo (USD 198) – Day 3 of 5 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out / Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out / Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out / Change )

Cancel

Connecting to %s