What is the difference between geography and geometry spatial data types

     It is not easy to understand the difference between the SQL Spatial data types geography and geometry when you try to learn about them first time. There are a lot of resources about them on Internet and most of them are old articles from the SQL Server 2008. Many of these articles including my older post use bunch of maps to explain the difference between geography and geometry.

     I am going to try to explain the difference between them without using any maps here. First thing, you need to know is geometry data type is always in 2D just like a map on a piece of paper. When you create a point in geometry, you use x coordination and y coordination. In the following example, I created a point in Ohio also I created a point for Nepal. What is 4326? That's the spatial reference id and it helps to spatial functions. You can ignore that number for now specially for geometry data type. It's almost useless when it comes to geometry data type.

declare @OhioGeom as geometry = geometry::Point(41.3,-81.6, 4326)
declare @NepalGeom as geometry = geometry::Point(30,40, 4326)

     Now, what you need to know is, These points are not real latitudes and longitudes. They are just two points in coordinate system. Here is the proof, Let's find how far is Nepal from Ohio. Following spatial function finds the distance between two spatial objects. I have divided it 1609.344 to display the result in miles. Original result is in meters because I used 4326 for spatial reference id when i created the points, and 4326 returns meter.

select @OhioGeom.STDistance(@NepalGeom)/1609.344 as Diff

     So the distance between Ohio and Nepal is not even a mile if you use geometry. You can create map of Ohio with geometry and it will look right but if you need to do calculations then your calculations will not return right results since geometry spatial data does not represents the real latitude and longitude.

     Now,  I am going to use the same points and I am going to create them as geography data type rather than geometry.

declare @OhioGeog as geography= geography::Point(41.3,-81.6, 4326)
declare @NepalGeog as geography= geography::Point(30,40, 4326)

    First thing you need to know is, In this example since we used geography spatial data types Ohio's and Nepal's points are real latitudes and longitudes When I am going to try to find the shortest line between Ohio and Nepal, the line between them is not going to be straight since earth is not flat. The line is going to have a curve. Let's see what is the distance between Ohio and Nepal by using the same function.

select @OhioGeog.STDistance(@NepalGeog)/1609.344 as Diff

   It looks like the shortest distance between Ohio and Nepal is 7556 miles which makes sense. I hope this post is helpful to explain the difference between geography and geometry spatial data types in SQL Server.