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.
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..
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 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.
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.
Question 18
The STDistance function of the Geography Data type calculates the distance between two points in …
- Feet
- Meters
- Kilometers
- Miles
- 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)
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!
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.
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
you repeated yourself more than a lot, made the article bad.
STDistance returns distance between two points in meter.
Country India
Thank you soo much!!!!!
this is perfect
The correct answer:2(Meters)
Country:Bangladesh
this is very awesome :) thanx y’ve solved many probs 4 me :)
You Are a Hero! Thanks for the awesome tutorial
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;
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
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