Spatial Data (OLTP)---a Technical Reference Guide for Large Enterprise Solutions for Designing Mission-Critical OLTP Solutions

  • Article

Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.

Microsoft SQL Server Spatial data storage and manipulation support consists of two data types, numerous methods and spatial indexes. This support is implemented via common language runtime (CLR) user-defined types (UDTs).

The two spatial data types, geometry and geography, support two-dimensional vector data representing features defined as linear objects. Collections on these objects are also supported. The underlying object model adds support for two additional dimensions, typically representing height (Z) and measure (M) values. Geometry objects can represent anything that can be defined by X and Y planar coordinates, including projected Earth features. Geography objects most commonly represent objects on the Earth, such as points-of-interest, addresses, street centerlines, country/regional boundaries, and other geographical information.

SQL Server Spatial data supports international standards as defined by the Open Geospatial Consortium (OGC) Simple Features for SQL Version 1.1 (OGCSFS1.1) and International Organization for Standardization (ISO) 19125. Some features from ISO SQL Multimedia, Part Three, are also supported. The geometry data type is fully compliant with OGCSFS1.1 and ISO 19125. The geography data type implements near full support for these standards, but cannot fully comply because these standards do not support the Earth modeled as a closed surface (a recognized fault of these standards' implementations).

Best Practices

The following resources provide information on spatial data support, in addition to general spatial data reference material. (Note that the full URLs for the hyperlinked text are provided in the Appendix at the end of this document.)

Case Studies and References

For information on partners activities and other projects, see the following articles:

For information on selected partners using SQL Server spatial data support in their product line, see the SQL Server 2008 Spatial Partners6 list. For more information on specific partners, see the following:

Questions and Considerations

This section provides questions and issues to consider when working with spatial data.

  • The data sheet SQL Server 2008 Spatial Data11 can help you gain very elementary understanding of coordinate systems. For a more advanced understanding, refer to the white paper Delivering Location Intelligence.12

  • Spatial data should be normalized into a common coordinate system prior to loading into SQL Server. Extract, Transform, Load (ETL) tools, such as the Safe Software Feature Manipulation Engine (FME), can be used as stand-alone Windows software or as plug-ins to SQL Server Integration Services (see Safe Software – FME link in the Sample Case Studies/References section of this guide for more information) to accomplish this task. Note that there is no spatial data support in SQL Server Integration Services except through partners. The SQL Server Import and Export Wizard do not support spatial data.

  • SQL Server spatial is supported by many geographic information system (GIS) software providers.

  • Data loaders for common spatial data formats are provided by partners.

  • Spatial indexes are commonly used but can tricky to configure. Index hints are often required. Spatial indexing requires a primary key on all tables.

  • A redistributable spatial library is exposed and can be used to extend spatial data support. This library can also be used in Windows Azure. Silverlight support is not currently available. The redistributable library is shipped as part of the SQL Server 2008 R2 Feature Pack.13

Appendix

Following are the full URLs for the hyperlinked text.

1 Beginning Spatial with SQL Server 2008http://www.amazon.com/Beginning-Spatial-SQL-Server-2008/dp/1430218290/ref=sr_1_1?ie=UTF8&qid=1299654540&sr=8-1

2 SQL Server Spatial Bloghttps://blogs.msdn.com/b/edkatibah/

3 Inside Microsoft SQL Server 2008: T-SQL Programminghttps://www.microsoft.com/learning/en/us/Book.aspx?ID=12805&locale=en-us

4 ESRI Announces Support for the Spatial Enhancements of SQL Server 2008https://download.microsoft.com/download/9/5/6/956d5568-395f-4217-a0ed-efad8fc995f3/ESRI%20final.doc

5 Implementing a Microsoft SQL Server 2008 R2 Databasehttps://www.microsoft.com/learning/en/us/Course.aspx?ID=6232B&Locale=en-us#tab1

6 SQL Server 2008 Spatial Partners listwww.microsoft.com/sqlserver/2008/en/us/spatial-partners.aspx

7 Microsoft Esri overviewhttp://www.esri.com/partners/alliances/microsoft/index.html

8 More format interoperability and data transformation capabilities for SQL Serverhttp://www.safe.com/solutions/for-products/microsoft-sql-server/

9 MapInfo Professional overviewhttp://www.pbinsight.com/products/location-intelligence/applications/mapping-analytical/mapinfo-professional/

10 Map Data for Microsoft SQL Developershttp://www.nn4d.com/site/global/home/MicrosoftSQL2008.jsp

11 SQL Server 2008 Spatial Data Datasheethttps://download.microsoft.com/download/c/8/4/c8470f54-d6d2-423d-8e5b-95ca4a90149a/SQLServer2008_SpatialData_Datasheet.pdf

12 Delivering Location Intelligence with Spatial Datahttps://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-spatial-data.aspx

13 SQL Server 2008 R2 Feature Packhttps://www.microsoft.com/downloads/en/details.aspx?FamilyID=ceb4346f-657f-4d28-83f5-aae0c5c83d52&displaylang=en