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 near where I live in Cape Canaveral, Florida, 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 GitHub SQL Server Source Code Repository at the following URL:

https://github.com/Michael-G-Workman/SQL-Server/blob/master/GeoSpatial.sql

 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, and is being hosted on the Microsoft Azure Cloud Platform at the following URL:

https://geospatial20190511042739.azurewebsites.net/


I also have publicly viewable source code repositories for some software applications I have worked on at the following GitHub URL;

https://github.com/Michael-G-Workman/







Comments

Popular Posts