Repost/Revision Updated serialization formats for SQL Server spatial types

SQL Server Denali CTP1 was released today. I don’t want to be the fiftieth person to post the link to the download; reference Ed's blog posting for the location.

This is a repost (with some corrections now that the CTP is actually out) of a blog posting I did when someone discovered a web location of an updated serialization spec. It attempted to give an overview of where the new spatial types (like circular string and curves) came from and what they mean. I took down the blog posting when the spec "disappeared". But now that Ed Katibah blogged about it (be sure to read his excellent whitepaper), I guess it’s *really* OK now.

Everyone's on the lookout for info about features of SQL Server Denali. The docs folks may have given us some insight in the publication of an update of the "Microsoft SQL Server CLR Types Serialization Formats [MS-SSCLRT]" spec. This includes updates that "apply to SQL Server Denali CTP1" according to footnotes in the spec. A message about this spec update was posted on the Technet SQL Server spatial forum. The spec is publically posted.

Besides a bit indicating "this geography instance is bigger than half a hemisphere", there's four new types listed under OpenGIS Types: CircularString, CompoundCurve, CurvePolygon, and FullGlobe. The 1.2 versions of the OGC specs mention the first three. No mention of FullGlobe, that must technically not be a new type. So what are the three remaining types and what are they good for? That's more interesting than just knowing they exist. The best information I could find comes from the ISO-ANSI SQL/MM Part 3 spec.

In OGC's original type hierarchy (I couldn't find an updated one in their new specs, maybe I didn't look hard enough), LineString is the only direct subtype of Curve and Line and LinearRing derive from LineString. In SQL/MM's type hierarchy LineString, CircularString, and CompoundCurve all directly derive from Curve (an abstract type). So both CircularString and CompoundCurve "is a" Curve, just as LineString is. There is no Line and LinearRing in SQL/MM spec. In a somewhat similar fashion, Surface is declared as abstract type, CurvePolygon is the direct subtype, and Polygon is a subtype of CurvePolygon.

Back to LineString vs. CircularString vs CompoundCurve. A single segment of a LineString consists of two points connected by a straight line. But a single segment of a CircularString consists of three points that form an arc: start point, end point, and any point on the arc in between. You can have multiple segments; the end point of one segment can be the start point of the next segment. "A combination of linear and circular strings can be modeled using the ST_CompoundCurve type." (quote from the spec). Ah…so it would seem to be less work to define an arc as three points than approximate an arc with many small linestrings. More accurate too. That's (possibly) the goodness.

For CurvePolygon, to relate it to Polygon (which we all know and love) and Surface (the abstract class), from the spec: "Surfaces, as 2-dimensional geometries, are defined in the same way as curves using a sequence of points. The boundary of each surface is a curve, or a set of curves if the surface has any holes in it. The boundary of a surface consists of a set of rings, where each ring is a curve. The type ST_CurvePolygon stands for such a generalized surface, and the subtype ST_Polygon restricts the conditions for the rings of the boundary to linear strings."

Finnaly there was a question I just had to find the answer to: how then do you define a Circle? According to the new CTP1 docs, you need to define a circle in halves. So something like: declare @g geometry = 'COMPOUNDCURVE(CIRCULARSTRING(0 2, 2 0, 4 2), CIRCULARSTRING(4 2, 2 4, 0 2))'; might do the trick.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.