New Spatial Features in the SQL Azure Q2 2011 Service Release


 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.

New Spatial Features in the SQL Azure Q2 2011 Service Release

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:

  1. 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.
  2. Features that are dependent on the new enhancements to the spatial data platform presented in SQL Server Code-Name “Denali”, 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 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()

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

No5

ConvexHullAggregate()

ConvexHullAggregate()

100

No5

EnvelopeAggregate()

EnvelopeAggregate()

100

No5

UnionAggregate()

UnionAggregate()

100

No5

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.

Geography Type

Geometry Type

Minimum DB Compat

Level

SQL Azure Q2SR 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

No2

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 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.

Notes:

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.