Spatial Aggregates in SQL Server Denali


 

SQL Server Code-named “Denali” is currently available as a Community Technology Preview (CTP).  Denali introduces 4 new aggregates to the the suite of spatial operators in SQL Server:

  • Union Aggregate
  • Envelope Aggregate
  • Collection Aggregate
  • Convex Hull Aggregate

All aggregates are implemented as static methods which work for either the Geography or the Geometry data types. As an example, consider the following geography aggregate:

Geography::UnionAggregate(geog)

and its counterpart for geometry:

Geometry::UnionAggregate(geom)

The following introduces each aggregate with a short definition, a code sample and a visual illustrating what goes into the aggregate and what is returned. While aggregates are applicable to all classes of spatial data (points, linestrings, etc.) they are most effectively described and illustrated with polygons, which are used in the following discussion.

Union Aggregate

Definition: Combines multiple spatial objects into a single spatial object, removing interior boundaries, where applicable.

SELECT Geography::UnionAggregate(geog)
 
FROM Counties
   
WHERE name_1 = ‘Washington’;

image

Envelope Aggregate

The unlike other aggregates, the Envelope Aggregate exhibits different behavior depending on whether is is applied to a geography or a geometry type.  For the geometry type, a “traditional” rectangular polygon is calculated which closely bounds the selected input objects.  For the geography type, a circular object is calculated which loosely bounds the selected input objects.  Furthermore, the circular object is defined using the new CurvePolygon feature.

Envelope Aggregate for the Geometry Type

Definition: Returns a bounding rectangular polygon object, which encloses one or more spatial objects.

SELECT Geometry::EnvelopeAggregate(geom)
  FROM Counties
    WHERE name_1 = ‘Washington’;

image

Envelope Aggregate for the Geography Type

Definition: Returns a bounding circular object as a CurvePolygon which encloses one or more spatial objects.

SELECT Geography::EnvelopeAggregate(geog).STCurveToLine()
 
FROM Counties
   
WHERE name_1 = ‘Washington’;

Note the STCurveToLine() method in the sample code, above.  This new method is used to output spatial objects in a manner which older visualization systems can digest.

image

Collection Aggregate

Definition: Returns a geometry collection with one geometry part for each spatial object(s) in the selection set.

SELECT Geography::CollectionAggregate(geog)
  FROM Counties
    WHERE name_1 = ‘Washington’;

image

 

Convex Hull Aggregate

Definition: Returns a convex hull polygon which encloses one or more spatial objects.

SELECT Geography::ConvexHullAggregate(geog)
  FROM Counties
    WHERE name_1 = ‘Washington’;

image

 


Comments (6)

  1. PK says:

    Yah! Finally these are getting into SqlServer instead of using the codeplex tools. Love the CollectionAggregate also – very kewl!

    Lets hope the hemisphere issue is now resolved and we can get some better performance when doing lots of things like Point in Poly (STIntersects()) or STIntersection() calculations.

    Bring on Denali!!

    -PK (using Sql Spatial since the beta's..)

  2. tanoshimi says:

    @Ed – thanks for the article. Any indication when the general public will get a Denali CTP release with the SSMS Spatial Results tab back, so that we can visualise these aggregates ourselves? (I was surprised how much I miss it – I think you'd get more people testing the new Denali spatial features such as aggregates if they could see what was going on. Tabular results just don't cut it any more…;)

    @PK – the "hemisphere issue" is no more. For example, in Denali CTP1, you can declare a FULLGLOBE geography, use STDifference() to take away A UnionAggregate of all the countries of the world, and what you'll be left with is the entire global ocean surface as a single geography instance, no problems…

    One thing to watch out for – although the "within a hemisphere" rule was a technical limitation, it actually had some beneficial side-effects: namely, it provided a safeguard against *accidentally* creating geography polygons larger than a hemisphere. Since the majority of spatial objects occupy less than a hemisphere, if you had incorrect ring orientation in SQL 2008 you'd get the familiar 24205 exception "Does not represent a valid geography instance because it exceeds a hemisphere…". In Denali, because this restriction has been lifted, if you try to create geography data with the incorrect ring orientation it will work just fine, but what you'll end up with is an "inside-out" polygon – excluding the area it was meant to include, and including the whole of the rest of the surface of the earth. Just something to watch out for…..

  3. Ed Katibah says:

    @tanoshimi – The Spatial results tab will return in CTP3.  I'm currently using an ill-advised hack – I've installed Denali CTP1 side-by-sdie with R2.  This brought back the Spatial results tab but has wacked a bunch of other useful SSMS features – such as backup and restore, among other equally useful features.  This has actually been quite beneficial in that it has forced me to learn how to perform the missing features with straight T-SQL.

    Regarding FULLGLOBE.  Yes, this is an excellent point.  Many shapefiles, for instance, orient polygon exterior rings using the right-foot rule.  If not modified during import, this will create geography polygons which are, as you say, "inside-out".  While I should have known better, this situation paused me for about 15-minutes one evening until I finally figured out what was going on.  And remember, you can now load invalid geography data into Denali.  Consequently, I now run a variant of the following T-SQL against freshly imported polygons as a standard part of my workflow (I generally review invalid data before validating the lot, whole-sale.  Likewise for polygon ring orientation):

    ———————————————————————-

    — Fix invalid objects

    ———————————————————————-

    UPDATE <your table>

     SET geog = geog.MakeValid()

       WHERE geog.STIsValid() = 0;

    GO

    ———————————————————————-

    — Reorient large polygons to small polygons

    ———————————————————————-

    UPDATE <your table>

     SET geog = geog.ReorientObject()

       WHERE geog.EnvelopeAngle() = 180;

    GO

  4. tanoshimi says:

    It might not be obvious from the screenshot above, but the CollectionAggregate() *always* returns a Geometry Collection.

    If you call CollectionAggregate() on a column containing only Points, for example, you'll get a Geometry Collection of Points, not a MultiPoint. Likewise, if you call it on a table containing only one LineString, say, you'll get a Geometry Collection with one LineString in it, not the LineString itself.

    This surprised me, as it does not match the behaviour of some other methods that seek to return the simplest geometry type capable of representing a given point set.

  5. A.M. Robinson says:

    Are there any other places someone can find documentation/explanations of these aggregates? I have never worked with spatial data ever, and for the 70-461 certification tests there are several questions on this exact topic, but I can find no mention of these anywhere in BOL!

    I can find zero mention in all the exam preps I've looked at.

    Some explanations somewhere in just "plain old English" would be great…maybe some examples?

    Thank you!

  6. Jake says:

    I found this article extremely useful while studying for MCSA sql server exams. Thanks!!!