Spatial Data (OLTP)---a Technical Reference Guide for Large Enterprise Solutions for Designing Mission-Critical OLTP Solutions
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.)
Customers have found the book Beginning Spatial with SQL Server 20081 very useful. They find that it is good not only for the beginners but also for advanced usage and best practices.
The SQL Server Spatial Blog2 has many useful articles on spatial data.
Chapter 14 of Inside Microsoft SQL Server 2008: T-SQL Programming3 provides significant content for understanding and using spatial data.
Case Studies and References
For information on partners activities and other projects, see the following articles:
See the press release ESRI Announces Support for the Spatial Enhancements of SQL Server 20084 for information about the support for Esri’s support for SQL Server .
The course Implementing a Microsoft SQL Server 2008 R2 Database5 includes education and training for SQL Server Spatial Data support.
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:
For more information about Esri, see Esri Corporate Alliance Partner: Overview.7
For more information about Safe Software - FME, see More format interoperability and data transformation capabilities for SQL Server.8
For more information about Pitney Bowes MapInfo, see MapInfo Professional Overview.9
For more information about NAVTEQ, see Map Data for Microsoft SQL Developers.10
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