Check if GPS points reside area (SQL Server)


I have been playing around with Bing Maps, GPS coordinates and SQL Server 2012 spatial data methods tonight. I am not sure if I will use these methods in my applications, but they are pretty nice and runs pretty fast on SQL Server 2012.

The picture below shows are geographical area in my neighborhood. The points 1, 2 and 3 defines a 2-dimentional area, and the two point 4 and 6 resides within this area, but point is outside. My objective is to create SQL statements to check if these points are inside or outside the area.

blog_bing_area

The easiest way of doing this is to use the new data type geography introduced in SQL Server 2008. The first thing we need to do is to define the area by using a POLYGON of the points 1-3. Secondly, we need to define the Points using the POINT object. The POLYGON and POINT object can be created by using the static method Parse. The last thing to do, is to check if the points reside the area. This is archieved by using the OGC Method STContains.

Code:

DECLARE @area GEOGRAPHY;
DECLARE @kalsnes GEOGRAPHY;
DECLARE @rostad GEOGRAPHY ;
DECLARE @ringstadaasen GEOGRAPHY ;
DECLARE @coords NVARCHAR(200);

SET @coords = '59.306414 10.945834, 59.316500 10.999811, ' + 
			'59.269515 11.024359, 59.306414 10.945834';

SET @area = geography::Parse('POLYGON ((' + @coords + '))');
SET @kalsnes = geography::Parse('POINT(59.308294 11.046463)');
SET @rostad = geography::Parse('POINT(59.299119 10.992154)');
SET @ringstadaasen = geography::Parse('POINT(59.304000 10.970417)');

SELECT @area.STContains(@kalsnes) AS kalsnes1;
SELECT @area.STContains(@rostad) AS rostad1;
SELECT @area.STContains(@ringstadaasen) AS ringstadaasen1;

Happy coding…

Advertisements

One thought on “Check if GPS points reside area (SQL Server)

  1. Pingback: Calculate distance between two GPS points (SQL Server) « Sveroa's Developer Blog

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s