Safari, the world’s most comprehensive technology and business learning platform.

Find the exact information you need to solve a problem on the fly, or go deeper to master the technologies and skills you need to succeed

Start Free Trial

No credit card required

O'Reilly logo
Pro Spatial with SQL Server 2012

Book Description

Microsoft SQL Server implements extensive support for location-based data. Pro Spatial with SQL Server 2012 introduces SQL Server's spatial feature set, and covers everything you'll need to know to store, manipulate, and analyze information about the physical location of objects in space. You'll learn about the geography and geometry datatypes, and how to apply them in practical situations involving the spatial relationships of people, places, and things on Earth.

Author Alastair Aitchison first introduces you to SQL Server's spatial feature set and the fundamental concepts involved in working with spatial data, including spatial references and co-ordinate systems. You'll learn to query, analyze, and interpret spatial data using tools such as Bing Maps and SQL Server Reporting Services. Throughout, you'll find helpful code examples that you can adopt and extend as a basis for your own projects.

  • Explains spatial concepts from the ground up—no prior knowledge is necessary

  • Provides comprehensive guidance for every stage of working with spatial data, from importing through cleansing and storing, to querying, and finally for retrieval and display of spatial data in an application layer

  • Brilliantly illustrated with code examples that run in SQL Server 2012, that you can adapt and use as the basis for your own projects.

