This post has been superseded by the newer TechNet Wiki post: Updated Spatial Features in SQL Azure Q4 2011 Service Release
SQL Azure Technical Article
Writer: Ed Katibah (Microsoft)
Technical Reviewers: Milan Stojic (Microsoft) and Bob Beauchemin (SQLskills)
Published: October 25, 2011
Applies to: SQL Azure Q2 2011 Service Release
Summary: This release of SQL Azure introduces the updated spatial data platform created in SQL Server Code Name “Denali”. The SQL Server Code Name “Denali” spatial features are expected to be released in SQL Azure in increments, beginning with this service release. This paper documents the status of the new spatial features.
Copyright
This is a preliminary document and may be changed substantially prior to final commercial release of the software described herein.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This white paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED, OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in, or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred.
© October 2011 Microsoft Corporation. All rights reserved.
Microsoft, SQL Azure, and SQL Server are trademarks of the Microsoft group of companies.
All other trademarks are property of their respective owners.
The Microsoft SQL Azure Q2 2011 Service Release (SR) is significant in that it represents the initial step towards providing a common base and feature set between the SQL Azure service and the upcoming release of SQL Server Code Name “Denali”, now known officially as SQL Server 2012. SQL Server Code Name “Denali” (currently available as Community Technology Preview 3) introduces a number of new spatial features. The SQL Azure Q2 2011 SR exposes a subset of the new “Denali” spatial features and is the subject of this note. A white paper covering all of the new spatial features in SQL Server “Denali” is available SQLCAT.com. You may also view this document from the Microsoft TechNet Wiki. You can use these references to explore the new spatial features presented in this document in more detail.
You can verify that your SQL Azure instance is the Q2 2011 Service Release by checking the version in SQL Server Management Studio:
SELECT @@version --Returns: Microsoft SQL Azure (RTM) - 11.0.1467.26
If your SQL Azure instance has a major version of 11, you are using the Q2 2011 SR.
Spatial features surfaced in SQL Server Code-Name “Denali” come in two varieties:
In this service release, only those spatial features with a minimum database compatibility level of 100 are made available. Spatial features with a 110 designation are scheduled to make their appearance in a future SQL Azure release.
Table 1 lists the new spatial methods released in the SQL Azure Q2 2011 SR. Additional spatial methods that are expected to be made available in a future SQL Azure release are also included.
Geography Type
Geometry Type
Minimum DB Compat Level
SQL Azure Q2SR Compatibility
OGC Methods
STContains()
Already supported in SQL Azure
100
Yes
STConvexHull()
STCurveN()
110
No1
STCurveToLine()
STIsValid()
STNumCurves()
STOverlaps()
STWithin()
Extended Methods
AsBinaryZM()
Yes2
BufferWithCurves()
CurveToLineWithTolerance()
HasM
HasZ
IsValidDetailed()
Yes3
MakeValid()4.1
Yes4
MinDbCompatibilityLevel()4.2
ReorientObject()4.3
Not applicable
ShortestLineTo()
Aggregates (Extended Static Methods)
CollectionAggregate()
No5
ConvexHullAggregate()
EnvelopeAggregate()
UnionAggregate()
Table 1. Spatial methods introduced in SQL Server Code-Name “Denali”. Methods that make their debut in this release of SQL Azure are shown in green. Methods that are staged for presentation in a future release of SQL Azure are shown in tan/orange color. Methods with red in the SQL Azure Q2SR Support column were intended to be available but are not operational in this release.
Notes:
1Microsoft plans to support these methods in a future SQL Azure update.
2The OGC WKB constructor methods - STGeomCollFromWKB(), STGeomFromWKB(), STLineFromWKB(), STPointFromWKB(), and STPolyFromWKB() - have been updated to accept Z and M from Well-known Binary input, including Well-known Binary created by the new AsBinaryZM method.
3Microsoft plans to expose the full utility of this method for the Geography type when the remainder of the Denali spatial features are released in a future SQL Azure update. This method is fully functional in this release of SQL Azure for the Geometry type.
4Although these methods execute, their full utility is not expected to be exposed until the remainder of the Denali spatial features are released in a future SQL Azure update.
4.1In this release of SQL Azure, invalid objects are not allowed in the Geography type. Consequently, the MakeValid() method, while present and operational for the Geography type, cannot do anything more than operate over an already valid object – effectively a no-op.
4.2In this release of SQL Azure, all spatial methods are restricted to minimum database compatibility level 100. Hence, the MinDbCompatibilityLevel() method always returns 100.
4.3In this release of SQL Azure, Geography polygons must occupy less than a logical hemisphere in extent. This is the same restriction under which Geography polygons operate in SQL Server 2008 and SQL Server 2008 R2. If the ReorientObject() method is applied to a Geography polygon (or multipolygon) in this release of SQL Azure, it returns NULL, because the only other option would be to return an invalid object.
5Spatial aggregates are exposed in SQL Azure Q2SR but fail to execute properly due to an internal permissions issue. Spatial aggregates are expected to work as designed in a future SQL Azure release. Here is the error message returned when a spatial aggregate is executed:
A severe error occurred on the current command. The results, if any, should be discarded.
Because no results are returned, there is no reason for concern with the error.
Additional spatial features from SQL Server Code-Name “Denali” make their debut in SQL Azure Q2 2011 SR. These new spatial features are presented in Table 2. Table 2 also lists several spatial features that were supposed to be present in this release but were not included.
Minimum DB Compat
Level
SQL Azure Q2SR Support
Type Class
FullGlobe
CircularString
CompoundCurve
CurvePolygon
Spatial Indexes
Auto Grid spatial index
SPATIAL_WINDOW_MAX_CELLS hint
Compression for spatial indexes
Nearest Neighbor query plan
Create Table DDL
Persisted computed spatial columns
Spatial Reference System
New SRID for Unit Sphere: 104001
sys.spatial_reference_systems view
No2
Stored Procedures
sp_help_spatial_geography_histogram
sp_help_spatial_geometry_histogram
sp_help_spatial_geography_index
sp_help_spatial_geometry_index
sp_help_spatial_geography_index_xml
sp_help_spatial_geometry_index_xml
Table 2. New and existing spatial features and their status in the SQL Azure Q2 SR 2011 release. Features that make their debut in this release of SQL Azure are shown in green. Features that are staged for presentation in a future release of SQL Azure are shown in tan/orange color. Features with red in the SQL Azure Q2SR Support column were intended to be available but are not present in this release.
1These features are expected to be supported in a future SQL Azure update.
2These features are supposed to be available in Q2 2011 SR but have been inadvertently excluded. These features are expected to be available in an upcoming SQL Azure release.