Using SQL Server for Latitude & Longitude Calculations

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:

Not very helpful right?

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:

Sweet!

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]
California to Florida – 3600 Kilometers

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.

Using SQL Server for Latitude & Longitude Calculations

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s