Updated Spatial Features in the SQL Azure Q4 2011 Service Release
Updated Spatial Features in the SQL Azure Q4 2011 Service Release
SQL Azure Technical Article
Writer: Ed Katibah (Microsoft)
Technical Reviewers: Milan Stojic (Microsoft) and Bob Beauchemin (SQLskills)
Published: December 13, 2011
Applies to: SQL Azure Q4 2011 Service Release
Summary: This release of SQL Azure updates some of the new spatial features in SQL Server 2012. The SQL Server 2012 spatial features are being released in SQL Azure in increments, beginning with the Q2 2011 Service Release. This paper documents the status of the spatial features updated in the SQL Azure Q4 2011 Service Release.
Note: This post supersedes the earlier Microsoft TechNet Wiki post: New Spatial Features in the SQL Azure Q2 2011 Service Release.
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.
© December 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.
Updated Spatial Features in the SQL Azure Q4 2011 Service Release
The Microsoft SQL Azure Q2 2011 Service Release (SR) was 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 2012 (currently available as RC0) introduces a number of new spatial features. The SQL Azure Q2 2011 SR exposed a subset of the new SQL Server 2012 spatial features and the Q4 2011 Service Release continues with this theme, releasing several more. A white paper covering all of the new spatial features in SQL Server 2012 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 Q4 2011 Service Release by checking the version in SQL Server Management Studio:
SELECT @@version
--Returns: Microsoft SQL Azure (RTM) - 11.0.1814.30 Nov 21 2011 16:46:09
If your SQL Azure instance has a version of 11.0.1814.30, you are using the Q4 2011 SR.
Spatial features surfaced in SQL Server 2012 come in two varieties:
- Features that are compatible with the previous version of SQL Server (SQL Server 2008 and SQL Server 2008 R2), which are designated by a value of 100 as the minimum database compatibility level.
- Features that are dependent on the new enhancements to the spatial data platform presented in SQL Server 2012, which are designated by a value of 110 as the minimum database compatibility level.
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 Service Release.
Summary of Spatial Features Updated in the Q4 2011 Service Release
Spatial aggregates (CollectionAggregate(), ConvexHullAggregate(), UnionAggregate()), and geometry::EnvelopeAggregate() now work correctly. Geography::EnvelopeAggregate does not work because it returns a circular subtype which is not yet supported in SQL Azure (see below for more details). Additionally, the system view, sys.spatial_reference_systems, previously missing from SQL Azure, is now available.
Table 1 lists the new spatial methods released in SQL Azure beginning with 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 Q4SR Compatibility |
OGC Methods |
|||
STContains() |
Already supported in SQL Azure |
100 |
Yes |
STConvexHull() |
Already supported in SQL Azure |
100 |
Yes |
STCurveN() |
STCurveN() |
110 |
No1 |
STCurveToLine() |
STCurveToLine() |
110 |
No1 |
STIsValid() |
Already supported in SQL Azure |
100 |
Yes |
STNumCurves() |
STNumCurves() |
110 |
No1 |
STOverlaps() |
Already supported in SQL Azure |
100 |
Yes |
STWithin() |
Already supported in SQL Azure |
100 |
Yes |
Extended Methods |
|||
AsBinaryZM() |
AsBinaryZM() |
100 |
Yes2 |
BufferWithCurves() |
BufferWithCurves() |
110 |
No1 |
CurveToLineWithTolerance() |
CurveToLineWithTolerance() |
110 |
No1 |
HasM |
HasM |
100 |
Yes |
HasZ |
HasZ |
100 |
Yes |
IsValidDetailed() |
IsValidDetailed() |
100 |
Yes3 |
MakeValid()4.1 |
Already supported in SQL Azure |
100 |
Yes4 |
MinDbCompatibilityLevel()4.2 |
MinDbCompatibilityLevel()4.2 |
100 |
Yes4 |
ReorientObject()4.3 |
Not applicable |
100 |
Yes4 |
ShortestLineTo() |
ShortestLineTo() |
100 |
Yes |
Aggregates (Extended Static Methods) |
|||
CollectionAggregate() |
CollectionAggregate() |
100 |
Yes |
ConvexHullAggregate() |
ConvexHullAggregate() |
100 |
Yes |
EnvelopeAggregate() |
EnvelopeAggregate() |
100 |
Yes5 |
UnionAggregate() |
UnionAggregate() |
100 |
Yes |
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. The method in red (EnvelopeAggregate()) for the Geography type) was intended to be available but is 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.
5The EnvelopeAggregrate() method for the Geography type executes but fails since the returned spatial object contains an unsupported class of spatial data. Here is the error message returned:
The specified operation cannot run under the current compatibility level. A common reason for this issue is that object contains circular arcs. Change the database compatibility level to 110 or higher, or use STCurveToLine.
Since SQL Azure is not yet equivalent to compatibility level 110, this method cannot operate properly in the current service release.
Additional spatial features from SQL Server 2012 made 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.
Geography Type |
Geometry Type |
Minimum DB Compat Level |
SQL Azure Q4SR Support |
Type Class |
|||
FullGlobe |
Not applicable |
110 |
No1 |
CircularString |
CircularString |
110 |
No1 |
CompoundCurve |
CompoundCurve |
110 |
No1 |
CurvePolygon |
CurvePolygon |
110 |
No1 |
Spatial Indexes |
|||
Auto Grid spatial index |
Auto Grid spatial index |
110 |
No1 |
SPATIAL_WINDOW_MAX_CELLS hint |
SPATIAL_WINDOW_MAX_CELLS hint |
110 |
No1 |
Compression for spatial indexes |
Compression for spatial indexes |
110 |
No1 |
Nearest Neighbor query plan |
Nearest Neighbor query plan |
100 |
Yes |
Create Table DDL |
|||
Persisted computed spatial columns |
Persisted computed spatial columns |
100 |
Yes |
Spatial Reference System |
|||
New SRID for Unit Sphere: 104001 |
Not applicable |
100 |
Yes |
sys.spatial_reference_systems view |
Not applicable |
100 |
Yes |
Stored Procedures |
|||
sp_help_spatial_geography_histogram |
sp_help_spatial_geography_histogram |
100 |
No2 |
sp_help_spatial_geometry_histogram |
sp_help_spatial_geometry_histogram |
100 |
No2 |
sp_help_spatial_geography_index |
sp_help_spatial_geography_index |
100 |
No2 |
sp_help_spatial_geometry_index |
sp_help_spatial_geometry_index |
100 |
No2 |
sp_help_spatial_geography_index_xml |
sp_help_spatial_geography_index_xml |
100 |
No2 |
sp_help_spatial_geometry_index_xml |
sp_help_spatial_geometry_index_xml |
100 |
No2 |
Table 2. New and existing spatial features and their status in the SQL Azure Q4 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 Q4SR Support column were intended to be available but are not present in this release.
Notes:
1These features are expected to be supported in a future SQL Azure update.
2These features were supposed to be available starting with Q2 2011 SR but have been inadvertently excluded. These features are expected to be available in an upcoming SQL Azure Service Release.