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 http://blog.sqlauthority.com which is next day GTM+2.5.

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

## 95 comments. Leave new

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;

You Are a Hero! Thanks for the awesome tutorial

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

The correct answer:2(Meters)

Country:Bangladesh

Thank you soo much!!!!!

this is perfect

STDistance returns distance between two points in meter.

Country India

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

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

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

Winner from USA:

Fatah KhasseWinner from India:

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

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!

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.

Hi Pinal,

It’s in The Meteres. 2nd option is correct.

Thanks.

Ashish Kadam – Pune – India

Hello Pinal,

Your explanation is superb.

Answer: 2

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

Country: India

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

Answer is:

2.Meters

Vinay,

Pune,India

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.

Correction Answer is Meters (option 2)

Thanks,

Bhasker, USA

Correct answer is: 2

Thanks,

Basavaraj

India

Answer : option 2

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

Country : USA

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

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

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

Correct Answer is Option 2 – Meters

Regards

Rajesh

From india

Correct answer is 2 meters.

I’m from INDIA

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

The correct answer is option 2 that is

Meters

Divesh

INDIA

2. Meters

Chetan – USA

Correct answer is option #2.

Meters

Regards

Rajesh

From india

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

The correct answer is option 2.

David

USA

Answere 18 : Meters

Jatin from India

2.Meters

Gordon Kane

Allen TX

USA

Correct Answer:2 Meter

Krishan Kumar mishra

India

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

By definition, the STDistance function returns a result in meters, option 2.

Country: United States

Correct answer is #2

USA

Mike Michalicek

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)

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

Option:2 : Meters is the correct answer

Country- India

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

Correct answer is 2: meters

STDistance( ) method calucates in meters as mentioned in the article.

Country – India

option. 2

Ghanshyam

Bangalore

The STDistance function of the Geography Data type calculates the distance between two points in Meters. Option 2 is the correct answers.

(Sale, Nigeria)

Correct OPtion is 2.

2. Meters

Karan,

India

Ans:2

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

Partha

India

Correct Answer Option No. – 2) Meters

Deepali Bhende.

Country : INDIA

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”

Correct answer option 2

‘Meters’

Somnath Desai

India

Answer: 2 [Based on your explanation ]

Sreeram

Indian

Correct answer is #2

Country : India

Option 2 : Meters is the correct answers.

Country-India

Answer: 2. Meters

Sudeepta,

India.

answer 2 is correct : Meters

India

answer 2 is correct: Meters

Correct option is 2.

Shilpa

India

Correct answer

2.Meters

Country : India

Answer is : Option (2) Meters

— GVPrabu || Bangalore || india

Correct Answer is Option 2.

2.Meters

Country:India

Thanks,

Fazal Vahora

Correct Answer is Option 2 (Meters)….

Pankaj Patil

INDIA

ans is 2. meters

Correct Answer Option No. – 2) Meters

shekhar gurav.

country : INDIA

Correct option is

2. Meter

City: BRD

Country: INDIA

Thanks.

GurjitSingh

Correct Answer option #2

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

Cochin,INDIA

The Correct Answer is Option – #2

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

Thanks,

Narendra(India)

option #2 is right

using SRID 4326, which means that STDistance() returns distances in meters.

Correct Answer is Meters

Ans- 2 Meters

Abhishek Mishra

INDIA NOIDA

The correct answer is option 2 that is

Meters

Mahmad Khoja

INDIA

AHMEDABAD

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)

Hi

then correct option is

2) Meters

I am from India

option 2 ie meters is correct

Tej narayan maurya

new delhi(india)

Correct answer is option 2 – meters

Thanks,

Prasad Yangamuni

INDIA (PUNE)

Correct answer is # 2.

Gopalakrishnan Arthanarisamy

Unisys, Bangalore, India

answer is second – Meters

India

The correct answer is 2 – meters. Thanks for very helpful lesson!

I am from USA

Correct answer is option #2.

Meters

Thanks,

Dhruval Shah – India

Question 18

Ans : Meters

Chennai, INDIA

Option 2 is the Right answer

Manoj Sahoo

India

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

Answer is:

3. Kilometers

India,Banglore

Answer is

2. Meters

oops ..my bad it doen’t say geometry

Quick Question… u mentioned geography and Geometry . where is the geometry part??

Meters

ADITI S

BOSTON, USA

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

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

Rene Castro

El Salvador

2. Meters

United States

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

Hi,

It returns distance between 2 points in Meters.

Option 2 is correct.

Thanks

Sudhir Chawla

New Delhi, India

Correct answer is #2 meters.

Uday Bhoopalam

USA

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

Correct option is #2.

2.Meters

New Delhi

India

Correct answer is option #3.

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

Thanks.

Country – India

2) is the answer as defined in this blog

2) Meters

Leo Pius

USA