sql server 2008

First I'd like to say if you haven't tried GIS Stack Exchange, give it a try. It's a good resource to ask GIS questions of all kind and get answers.

We haven't had a chance to try out the new Denali CTP1, and probably won't be able to get to it for another month or so. It does seem to have some new exciting features for both Spatial as well as the basic database features. We are also looking to do another compare shoot-out between SQL Server, PostGIS, and Oracle. Since PostGIS and SQL Server are the ones we are most familiar with, we'll do a bit of contrast and compare here

Spatial compares of the top 3 commonly used spatial databases

Last time we did a survey such as this Cross Compare SQL Server 2008 Spatial, PostgreSQL/PostGIS 1.3-1.4, MySQL 5-6 we had in our mix MySQL instead of Oracle. Many people wanted to see Oracle in that comparison, so this time, we have replaced MySQL with Oracle.

In our new compare, Compare SQL Server 2008 R2, Oracle 11G R2, PostgreSQL/PostGIS 1.5 Spatial Features, we focus mostly on the feature sets as documented by each offering in their manuals. Given the breadth of functionality we were comparing, its quite possible we missed something. Please let us know if we made any erroneous statements or missed a critical function, and we will be happy to correct. Part of the point of this compare is also for migration purposes. If you are using a feature in databasse X and want to know if you can still do the same or if there is a comparable function in database Y, we hope this will be helpful in ascertaining that.

We really wanted to include SpatiaLite in there, but SpatiaLite fills a slightly different niche from the aforementioned 3 that I think in most cases is complimentary to the 3 rather than direct competition. The fact that SpatiaLite is built on SQLite and much of the same plumbing that PostGIS is built on (GEOS/Proj), means it lacks some of the enterprise features we have come to expect from an enterprise database -- e.g strong aggregation functions, stored procedures/functions, but on the plus side is much more portable (suitable for in the field work) and still has a lot of spatial power under its toolbelt.

PostGIS in Action status

We have received our final set of reviews as well as blog reviews of our book PostGIS in Action and are still making revisions to the chapters based on them and prior reviews. The reviews were very positive. The last set of reviews, we got perfect scores. However there were noted areas of improvement. For one, I guess people are really into real examples rather than made up ones. As a result, we have rewritten chapter 1 to be more of a 60 minute quick course in loading geometry/geography, viewing, and writing spatial queries against which will be filled in with greater and greater depth in later chapters.

We've also tried to sprinkle along the way in the book, non-US examples. This is tricky though since US data is the data set we have most access to and can esily package in without licensing issues. Any thoughts on Non-US datasets we can use would be welcome. There are some things we will have a harder time with such as adding more pictures and more polished pictures. Those unfortunately just add a lot of overhead. We will try our best to improve on that where it doesn't considerably impact cost.

As far as blog reviews, we thank Bill Dollins and Mark Leslie for their great blog reviews. It is nice to know that we are hitting the audience and level of depth we had hoped to.

Essential PostgreSQL Refcard is out

We've been working on writing our book on PostGIS PostGIS in Action. We have also been working on an Essential PostgreSQL DZone RefCardz to both provide free publicity for our upcoming book and also to hopefully serve a greater good to the PostgreSQL community. We are pleased to announce that the RefCard is finally done and out on the street. We still have a ways to go with the book (slaving on chapter 8), but we hope the RefCard will be a nice companion to our book. The RefCardz is available for free download at Essential PostgreSQL http://refcardz.dzone.com/refcardz/essential-postgresql?oid=hom12841.

If you've never looked at a DZone RefCard, we encourage you to. They are basically 6 pages of the most important (well at least what the author thought) need to know about a piece of software or technology. They also make attractive handouts at conferences. We did an Essential PostgreSQL because we felt there was a greater audience that could benefit from that card not just in the spatial community, but in the PostgreSQL community at large. We hope to do one later targeting PostGIS. It would be neat if similar cards could be done for other common Open source GIS tools such as Mapserver, Geoserver, and OpenLayers.

New on the PostGIS front

Paul has managed to sneak in Geography support in the upcoming PostGIS 1.5. There isn't much to see yet except ability to create geography using PostgreSQL typmod syntax, do bounding box queries, convert back and forth between geometry and geography. You can think of the new geography counterpart as native geodetic support similar to geography in SQL Server 2008. It will eventually have ST_Distance and ST_DWithin functionality and hopefully that will make it into PostGIS 1.5. We'll be providing experimental windows builds of this in the next week or so.

One of the interesting things to come out this week is the SQL Server 2008 R2 CTP

One of the enhancements we are really looking forward to and interested in experimenting with is the Report Builder 3.0/Reporting Services support for Geospatial data. We hope to try out the SQL Server 2008 R2 CTP 1 in the next 2 or so weeks to test drive these features and maybe provide some reporting tutorials on BostonGIS.

