Working With GEOSPATIAL Data In Microsoft SQL Server TSQL
As anyone knows who has ever attempted to do calculations involving GeoSpatial data with Latitude and Longitude coordinates, it can be a very cumbersome and very difficult task. But like always, Microsoft has come to the rescue. Microsoft introduced in SQL Server 2008 databases, and later versions, including Microsoft Azure SQL Server, a new column data type called GEOGRAPHY. This new type makes doing calculations with Latitude and Longitude coordinates exceedingly simple and easy to do.
In this post, I will show an example Microsoft SQL Server TSQL script for working with GeoSpatial data, with locations in the Cocoa Beach, Florida area where I live on The Florida Space Coast, and also locations in the Central Florida area, and calculating distances in miles and kilometers using the Latitude and Longitude coordinates of the Florida locations.
In a previous job I held as a SQL Developer, I was responsible for developing Microsoft SQL Server Reporting Services (SSRS) reports using the SQL Server GEOGRAPHY column type, and doing distance calculations with Latitude and Longitude values. My employer at that time was a contractor with the USA Department of Homeland Security (DHS), and the reports I worked on would list the address of drug testing facilities near a DHS job applicant's home address Latitude and Longitude, so the job applicant could complete their pre-employment drug screen.
I am also planning to develop a web application and mobile app for Android and iOS and Windows 10 Mobile (Xamarin) to find the distances to Food Pantries using the GPS coordinates from a user's mobile device, the app will be called FOOD PANTRY. My previous experience with SQL Server and mobile apps provides a good foundation for me working with geospatial data, and I am using that experience also in the creation of our example TSQL Script in this post.
In the first part of our TSQL script, we will create a table with Latitude and Longitude columns, the name of the Location, and a column of type GEOGRAPHY called GeoPoint. Also we will have an auto-generated GUID primary key ID for this table.
-- geography type testing CREATE TABLE [dbo].[GeoTest] ( [ID] uniqueidentifier NOT NULL, [Location] nvarchar(100) NULL, [Latitude] nvarchar(50) NULL, [Longitude] nvarchar(50) NULL, [GeoPoint] geography NULL CONSTRAINT [PK_GeoTest] PRIMARY KEY ([ID]) ) GO -- have the GUID primary key auto generated with each new record ALTER TABLE [dbo].[GeoTest] ADD CONSTRAINT [DF_geotest_ID] DEFAULT (newid()) FOR [ID] GO
Also an important note, if you are doing TSQL development and will ever be using foreign non-Latin character sets in a string column, you must make it an NVARCHAR type, a VARCHAR type column will not work with foreign character sets.
The geography type in this example uses string (nvarchar) variables for Latitude and Longitude, there is also a way to do it with Degrees Minutes Seconds for Latitude and Longitude, like HERE, but that is definitely far more complex to do than just using decimal coordinates in a string.
Even though the Latitude and Longitude coordinates are decimal numbers, we are using a string (nvarchar) for them here since the geography functions take the Latitude and Longitude as string parameters, and also to lessen issues with data conversions in Entity Framework.
Fortunately, Google Maps is very handy for geospatial data, since they provide the Latitude and Longitude of locations as decimal string values. I used Google Maps to find the coordinates of my Cocoa Beach, Florida locations being used in this example TSQL Script.
After we created our table, we then have to figure out the GeoPoint code for the particular Latitude and Longitude we are working with, this is one long string of characters that includes numbers and characters. TSQL provides a function to calculate the GeoPoint called "STGeomFromText", in the next part of this TSQL script we will use this function to get the geopoints for Cocoa Beach, Florida locations like Ron Jon Surf Shop, Coconuts Bar and Grill, and Starbucks. Note that the function takes the LONGITUDE value as first parameter, before the LATITUDE value.
-- insert latitude, longitude, and geopoint for Ron Jon Surf Shop
INSERT [dbo].[GeoTest] (Location, Latitude, Longitude, GeoPoint)
VALUES ('Ron Jon Surf Shop (Cocoa Beach)',
'28.3543972',
'-80.605348',
geography::STGeomFromText('POINT (-80.605348 28.3543972)',4326))
GO
-- insert latitude, longitude, and geopoint for Starbucks Cocoa Beach
INSERT [dbo].[GeoTest] (Location, Latitude, Longitude, GeoPoint)
VALUES ('Starbucks (Cocoa Beach)',
'28.3572519',
'-80.610383',
geography::STGeomFromText('POINT (-80.610383 28.3572519)',4326))
GO
-- insert latitude, longitude, and geopoint for Coconuts Bar and Grill
INSERT [dbo].[GeoTest] (Location, Latitude, Longitude, GeoPoint)
VALUES ('Coconuts on the Beach (Cocoa Beach)',
'28.3182401',
'-80.6104571',
geography::STGeomFromText('POINT (-80.6104571 28.3182401)',4326))
GO
This is what our table records look like after inserting the GeoPoint data. I removed the GUID ID column for readability. Note the long string of characters for the GeoPoint.
Now that we have the GeoPoint for our Cocoa Beach, Florida locations, now we can do interesting things like find the distance between 2 GeoPoints, and once we have that info we can easily convert the data to miles or kilometers. The STDISTANCE function is what we do this with, which provides the distance info in meters, but can easily be converted to miles and kilometers, like in this example.
The following bit of TSQL code will get the distance between Coconuts and Ron Jon Surf Shop in Cocoa Beach, Florida.
-- create a var to store the geopoint for Coconuts Bar and Grill DECLARE @Coconuts geography; SELECT @Coconuts = [GeoPoint] FROM [GeoTest] WHERE Location = 'Coconuts'; -- get distance in miles SELECT [GeoPoint].STDistance(@Coconuts)/1609.344 AS DistanceMiles FROM [GeoTest] WHERE Location = 'Ron Jon Surf Shop (Cocoa Beach)'; -- get distance in kilometers SELECT [GeoPoint].STDistance(@Coconuts)/1000 AS DistanceKilometers FROM [GeoTest] WHERE Location = 'Ron Jon Surf Shop (Cocoa Beach)'; GO
THAT IS ALL THERE IS TO IT!! Even I was surprised about how easy it is to work with geospatial data with the geography type in Microsoft SQL Server databases. It is definitely easy and simple. Also the functions have many more options than what is in this example. You can also create geopoints for areas like rectangles and polygons, not just points, and also there are options to convert a geopoint into its corresponding Latitude and Longitude.
Here is what our distance calculations between Ron Jon Surf Shop and Coconuts Bar and Grill in Cocoa Beach, Florida look like after being calculated, they are long decimal values since the precision is down to a single meter, but can easily be rounded to whatever precision you desire.
Here is a link to the entire TSQL Script for our example of working with geospatial data in Microsoft SQL Server databases. I have it stored in my Azure DevOps SQL Server Source Code Repository at the following:
Now you might be saying to yourself, Ok how do I use this in an ASP.NET MVC Web Application using Entity Framework? That is an entirely different creature, where you use a data type in the C# programming language called DbGeography. In my next post I will provide info on how to do that with an ASP.NET MVC web application using Entity Framework. The ASP.NET MVC Web Application with Database First Entity Framework is now complete, with source code viewable on Azure DevOps at the following:
To see my Curriculum Vitae, go to Michael G Workman CV
To see my projects on Azure DevOps, go to https://dev.azure.com/AbionTechnology/
To see my Posts and Answers on Stack Overflow, go to Michael G. Workman on Stack Overflow
If you have any questions about C, C++, Microsoft C# .NET, Microsoft Azure Cloud, Unix, Linux, and/or Mobile Apps, please feel free to contact me by email at:
michael.g.workman@outlook.com
