BostonGIS

I've been whining a lot lately about how SQL Server 2008 (and none of the other SQL Server's) have a generate_series() function that I have grown to love in PostgreSQL. Admittedly I've just been too lazy to create one even though its not that difficult of a task.

Simon Greener over at Spatial DBAdvisor heard my whining and I guess got fed up enough to create a generate_series() function for SQL Server 2008. He also has a generate_series function for Oracle too by the way.

Now there are a couple of differences between the way you use it in the 3 databases which are caused by fundamental differences between the architectures of the 3 databases.

We have published the first draft of our SQL Server 2008 Spatial Part 3 tutorial. In this part, we demonstrate how to install the SQL Server Spatial Tools which includes the much needed Union aggregate function. More examples of its use will follow.

We recently published our second tutorial in our SQL Server 2008 spatial series SQL Server 2008 Spatial: Reproject data and More Spatial Queries. This one goes over transforming and loading data, quick look at analyzing plans and more simple spatial queries. We also put in links to other tutorials we found useful on the web.

In looking back at this, I noticed that we violated something. According to OGC spec, ST_PointN, STPointN - is only defined for LINESTRINGS and MULTIPOINTS, but SQL Server 2008 allows you to get away with using this for POLYGONS. If you try the same thing in PostGIS you will get null and I suspect the same holds true with DB2 and Oracle

I guess its somewhat debateable whether it is right to do more than what the spec says or not. Paul Ramsey mentioned (and I think he even changed in PostGIS trunk to have ST_NumPoints (which is only supposed to work for linestrings and multipoint?) to be an alias for ST_NPoints (which is not an OGC spec, but works for everything in PostGIS). STPointN/ST_PointN I guess is more arguable whether it should work for POLYGONS/MULTIPOLYGON because suppose for complex - its unclear what you would consider the order of points where rings are involved. I guess even there there is a natural order.

I suppose from a portability stand point its annoying to have things named the same or sort of the same that don't behave exactly the same across the databases you work with, but then again we don't live in an ideal world and who wants everything to be the same anyway? Where is the choice in that? So needless to say I am torn especially in cases where it seems the spec was short-sighted. ST_PointN/STPointN/ST_NumPoints/STNumPoints to me is one of those cases.

Anyrate stay tuned for the 3rd in our series.

We have started to take a really hard look at what SQL Server 2008 offers. Aside from the usual stuff that makes a database lover's heart go thumpety thump -

• More SQL constructs
• Enhancements to Reporting Services
• New expansion of SQL Server database storage to use filesystem for large document storage AKA (FILESTREAM storage)
• Powershell integration
• Introduction of sparse columns more suitable for datewarehouse type stuff
• And of course introduction of spatial which makes a spatial database analysts heart go thumpety thump thump.

To commemorate our tour of the new SQL Server, we have started a new section in BostonGIS called SQL Server 2008 Tutorials and have put in our very first article. Part 1: Getting Started with SQL Server 2008 Spatial: An almost Idiot's Guide. We are still learning so feel free to let us know where we went wrong.

Those who have read our popular Part 1: Getting Started With PostGIS: An almost Idiot's Guide will recognize some similarities and those are for the most part intentional. Does this mean we are abandoning PostGIS? No. We are technologists and implementors and as such, it is always important to have at least 2 answers to every question. For example PostGIS has the advantage of running on more operating systems and a growing and not too shabby ecosystem and a very nimble architecture, while SQL Server 2008 although it runs only on Windows, it has a massive ecosystem behind it from third-party's and Microsoft itself (e.g. SharePoint, CRM, Virtual Earth), which for many of the clientele we deal with are not too shabby things.

It is equally important to stick with standards and themes within reason because standards provide the glue that allows disparate systems to interoperate. Focusing on standards also means the less you need to learn to get stuff done. The main thing we like about both of these platforms, is that they try to stick with ANSI database standards and OGC standards. This is very important because it means there is synergy with learning both. Learning one helps reinforce learning the other - its sort of like analyzing a coin and flipping it on its side. Where the requirements of one project rules out one for one reason or another , the other may still be a likely fit. In fact we might just have some chocolate and peanut butter examples in the wings.