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
Good luck to everyone in business and life.