Why you should consider MS SQL Server for your GPS enabled mobile apps

How SQL Server can make your mobile apps better with GPS

By  

When it comes to working with spatial data in mobile software, the business needs can vary greatly. At a basic level, you're probably going to be working with latitude and longitude coordinates as a base for the complex math involved with route calculation, distance calculation, proximity and so forth. This is where Microsoft SQL Server (2008 and greater) can help you out.

First, let me take a moment to quell the Microsoft haters out there. I'm well aware of the cost associated with running the Microsoft tech stack, but if you are in a position where you have a Windows server available or you're already a Microsoft shop, you'll find that the free version of MSSQL Server 2012 Express can suffice for quite some time. It is a fantastic product with excellent (free) management tools and Redmond has been so nice as to increase the maximum database file size in their free version to 10GB which can see a startup off the ground at a minimum. Coupled with their polished IDE, Visual Studio Express (also free), you're really only left covering the cost of the operating system.

When you have a need to store global coordinates, you'll have to make a decision on whether to simply create two decimal columns for the latitude and longitude, or use one column of the geography type. The two spatial data types are geometry and geography. In most cases, you'll be more interested in the geography type since it provides the round earth perspective where geometry is more of a flat view system of spatial data. The plain decimal columns can show far better performance when doing a large number of read operations on coordinates, but by using the geography type you open up a whole new world of possibilities that SQL Server provides.

Let's say you want to find the distance between two points that you've stored in two plain decimal columns. You could use the following JavaScript:

var R = 6371; // km
var dLat = (lat2-lat1).toRad();
var dLon = (lon2-lon1).toRad();
var lat1 = lat1.toRad();
var lat2 = lat2.toRad();
var a = Math.sin(dLat/2) * Math.sin(dLat/2) +
Math.sin(dLon/2) * Math.sin(dLon/2) * Math.cos(lat1) * Math.cos(lat2);
var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
var d = R * c; // your distance in km.

 

Or, using the geography data type, you could do the following, thanks to SQL Server:

/*WHERE @A and @B are geography Points*/
SELECT @A.STDistance(@B) / 1000 /*your distance in km*/

 

There are well known methods and code to perform these types of calculations but once you get into intersecting paths and proximity boundaries, it is certainly not basic stuff. MS SQL server provides you with a bounty of built-in geo-spatial functions you can use with a small amount effort and your geography data type, a full list of which can be found on the MSDN page. On top of that, if you're programming with .NET and the Entity Framework, you can use built-in LINQ helpers to make this process even easier. For example, let's suppose we have a MS SQL database storing lat/long points for restaurants in your city using the geography data type and we're using Entity Framework 5 to query the database:

DbGeography myLocation = DbGeography.PointFromText("POINT(" + Longitude
+ " " + Latitude + ")", 4326);

var restaurants = (from r in db.Restaurants
where myLocation.Distance(r.Location) <= 5
select r).ToList();

 

You now have back a list of restaurants within 5 km of your current location. These are pretty simple examples, but for anyone familiar with this type of software problem, this should come as a relief.

 

Spatial Indexing

With SQL Server 2012 the performance has been improved with regards to spatial data, but you can also take extra measures to speed up your calculations. Spatial Indexes play a big role in reducing the number of calculations necessary to determine position and distance in relation to area rather than a single point. The spatial index works by breaking the surface of the globe up into zones with several levels of granularity. Similar to a normal database index, your query execution plan uses the index to get hints on shortcuts the engine can take to return your answer.

I've only touched on a few reasons why I recommend MS SQL Server and its geography data type for your mobile applications, but hopefully these basic insights into its use will encourage you to take a closer look at the technology. I've seen substantial rewards with minimal effort by taking this approach. Removing the stigma of costly development and complexity for GPS enabled applications can bring a lot of new potential to the table, and it's always something the user or the client is going to be excited about.

Join us:
Facebook

Twitter

Pinterest

Tumblr

LinkedIn

Google+

Join us:
Facebook

Twitter

Pinterest

Tumblr

LinkedIn

Google+

Ask a Question
randomness