Table of Contents

  1. Title
  2. Dedication
  3. Contents at a Glance
  4. Contents
  5. Foreword
  6. About the Author
  7. About the Technical Reviewer
  8. Acknowledgments
  9. Introduction
  10. Chapter 1: Spatial Reference Systems
    1. What Is a Spatial Reference System?
    2. Modeling the Earth
    3. Geographic Coordinate Systems
    4. Projected Coordinate Systems
    5. Putting It All Together: Components of a Spatial Reference System
    6. Spatial Reference Identifiers (SRIDs)
    7. Well-Known Text of a Spatial Reference System
    8. Contrasting a Geographic and a Projected Spatial Reference
    9. Summary
  11. Chapter 2: Spatial Features
    1. Geometry Hierarchy
    2. Interiors, Exteriors, and Boundaries
    3. Points
    4. LineStrings
    5. CircularStrings
    6. CompoundCurves
    7. Polygons
    8. CurvePolygons
    9. MultiPoints
    10. MultiLineStrings
    11. MultiPolygons
    12. GeometryCollections
    13. FullGlobe
    14. Empty Geometries
    15. Choosing the Correct Geometry
    16. Summary
  12. Chapter 3: Spatial Datatypes
    1. SQLCLR Foundations
    2. The geography Datatype
    3. The geometry Datatype
    4. Comparing geography and geometry Datatypes
    5. Storage Requirements
    6. Internal Data Structure
    7. Converting Between Datatypes
    8. Creating Spatially Enabled Tables
    9. Enforcing a Common SRID
    10. Summary
  13. Chapter 4: Creating Spatial Data
    1. Creating Spatial Data from Well-Known Text
    2. Creating Spatial Data from Well-Known Binary
    3. Creating Spatial Data from Geometry Markup Language
    4. Creating Spatial Data from Dynamically Generated WKT
    5. Creating Spatial Data Using the Builder Classes
    6. Summary
  14. Chapter 5: Importing Spatial Data
    1. Sources of Spatial Data
    2. Importing Tabular Spatial Data
    3. Importing Data Using OGR2OGR
    4. Importing ESRI Shapefile Data
    5. Importing MapInfo Data
    6. Reprojecting Data During Import
    7. Exporting Spatial Data from SQL Server
    8. Spatial ETL Using SQL Server Integration Services
    9. Summary
  15. Chapter 6: Geocoding
    1. The Bing Maps Geocoding Service
    2. Creating a .NET Geocoding Assembly
    3. Creating a Geocoding UDF Wrapper
    4. Compiling the Assembly
    5. Configuring the Database
    6. Importing the Assembly
    7. Creating the Geocode Function
    8. Using the Geocode Function
    9. Creating a Geocoding TVF Wrapper
    10. Asynchronous and Batch Geocoding
    11. Reverse Geocoding
    12. Summary
  16. Chapter 7: Precision, Validity, and Errors
    1. Precision
    2. Validity
    3. Handling Errors
    4. Summary
  17. Chapter 8: Transformation and Reprojection
    1. Datum Transformation
    2. Transformation Algorithms
    3. Transforming to and from WGS84
    4. Projection, Unprojection, and Reprojection
    5. Performing Manual Calculations
    6. Creating a Reusable Spatial Conversion Library
    7. Registering the Transformation Assembly and Functions
    8. Transforming Spatial Data in T-SQL
    9. Architecture and Design Considerations
    10. Summary
  18. Chapter 9: Examining Spatial Properties
    1. Property and Method Syntax—A Reminder
    2. Examining the Type of Geometry Used to Represent a Feature
    3. Testing for OGC Properties
    4. Counting the Number of Points in a Geometry
    5. Returning Individual Points from a Geometry
    6. Determining the Center of a Geometry
    7. Returning Coordinate Values
    8. Properties Describing the Extent of a Geometry
    9. Properties Related to Surfaces
    10. Properties Related to GeometryCollections
    11. Calculating Metrics
    12. Setting or Retrieving the Spatial Reference Identifier (SRID)
    13. Summary
  19. Chapter 10: Modification and Simplification
    1. Simplifying a Geometry
    2. Converting Curves to Lines
    3. Reorienting a geography Polygon
    4. Densification
    5. Buffering
    6. Creating the Convex Hull of a Geometry
    7. Summary
  20. Chapter 11: Aggregation and Combination
    1. Creating a Union of Two Items of Spatial Data
    2. Appending One Geometry onto Another
    3. Subtracting One Geometry from Another
    4. Determining the Unique Parts of Two Geometries
    5. Defining the Intersection Between Two Geometries
    6. Aggregating Columns of Spatial Data
    7. Combining Spatial Result Sets
    8. Joining Tables Using a Spatial Column
    9. Summary
  21. Chapter 12: Testing Spatial Relationships
    1. Calculating the Distance Between Two Geometries
    2. Calculating the Shortest Path Between Two Geometries
    3. Testing for Intersection
    4. Identifying Specific Types of Intersection
    5. Defining Custom Relationships Between Geometries
    6. Summary
  22. Chapter 13: Clustering and Distribution Analysis
    1. SQL Server's Spatial Histogram Procedures
    2. Creating a Customized Distribution Analysis
    3. k-Means Clustering
    4. Summary
  23. Chapter 14: Route Finding
    1. Graph Theory
    2. Modeling a Street Network
    3. Brute-Force Routing in T-SQL
    4. A*: A More Efficient Routing Algorithm?
    5. The Traveling Salesman Problem
    6. Harnessing the Bing Maps Routing Service
    7. Summary
  24. Chapter 15: Triangulation and Tesselation
    1. The Importance of Triangles
    2. Triangulation
    3. An SQLCLR Triangulation Procedure
    4. Creating 3D Surface Models
    5. Creating Alpha Shapes (Concave Hulls)
    6. Voronoi Tessellations
    7. Summary
  25. Chapter 16: Visualization and User Interface
    1. The SSMS Spatial Results Tab
    2. Thematic Mapping with Google Earth
    3. Creating a Web Map Interface with Bing Maps
    4. Summary
  26. Chapter 17: Reporting Services
    1. Creating a Simple Report Map
    2. Customizing the Map Viewport
    3. Adding an Analytic Dataset
    4. Applying a Styling Rule
    5. Adding a Bing Maps Tile Layer
    6. Assigning Actions to Map Elements
    7. Limitations of the SSRS Map Control
    8. Summary
  27. Chapter 18: Indexing
    1. The Need for a Spatial Index
    2. How Does a Spatial Index Work?
    3. Creating a Spatial Index in T-SQL
    4. Analysing How the Index Is Used
    5. Creating a geography Index
    6. Designing Queries to Use a Spatial Index
    7. Optimizing an Index
    8. Performance Comparison
    9. Summary
  28. Appendix
  29. Index