Using the Geography Data Type in SQL Server

Hello everyone. In this article, I will try to give information about using the Geography data type in SQL Server.

In SQL Server, in some cases, you may have to use the Geography data type.

The data type that allows us to keep the coordinates in SQL Server is the Geography data type.

Besides this service, SQL Server also offers methods that allow us to perform some operations on these coordinates. You can see an example usage below.

DECLARE @geo_istanbul geography
SET @geo_istanbul = geography::Point(41.010467,28.939179 , 4326)
SELECT @geo_istanbul

Point(Latitude, Longitude, SRID) gives us the coordinates of a point on the earth. The variables inside the point are latitude, longitude and SRID, respectively.

SRID is a reference, it tells us whether the system is planar or like the shape of the earth. The 4326 we use is the one used in normal GPS systems. We will always use it.

You may think that we can’t write the “Point(41.010467,28.939179 , 4326)” part, which you see directly above, on the table, but unfortunately you need to make a definition as above. The reason for this is that this system stores data of the Geography type as binary in the database.

In the example below, you can see how to add a Geography data type to a table.

IF OBJECT_ID('tempdb..#GeoTest') IS NOT NULL
    DROP TABLE #GeoTest
CREATE TABLE #GeoTest ([CoordinateLocation] [geography] NULL);
INSERT INTO #GeoTest (CoordinateLocation)
SELECT GEOGRAPHY::STPointFromText('POINT(41.010467 28.939179)', 4326);
SELECT *
FROM #GeoTest;

The method we can use the most is the STDistance() method. This method calculates the distance between two points on the earth. You can see an example on this subject below.

DECLARE @istanbul geography, @izmir geography
SET @istanbul = GEOGRAPHY::STPointFromText('POINT(41.00527 28.97696)', 4326)
SET @izmir = GEOGRAPHY::STPointFromText('POINT(38.41885  27.12872)', 4326)
SELECT @istanbul.STDistance (@izmir)/1000

Those who wish

They can check it from https://www.meridianoutpost.com/resources/etools/calculators/calculator-latitude-longitude-distance.php

Good luck to everyone in business and life.