SQL Server 2010 Denali is coming

• Allow of computed columns that involve spatial functions. I presume you can index on these as well.
• Finally we have built-in aggregate functions for geography and geometry: UnionAggregate, EnvelopeAggregate, CollectionAggregate, ConvexHullAggregate.
• General improvement in speed all CLR UDTs to the tune of 5-20% faster than SQL Server 2008, whihc should make the built-in aggregate support much much faster than the SpatialTools add-on in SQL Server 2008
• Allow invalid geography types to be inserted and introduction of 2 new functions for geography STIsValid, MakeValid(). Again they have 2 up on PostGIS here since we currently have no mechanism in PostGIS to validate a geography or make valid aside from transforming it to a planar reference system, checking validatity (and in 2.0 using the ST_MakeValid function) and then transforming back to geography.
• Support for geographies that cover more than a hemisphere as well as a function for fixing ring orientation called ReorientObject() where you really intended the object to only fit in one hemisphere. You can even do this now to have the whole globe in your database: GEOGRAPHY::STGeomFromText('FULLGLOBE',4326);
• Geography now sports STWithin(), STContains(), STOverlaps(), STConvexHull(), similar to what geometry has enjoyed
• Better spatial indexing features like faster building of indexes, compressed indexes, a new kind of index called a GEOGRAPHY_AUTO_GRID and a new SPATIAL_WINDOW_MAX_CELLS spatial index hint when using spatial indexes in a query. It will be interesting to compare the speed performances achieved with these new features.
• Optimization of STIntersects(), STWithin(), STContains(), STOverlaps() when working with point data
• Speedier nearest neighbor queries of the form SELECT TOP 5... ORDER BY loc.STDistance(@someloc)
• More diagnostic helper functions: sp_help_spatial_geography_histogram, sp_help_spatial_geometry_histogram
• Support for curved geometries and geographies -- e.g. CIRCULARSTRING, CURVEPOLYGON, COMPOUNDCURVE and COMPOUNDCURVES in CURVEDPOLYGONs. It's true that PostGIS has had this since about 1.4, but SQL Server has a leg up on PostGIS here by supporting curves in geography datatype. Along with those new types are STCurveToLine, CurveToLineWithTolerance which again PostGIS has had for a while in ST_CurveToLine and its overloaded version. In addition they have a special buffer function just for curves called. I think they have a leg up on Oracle there too.

One feature which still seems glaringly missing unless I missed it is built-in transformation support (reprojection e.g converting a state plane planar geometry to geography and vice versa). This something that both PostGIS and Oracle have that still appears to be lacking in Denali. For new age mapping in a world where we only have geography for earth and geometry just for non-geographic, this may not be needed. I don't think we are quite there yet.

As far as where PostGIS is going in 2.0 -- you can check out Paul Ramsey's State of PostGIS he gave at FOSS4G Japan video for a brief summary.

Of course no spatial data management system would be worth its weight in gold without the DBMS that feeds and clothes it. So what does SQL Seerver 2010 Denali offer to make the spatial even more juicy.

• As mentioned already better CLR UDT and function support, which means faster performance for spatial functions and indexes.
• Finally we have dynamic paging using OFFSET FETCH -- similar to what PostgreSQl, MySQL, and SQLite have had in their LIMIT OFFSET offerings. So you will be able to write a query like:

  SELECT o.order_id, o.customer_name
          FROM orders AS O
          ORDER BY customer_id, order_id 
              OFFSET 0 ROWS
              FETCH NEXT 10 ROWS ONLY;
  

• Introduction of the ANSI-SQL SEQUENCE object as described in Aaron Bertrand's Using SEQUENCE. Feature pretty much follows what PostgreSQL serial and sequence object and Oracle sequence. In some cases it will replace the use of IDENTITY AUTO_INCREMENT and in some cases it won't
• Contained databases as described in Aaron Bertrand's SQL Server v.Next (Denali) : Contained Databases. In a nutshell, this means you can have a user without an associated sql server login defined as part of the database and its user account will go along with it when you backup and try to restore elsewhere. The user can log in as if they had a login account and in a contained object (Not in Denali), you will eventually not be allowed to create objects that reference entities in other databases or external objects.

We are still holding out for improved Window functionality which Itzik reiterated Window functionality is relatively weak in SQL Server. At this point Oracle, IBM DB2, and PostgreSQL are still far ahead of SQL Server. I see no mention of those in the books online documentation, but I still hope we will see it in later CTPs.