One thing I am interested in seeing is whether the geospatial support can be leveraged for other spatial databases besides SQL Server. One thing I have to say is nice about the SQL Server add-ons like SSIS, Reporting Services etc. is that they can be used for other databases such as Oracle and PostgreSQL. Reporting Services has native drivers for Oracle and I believe Terradata and some others. It would be nice to implement similar drivers for PostgreSQL/MySQL and other open source databases. For most OS dbs, you usually have to use the ODBC driver,which is okay but not great. Its strange you can't use the ADO.NET driver, it seems you need a special Reporting Services provider driver, or at least we haven't been able to. It is possible to use the report builder in a stand alone mode (client mode -- no reporting services) and then I believe you can use ADO.NET drivers. We haven't played around with that but plan to.

To be honest we don't really use reporting services that much, but lately we have been forced into it since a lot of our bigger clients are married to it mostly because of the subscription support capabilities that allows you to mail reports on a scheduled basis to groups of users. The report builder isn't too bad either with some training. Its also tied in with other Microsoft stack things like Microsoft CRM/SharePoint etc. that many of our clients use so is a big part of their workflow.

We also want to check out how the spatial support has changed/improved since the SQL Server 2008 RTM.

More Database comparisons

We also just updated our basic comparison between SQL Server 2008, PostgreSQL 8.4, and MySQL 5.1 . This is not the spatial part, just general database features. It seems to be garnering a lot of traffic.

For our spatial compare that we did a while back. SQL Server 2008, PostGIS, MySQL, we'll probably repeat that exercise again when SQL Server 2008 R2 and PostGIS (1.5 or 2.0) come out. For our next iteration, we'll probably leave out MySQL since MySQL spatial support doesn't appear to have changed much. But instead we will replace with Oracle Spatial/Locator (and possibly Db II). Since more people seemed interested in how Oracle compares with the other two than how MySQL compares.

One thing we are really fond of are the ANSI SQL Standard Row Constructors. PostgreSQL has supported this feature since 8.2 I believe and SQL Server 2008 supports it too, but prior SQL Server's do not. MySQL has supported for as far back as I can remember.

Any rate we thought what a fun way to play around with row constructors in SQL Server 2008 than to write queries we would write in PostGIS in SQL Server 2008. The results were a bit surprising when testing out Centroid. The STPointOnSurface I can accept as okay since I think the point is only guaranteed to be on the surface and when you look at say IBM DB II specs and ESRI manual it suggests these should only work for POLYGONS and MULTIPOLYGONS. Both PostGIS and SQL Server 2008 seem to return point on surface for any common geometry and both are on the surface just a different point. For Centroid however SQL Server as far as we can tell doesn't work for points, multipoints, linestrings and I presume probably only works for POLYGONS and MULTIPOLYGONs. Checking the docs confirms that SQL Server does not return a centroid for anything but POLYGONS and MULTIPOLYGONS. This is a bit of a bummer. Well given that PointOnSurface always works and that is what a lot of people expect from Centroid I guess the lesson is to use STPoinOnSurface. Centroid is not guaranteed to return a point on the surface in any spatial database we know of especially when you have polygons with holes.

UPDATE: On further inspection it appears the MM specs (at least the version I have seen) only specify centroid and point on surface for surface geometries (polygons and multipolygons) and spatial databases seem a bit split as to how they extend the spec in this regard. PostGIS extends the concept to apply to all common geometries. SQL Server extends it for PointOnSurface but not for Centroid. IBM extends it for centroid but not PointOnSurface and Oracle appears to read the spec to the letter not extending for either.

Anyrate here are 2 queries which are in theory identical but not.

--Query on SQL Server 2008

SELECT name, the_geom.STCentroid().STAsText() As cent_geomwkt, 
            the_geom.STPointOnSurface().STAsText() As pos_geomwkt
