You are currently browsing the category archive for the ‘Spatial’ category.
On of the problems when working with spatial data is figuring out what to GROUP BY when aggregating data spatially. I’ll talk more about this in a future post and, for one of the approaches I tried, I needed the ability to draw an arbitrary geography polygon (square in my case) that is yay long and wide.
Obviously with the geography type, you can’t just add n units to the x/y axes as you need geographic co-ordinates (latitude and longitude). I needed a function were I could find out the co-ordinates of a point, say, 500 metres north of the current position.
I turned to Chris Veness’ excellent source of spatial scripts (http://www.movable-type.co.uk/scripts/latlong.html) and cribbed the rhumb line function, converting it into T-SQL.
The function takes start point, a bearing (in degrees) and a distance in metres – returning a new point.
Please note that this function is hard coded for WGS84 (srid 4326) and may need tweaking to get the earths radius out of sys.spatial_reference_systems or changed to suit your requirement.
Disclaimer: This code is provided as-is with no warranties and suits my purpose, but I can’t guarantee that it will work for you and the missile guidance system that you may be building.
CREATE FUNCTION [dbo].[RumbLine]
--Rumb line function created by Simon Munro
--Original post at Original post at
--Algorithm thanks to
--Hard coded for WGS84 (srid 4326)
DECLARE @result GEOGRAPHY;
DECLARE @R FLOAT = 6378137.0;
DECLARE @lat1 FLOAT, @lon1 FLOAT;
DECLARE @lat2 FLOAT, @lon2 FLOAT;
SET @distance = @distance/@R;
SET @bearing = RADIANS(@bearing);
SET @lat1 = RADIANS(@start.Lat);
SET @lon1 = RADIANS(@start.Long);
SET @lat2 = ASIN(SIN(@lat1)*COS(@distance) + COS(@lat1)*SIN(@distance)*COS(@bearing));
SET @lon2 = @lon1 + ATN2(SIN(@bearing)*SIN(@distance)*COS(@lat1), COS(@distance)- SIN(@lat1)*SIN(@lat2));
SET @lon2 = CONVERT(DECIMAL(20,8), (@lon2+3*PI())) % CONVERT(DECIMAL(20,8),(2*PI())) - PI();
DECLARE @resultText VARCHAR(MAX);
SET @resultText = 'POINT('+CONVERT(VARCHAR(MAX),DEGREES(@lon2)) +' '+ CONVERT(VARCHAR(MAX), DEGREES(@lat2))+')';
SET @result = geography::STGeomFromText(@resultText, 4326)
An investigation triggered by the lack of support of spatial data in SQL Azure has left me with the (unconfirmed) opinion that although requested by customers, the support of spatial data in SQL Azure may not be good enough to handle the requirements of a scalable solution that has mapping functionality as a primary feature.
Update: SQL Azure now has spatial support. The arguments made in this post are still valid and use of spatial features in SQL Azure should be carefully considered.
I have been asked to investigate the viability of developing a greenfields application in Azure as an option to the currently proposed traditional hosting architecture. The application is a high load, public facing, map enabled application and the ability to do spatial queries is on near the top of the list of absolute requirements. The mapping of features from the traditionally hosted architecture is fine until reaching the point of SQL 2008’s spatial types and features which are unsupported under SQL Azure – triggering further investigation.
It would seem that the main reason why spatial features are not supported in SQL Azure is because those features make use of functions which run within SQLCLR, which is also unsupported in SQL Azure. The lack of support for SQLCLR is understandable to a a degree due to how SQL Azure is setup – messing around with SQLCLR on multitenant databases could be a little tricky.
The one piece of good news is that some of the assemblies used by the spatial features in SQLCLR are available for .NET developers to use and are installed into the GAC on some distributions (R2 amongst them) and people have been able to successfully make use of spatial types using SQL originated/shared managed code libraries. Johannes Kebeck, the Bing maps guru from MS in the UK, has blogged on making use of these assemblies and doing spatial oriented work in Azure.
So far, it seems like there may be a solution or workaround to the lack of spatial support in SQL Azure as some of the code can be written in C#. However, further investigation reveals that those assembles are only the types and some mathematics surrounding them and the key part of the whole process, a spatial index, remains firmly locked away in SQL Server and the inability to query spatial data takes a lot of the goodness out of the solution.
No worries, one would think – all that you need to do is get some view into the roadmap of SQL Azure support for SQL 2008 functionality and you can plan or figure it out accordingly. After all, the on the Microsoft initiated, supported and sanctioned SQL Azure User voice website mygreatsqlazureidea.com, the feature ‘Support Spatial Data Types and SQLCLR’ comes out at a fairly high position five on the list with the insightful comment ‘Spatial in the cloud is the killer app for SQL Azure. Especially with the proliferation of personal GPS systems.’ The SQL Azure team could hardly ignore that observation and support – putting it somewhere up there on their product backlog.
When native support for spatial data in SQL Azure is planned is another matter entirely and those of us on the outside can only speculate. You could ask Microsoft directly, indirectly or even try and get your nearest MVP really drunk and, when offered the choice between breaking their NDA and having compromising pictures put up on Facebook, will choose the former.
Update: You use your drunk MVP to try and glean other information as it was announced that SQL Azure will support spatial data in June 2010 http://blogs.msdn.com/sqlazure/archive/2010/03/19/9981936.aspx and http://blogs.msdn.com/edkatibah/archive/2010/03/21/spatial-data-support-coming-to-sql-azure.aspx (see comments below). This is not a solution to all geo-aware cloud applications, so I encourage you to read on.
I have n-th hand unsubstantiated news that the drastic improvements for spatial features in SQL 2008 R2 were made by taking some of the functionality out of SQLCLR functions and putting them directly into the SQL runtime which means that even a slightly deprecated version of SQL Azure based on R2, which I think is inevitable, would likely have better support for spatial data.
Update: In the comments below, Ed Katibah from Microsoft, confirms that the spatial data support is provided by SQL CLR functionality and not part of the R2 runtime.
In assessing this project’s viability as an Azure solution, I needed to understand a little bit more about what was being sacrificed by not having SQL spatial support and am of the opinion that it is possibly a benefit.
Stepping back a bit, perhaps it is worthwhile trying to understand why SQL has support for spatial data in the first place. After all, it only came in SQL 2008, mapping and other spatial applications have been around longer than that and, to be honest, I haven’t come across many solutions that use the functionality. To me, SQL support of spatial data is BI Bling – you can, relatively cheaply (by throwing a table of co-ordinates against postal codes and mapping your organizations regions) have instant, cool looking, pivot tables, graphs, charts and other things that are useful in business. In other words, the addition of spatial support adds a lot of value to existing data, whose transactions do not really have a spatial angle. The spatial result is a side effect of (say) the postal code, which is captured for delivery reasons rather than explicit BI benefits.
The ability to pimp up your sales reports with maps, while a great feature that will sell a lot of licences, probably belongs as a feature of SQL Server (rather than the reporting tool), I question the value of using SQL as the spatial engine for an application that has spatial functionality as a primary feature. You only have to think about Google maps, streetview and directions with the sheer scale of the solution and the millions of lives it affects and ask yourself whether or not behind all the magic there is some great big SQL database serving up the data. Without knowing or Googling the answer, I would suggest with 100% confidence that the answer is clearly ‘No’.
So getting back to my Azure viability assessment, I found myself asking the question.
If SQL Azure had spatial support, would I use it in an application where the primary UI and feature set is map and spatially oriented?
But before answering that I asked,
Would I propose an architecture that used SQL spatial features as the primary spatial data capability for a traditionally hosted application where the primary UI and feature set is map and spatially oriented?
The short answer to both questions is a tentative no. Allow me to provide the longer answer.
The first thing to notice about spatial data is that things that you are interested in the location of don’t really move around much. The directions from Nelsons Column to Westminster Abbey are not going to change much and neither are the points of interest along the way. In business you have similar behaviour – customers delivery addresses don’t move around much and neither do your offices, staff and reporting regions. The second thing about spatial data is the need to have indexes so that queries, such as the closest restaurants to a particular point, can be done against the data and spatial indexes solve this problem by providing tree like indexing in order to group together co-located points. These indexes are multidimensional in nature and a bit more complex than the flatter indexes that we are used to with tabular data.
Because of the slow pace at which coastlines, rivers, mountains and large buildings move around, the need to have dynamically updated spatial data, and hence their indexes, is quite low. So while algorithms exist to add data to spatial indexes, the cost of performing inserts is quite expensive, so in many cases indexes can be rebuilt from scratch whenever there is a bulk modification or insert of the underlying data.
So while SQL Server 2008 manages spatial indexes as with any other index, namely by updating the index when underlying data changes, I call into question the need for having such functionality for data that is going to seldom change.
If data has a low rate of change, spatial or not, it becomes a candidate for caching, and highly scalable websites have caching at the core of their solutions (or problems, depending on how much they have). So if I were to scale out my solution, is it possible to cache the relatively static data and the spatial indexes into some other data store that is potentially distributed across many nodes of my network? Unfortunately, unlike a simple structure like a table, the data within a spatial index (we are talking about the index here and not the underlying data) is wrapped up closely to the process or library that created it. So, in the case of SQL Server, the spatial index is simply not accessible from anywhere other than SQL Server itself. This means that I am unable to cache or distribute the spatial indexes unless I replicate the data to another SQL instance and rebuild the index on that instance.
So while I respect the functionality that SQL Server offers with spatial indexing, I question the value of having to access indexed data in SQL server just because it seems to be the most convenient place to access the required functionality (at least for a Microsoft biased developer). If my application is map oriented (as opposed to BI bling), how can I be sure that I won’t run into a brick wall with SQL server with spatial indexes in particular. SQL server is traditionally known as a bottleneck with any solution and putting my core functionality into that bottleneck, before I have even started and without much room to manoeuvre is a bit concerning.
I should be able to spin up spatial indexes wherever I want to and in a way that is optimal for a solution. Perhaps I can have indexes that focus on the entire area at a high level and can generate lower level ones as required. Maybe I can pre-populate some indexes for popular areas or if an event is going to take place in a certain area. Maybe I am importing data points all of the time and don’t want SQL spending time churning indexes as data, which I am not interested in yet, is being imported. Maybe I want to put indexes on my rich client so that the user has a lighting fast experience as they scratch around in a tiny little part of the world that interests them.
In short, maybe I want a degree of architectural and development control over my spatial data that is not provided my SQL’s monolithic approach to data.
This led me to investigating other ways of dealing with spatial data (generally), but more specifically spatial indexes. Unsurprisingly there are a lot of algorithms and libraries out there that seem to have their roots in a C and Unix world. The area of spatial indexing is not new and a number of algorithms have emerged as popular mechanisms to build spatial indexes. The two most popular are R-Tree (think B-Tree for spatial data) and Quadtree (where a tree is built up by dividing areas into quadrants).
There is a wealth of information on these fairly well understood algorithms and event Microsoft’s own implementations do not fall far from these algorithms. Bing maps uses ‘QuadKeys’ to index tiles, seemingly referring to the underlying Quadtree index. (SQL Server is a bit different though, it uses a four level grid indexing mechanism that is non recursive and uses tessellation to set the granularity of the grid.)
So if all of this spatial data stuff is old hat, surely there are some libraries available for implementing your own spatial indexes in managed code? It seems that there are some well used open source libraries and tools available. Many commercial products and Sharpmap, an OSS GIS library, make use of NetTopologySuite, a direct port of the Java based JTS. These libraries have a lot of spatial oriented functions, most of which only make vague sense to me, including a read only R-Tree implementation.
Also, while scratching around, I got the sense that Python has emerged as the spatial/GIS language of choice (it makes sense considering all those C academics started using Python). It seems that there are a lot of Python libraries out there that are potentially useful within a .NET world using IronPython.
It is still early in my investigation, but I can’t help shaking the feeling that making use of SQL 2008 for spatial indexing because that is the only hammer that Microsoft provides is not necessarily the best solution. This is based on the following observations:
Handling of spatial data is not new – it is actually a mature part of computer science. In fact SQL server was pretty slow to implement spatial support.
An RDBMS like SQL or Oracle may be a good place to store data, but not necessarily the best place to have your indexes. The SQL bias towards data consistency and availability are counter to the demands of spatial data and their indexes.
In order to develop a map oriented solution, a fine degree of control over spatial data may be required to deliver the required functionality at scale.
While I am not against OSS, evaluating libraries can be risky and difficult and I am stunned at the lack of support for spatial data in managed code coming out of Microsoft. Microsoft needs to pay attention to the demand for support of spatial data for developers (not just database report writers). The advent of always connected geo-aware mobile devices and their users’ familiarity with maps and satnav, will push the demand for applications that are supportive of geographic data. It is not unlikely to picture the teenager demand for a map on their mobile devices that shows the real time location of their social network.
To support this impending demand, Microsoft needs to make spatial data a first class citizen of the .NET framework (system.spatial). It wouldn’t take much, just get some engineers from SQL and Bing maps to talk to each other for a few weeks. Microsoft, if you need some help with that, let me know.
In the meantime I will walk down the road of open source spatial libraries and let you know where that road leads.