If you’re like me, you’ve probably stored latitude and longitude data in decimal columns for a long time. I know SQL Server has a geography type as of a few years ago, but I never really played with it much. Anyway, I finally tried it, and so far it’s great – and no individual decimal columns – and I can do calculations right in SQL.

To get started, use the geography type in a table like this?:

CREATE TABLE [dbo].[MattressStoreLocations](	
[Mattress Store Name] [nvarchar](50) NULL,	
[Location] [geography] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Now let’s add some data. When adding a new record, we’re going to use the Geography Point since it’s for latitude & longitude. It’s take a format like this: geography::Point(<lat>,<long>, SRID), so like this:

insert into [MattressStoreLocations] 
 ([Mattress Store Name],[Location]) values 
 ('Discount Mattress Corner Store',
geography::Point(27.244048, -80.829829, 4326));

You can see the latitude (27…) and longitude (-80…). The SRID is the Spatial Reference Identifier. If you’re using latitude and longitude from or along with something like Google Maps, 4326 is the format that’s common. (3857 might be another depending on your case, but probably not).

Now that we have some data added, if you simply just do a:
select * from [MattressStoreLocations] you’ll see the below:

So let’s expand it to pull the fields:

select [Mattress Store Name], 
  [Location].Lat as Lat,  [Location].Long as Lon
   from [MattressStoreLocations] 

And now we get this:

The really useful thing:

Use methods on our location field to perform calculations, like how far it is from another lat & long. (Remember coding these calcs yourself? Thinking about the radius of earth, etc.)

STDistance – Check this out:

SELECT        [Mattress Store Name], 
   [Location].STDistance(geography::Point(34.073444, -118.402882, 4326)) / 1000 AS DistInKM
FROM            [MattressStoreLocations]

This determined how far one point is from another. Since SRID 4326 has meter as a unit of measure, the STDistance will return meters. In my query above, I divide by 1000 to get kilometers. (you could do other calcs to get miles, etc.) Checkout all of the other methods you can do here.

Anyway, hope it helps you if you’re not already using the Geography type. If you’re creating a store locator by radius, or maybe trying to use to data to figure our why so many mattress stores exist, this is a perfect use.