|
I am working on a project where I have to allow users to find things that are near by. In the past I have just manually stored lat/longs and done my own calculations but I'm curious if it's worth looking into using the GeoSpatial features in SQL Server 2008. Are people out there having success using this for their spatial queries? Are there examples of large companies out there that are using this? Are there any common problems that I should be aware of? |
|||
|
|
|
While I can't answer for anyone else, and the company is not large that I work for, we have had a lot of success with using SQL Server 2008 and it's spatial capabilities. Because I work for a forestry company, the spatial side of things is very important to our business. If you are just doing nearest neighbour type analysis, then I would suggest giving the spatial functions a look, because this is specifically what it was built for in the first place. The spatial functions are fast for this type of thing, I would say a deal faster than something you coded yourself. For me, it's kind of like when people consider rolling their own custom super-strong homemade encryption. This sort of thing has a lot of edge cases and holes, so why recreate the wheel? Is it really worth the risk and time? What about if you get it wrong? Now I understand the risks between spatial and encryption are worlds apart, but the same sort of thing applies. Once you start using the native geometry, you get a lot more options for no extra work. We also run postgres with postgis as well, so my comparisons are based between these two. Postgres has VERY good spatial support, SQL Server 2008 is not on par yet in terms of functionality with Postgres. One thing I find annoying in SQL Server is it is not that easy to reproject if you are using the geometry datatype. Microsoft generally encourage the use of the geography type, which is lat/long based, but has distances and areas in metric units rather than degrees. We need to store data in geometry type, and as such to do area and distance calculations we regularly need to convert from geometry to geography and back again. Of course if you just store in geography, then this isn't an issue. In earlier versions you had to prompt the spatial indexes, but in the latest update, this seems to have smoothed out most of those sort of issues for us. I find if you are manually creating spatial indexes a bit tedious in Sql Server, but that is because you have to specify a bounding box (if you are using geometry type). Again, if you use geography, this is not necessary. GIS software support for SQL Server is getting better, with most of the major vendors offering direct connection to SQL Server. Open Source GIS is coming on board as well now that ogr has sql server 2008 connectors. I haven't had any experience with these, so I will not comment. We serve up map tiles of our data through Geoserver, which has a plugin to allow serving of data through SQL Server. Although it is not as mature (or as fast) as the Postgres implementation, it is still more than adequate for our needs. We are a .NET development shop and I have found the geometry / geography types exposed through the Microsoft.SqlServer.Types namespace to be intuitive enough to use. I haven't used it much, nor have I done anything fancy aside from converting from well-known-text to geometry and persisting it, but it did just work. There was some talk that this namespace wasn't able to be used in Silverlight, but if that is for you, then you would have to look into this further. We are using it with ASP.NET front end and compiled classes on the back, talking to the database and have had no troubles. You can build you spatial queries in code, then shoot them through to the database and get the results back if you so wish, same as if you were using a non-spatial datatype. I believe that the Microsoft Entity Framework does not support the spatial types as of the latest stable release, but the CTP released in June does have the geometry and geography support. I know that NHibernate does have spatial support, as do some of the other Micro ORMs that are popping up like PetaPoco. To get a feel for what people are using it for, a search on gis.stackexchange for sql server will probably render some interested cases. |
|||
|
|
|
The spatial functionality available in SQL Server does (amongst other things) just that. Find things that are nearby. Why re-invent the wheel? its optimized, and then you can always use all the other functions to make further analysis. So I would say-yes- its definitely worth looking into. As for case studies a quick qoogle search revealed this and this. And you can also go to http://www.microsoft.com/casestudies/ and search for SQL Server 2008 and spatial as keywords in the Advanced Search. |
|||
|
|
|
I know from my experience that many large companies successfully use (fill whatever here). Success measure is based on number of successful customer feedback e-mails sent before x-mas or after project finish. |
|||||||
|
