Pro Spatial with SQL Server 2012

Pro Spatial with SQL Server 2012

Released May 2012
Publisher(s): Apress
ISBN: 9781430234913

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

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

    Product information

    • Title: Pro Spatial with SQL Server 2012
    • Author(s):
    • Release date: May 2012
    • Publisher(s): Apress
    • ISBN: 9781430234913