Published on

August 4, 2011

Understanding Geography Data Type in SQL Server

In this blog post, we will explore the Geography data type in SQL Server. This data type was introduced in SQL Server 2008 and allows us to store and manipulate geographical information such as areas and points on the earth.

Prior to SQL Server 2008, storing and working with geographical data required custom applications outside of SQL Server. For example, if we wanted to calculate the distance between two locations, we would have to extract the data into a separate application and perform the calculations there.

However, with the introduction of the Geography data type, we can now store latitude and longitude values in a single field and perform calculations directly within SQL Server. This eliminates the need for separate custom applications and simplifies the process of working with geographical data.

Let’s take a look at an example. Suppose we have a table called “Location” with fields for city, state, street, latitude, and longitude. In order to combine the latitude and longitude values into a Geography field, we can use the following code:

ALTER TABLE Location
ADD GeoLoc GEOGRAPHY NULL
GO

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

In the above code, we first add a new field called “GeoLoc” of type Geography to the “Location” table. We then use the “GEOGRAPHY::Point” function to generate the geospatial locations based on the latitude and longitude values.

Once we have the Geography field populated, we can perform various calculations and operations on it. For example, we can calculate the distance between two points on the earth using the “STDistance” method:

DECLARE @Seattle GEOGRAPHY
DECLARE @Boston GEOGRAPHY

SELECT @Seattle = GeoLoc
FROM Location
WHERE City = 'Seattle'

SELECT @Boston = GeoLoc
FROM Location
WHERE City = 'Boston'

SELECT @Seattle.STDistance(@Boston) / 1000 AS DistanceInKM

In the above code, we first declare two variables, @Seattle and @Boston, and assign them the respective GeoLoc values for the cities of Seattle and Boston. We then use the “STDistance” method to calculate the distance between the two points and divide it by 1000 to get the distance in kilometers.

By using the Geography data type and its built-in functions, we can easily work with geographical data within SQL Server without the need for external applications. This simplifies the development process and allows for more efficient and accurate calculations.

Don’t forget to participate in our quiz by answering the following question:

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

Please leave your answer in the comment section below along with the correct option, explanation, and your country of residence. Every day, one winner from the United States and one winner from India will be announced. The winners will receive a free subscription to Joes 2 Pros Volume 3.

Thank you for reading this blog post. Stay tuned for the next one!

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.