FROM 
( VALUES ('mpoint', geometry::STGeomFromText('MULTIPOINT(-1 1, 0 0, 2 3)',0 ) ),
('mlinestring', geometry::STGeomFromText('MULTILINESTRING((0 0,0 1,1 1),(-1 1,-1 -1))',0) ),
('polygon', geometry::STGeomFromText('POLYGON((-0.25 -1.25,-0.25 1.25,2.5 1.25,2.5 -1.25,-0.25 -1.25), 
(2.25 0,1.25 1,1.25 -1,2.25 0),(1 -1,1 1,0 0,1 -1))', 0) )  ) As foo(name, the_geom);

--Results
name      cent_geomwkt    pos_geomwkt
point	NULL	POINT (-1 1)
mpoint	NULL	POINT (2 3)
mlinestring	NULL	POINT (0 0.5)
polygon	POINT (1.125 0)	POINT (1.1666666666666667 1.1666666666666667)

Same query on PostGIS :

SELECT name, ST_AsText(ST_Centroid(the_geom)) As cent_geomwkt, 
     ST_AsText(ST_PointOnSurface(the_geom)) As pos_geomwkt
FROM (VALUES ('point', ST_GeomFromText('POINT(-1 1)') ),
('mpoint', ST_GeomFromText('MULTIPOINT(-1 1, 0 0, 2 3)') ),
('mlinestring', ST_GeomFromText('MULTILINESTRING((0 0,0 1,1 1),(-1 1,-1 -1))') ),
('polygon', ST_GeomFromText('POLYGON((-0.25 -1.25,-0.25 1.25,2.5 1.25,2.5 -1.25,-0.25 -1.25), 
(2.25 0,1.25 1,1.25 -1,2.25 0),(1 -1,1 1,0 0,1 -1))') )  )
As foo(name, the_geom);

--Results
    name     |               cent_geomwkt                |   pos_geomwkt
-------------+-------------------------------------------+-----------------
 point       | POINT(-1 1)                               | POINT(-1 1)
 mpoint      | POINT(0.333333333333333 1.33333333333333) | POINT(-1 1)
 mlinestring | POINT(-0.375 0.375)                       | POINT(0 1)
 polygon     | POINT(1.125 0)                            | POINT(-0.125 0)

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.

A few people have been asking us what are the pros and cons of using SQL Server 2008 Spatial and PostGIS and as a Windows user, why would you still consider using PostGIS. Rather than simply providing some hand-waving saying "well if you just care about displaying data, then use whatever you feel comfortable with, but if you want to do real intensive sophisticated spatial analysis and geometric processing without having to purchase a bunch of expensive software, then PostGIS is probably better for you. Hell why must you think in either or propositions - just use both using the strengths of each.", we have tried really hard to quantify the similarities and differences between the 2 and to boot - we have also added in MySQL.

Our analysis can be found at Cross Compare SQL Server 2008 Spatial, PostgreSQL/PostGIS 1.3-1.4, MySQL 5-6

If you have any comments, suggestions of additions, things you felt we got wrong, then please don't hesitate to comment and we'll try to update our survey.

I see that Manifold 8.0 is out and sounds like a great leap forward from prior Manifold versions and just a great leap forward period.

I've heard a lot about Manifold lately via various blogs e.g A manifold blogger and just general web stumbling. For example we see James blog has a lot of rants and raves about Manifold and Simon Greener, seems to like it and details it in his Mainfold Tips and Tricks, granted those tips are kind of old. Ironically I haven't physically come into contact with anyone using Manifold. Most of what I get when I ask my GIS neighbors is "What's that?"

Just from my general readings of it - it sounds like a database spatial analysts / .NET Windows developer's dream tool.

• Supports Oracle Spatial, ESRI Geodatabase, Shape, and the new version 8.0 supports - PostGIS, IBM DBII Spatial Blade, and SQL Server 2008 Spatial Katmai which hasn't even come out yet.
• A free Manifold Spatial Extender for SQL Server 2005 that provides spatial index capability to SQL Server 2005
• The price is fairly affordable compared to ArcGIS and MapInfo tools.
• Relatively inexpensive add-ons for doing sophisiticated GIS processing
• A GIS tool that is an honest to goodness 64-bit capable - not running in some WOW64 emulation layer.
• Direct SFSQL and database access - not some funky middleware to hop thru.
• IronPython scripting and I assume other .NET scripting as well.

If this product is even 25% as good as it sounds like - it is a REALLY REALLY good value.

Given that there seem to be very few of us, at least in my neck of the woods, (those who are intimately familiar and love the power of relational databases but are enamored with the even greater power that a relational database married with GIS capabilities can bring and I'm not talking about a database as a passive storage container for GIS objects) and the fact that I don't think Manifold markets as much as ESRI, I would say I'm not too surprised that few I have run into use Manifold. I really hope the kingdom of database spatial analysts is growing and I feel it is, which from my cursory guess can only be a good thing for Manifold.

Of course I guess having a checklist of bullets is not the final test. There is stability, speed, ease of use and all that other good stuff that you can't easily qualify in bullets.

This document that details differences between doing tasks in Manifold and ArcGIS does make it look pretty user friendly. I am eager to try Manifold out and see how good its new PostGIS and SQL Server 2008 capabilities are in particular. I'm also equally interested in hearing from people who have used Manifold (especially if they have used the new one and with PostGIS) and how it compares to other tools they have used e.g MapInfo, ArcGIS, Intergraph, QGIS, uDig, GRASS etc.