With SQL2008 Spatial functions, How can I generate a polygon containing the results of an aggreation

Question

I've got a table of points, each with a name, latitude, longitude, and an area code. What I'd like to do is perform an aggregation (i.e. a "group by" clause) and return a polygon containing all the points for a particular area code.

I'm struggling a little to even find if there are any built in aggregations for the geography data types though, let alone to give me the bounding polygon

For the sake of argument, assume I've a table that looks a little like:

+---------+------+---------+---------+
| Name    | Area | Lat     | Long    |
+---------+------+---------+---------+
| Ipswich | A    | 52.053  |  1.156  |
| Luton   | A    | 51.8717 | -0.4246 |
| Melton  | A    | 52.1064 |  1.3322 |
| Didcot  | B    | 51.6024 | -1.2321 |
| Oxford  | B    | 51.7486 | -1.265  |
+---------+------+---------+---------+

(In reality, the smallest area has 57 points, and the largest 626)

Answer 1

While there is no built-in way to do this, there are Aggregates available as part of the Sql Spatial Tools CodePlex package. Depending on exactly what you want, you may be interested in using either the Bounding Box Aggregate or the Union Aggregate followed by a call to ConvexHull to get the minimum polygon instead of a box.