Pro Spatial with
SQL Server 2012
Alastair Aitchison
Apress*
ULB DarmstadtIllllllill
17711989Contents
Contents at a Glance v Foreword xxi About the Author xxii About the Technical Reviewer xxiii Acknowledgments xxiv Introduction ...xxv I I Chapter 1: Spatial Reference System 1 What Is a Spatial Reference System? , ....1 Modeling the Earth 2
Approximating the Geoid , 3 Regional Variations in Ellipsoids 4 Realizing a Reference Ellipsoid Model with a Reference Frame 5
Geographic Coordinate Systems ...6
Geographic Units of Measurement .7 Defining the Origin of a Geographic Coordinate System 7
Projected Coordinate Systems 8
Creating Map Projections 8 Hammer-Aitoff Projection 9 Mercator Projection : 10 Equirectangular Projection 11 Universal Transverse Mercator Projection 12 Projection Parameters 13 Projected Units of Measurement 14
Putting It All Together: Components of a Spatial Reference System 15 Spatial Reference Identifiers (SRIDs) 16 Weil-Known Text of a Spatial Reference System 17 Contrasting a Geographic and a Projected Spatial Reference 18 Summary 19
• CONTENTS
• Chapter 2: Spatial Features 21 Geometry Hierarchy 21 Interiors, Exteriors, and Boundaries 23 Points 24
Example Point Usage 24 Defining a Point 25 Defining Points in 3- and 4-Dimensional Space 26 Characteristics of Points 27
LineStrings 27
Example LineString Usage 27 Defining a LineString 28 Characteristics of LineStrings 29 LineStrings and Self-Intersection 30
CircularStrings 30
Example CircularString Usage ;rr. ...30
Defining a CircularString „ 31 Characteristics of CircularStrings 33 Drawing Complete Circles 33 Choosing Between LineString and CircularString .34
CompoundCurves 34
Example CompundCurve Usage 35 Defining a CompoundCurve 35 Characteristics of CompoundCurves 36
Polygons.... 36
Example Polygon Usage 36 Exterior and Interior Rings 37 Defining a Polygon 38 Characteristics of Polygons 38
CurvePolygons 39
Example CurvePolygon Usage 39 Defining a CurvePolygon 40 Characteristics of CurvePolygons 41
iCONTENTS
MultiPoints 41
Example Multipoint Usage 41 Defining A Multipoint 42 Characteristics of MultiPoints 43 Many Single Points, or One Multipoint? 43
MultiLineStrings 43
Example MultiLineString Usage 43 Defining a MultiLineString 44 Characteristics of MultiLineStrings 45
MultiPolygons 45
Example MultiPolygon Usage 45 Defining a MultiPolygon 46 Characteristics of MultiPolygons 46
GeometryCollections , 46
Example GeometryCollection Usage ....^ 46 Defining a GeometryCollection 46 Characteristics of Geometry Collections ".. 47
FullGlobe 47
Defining a FullGlobe 47 Characteristics of the FullGlobe geometry are as follows 47
Empty Geometries 48
Defining an Empty Geometry 48 Characteristics of Empty Geometries 49
Choosing the Correct Geometry 49 Summary 49 I I Chapter 3: Spatial Datatypes 51 SQLCLR Foundations 52
Methods and Properties 53 Static Methods 54 Instance Methods 54 Properties 55 Spatial Libraries 56
I CONTENTS
The geography Datatype 57
Spatial Reference Systems for the geography Datatype 58 Correct Ring Orientation for geography Polygons 60
The geometry Datatype 62
Spatial Reference Systems for the geometry Datatype 64 Storing Nongeodetic Data ...65 The Insignificance of Ring Orientation in a geometry Polygon 65
Comparing geography and geometry Datatypes 66
Consistency 66 Accuracy 67 The End(s) of the World 67 Presentation 69 Performance 70 OGC Standards Compliance.... 70 General Rules .-....„. 71
Storage Requirements 71 Internal Data Structure : 72 Converting Between Datatypes 73 Creating Spatially Enabled Tables 74 Enforcing a Common SRID ...75 Summary 76 i f Chapter 4: Creating Spatial Data 77 Creating Spatial Data from Well-Known Text 77
Choosing a WKT Static Method 78 Passing WKT to the Method 78 Retrieving the WKT Representation of an Instance 81
Creating Spatial Data from Well-Known Binary 81
Choosing a WKB Static Method 82 Representing an Existing Geometry as WKB 84
Creating Spatial Data from Geometry Markup Language 85
GeomFromGmlO—The Only GML Static Method 86 The GML Namespace 86 Representing an Existing Geometry as GML 87
S CONTENTS
Creating Spatial Data from Dynamically Generated WKT 88 Creating Spatial Data Using the Builder Classes 91
Configuring a .NET Console Application for Spatial Data 91 Constructing a Simple Point 93 Building Polygons with Multiple Rings 96 Creating Geometry Collections 97 Building Curved Geometries 98 Programmatically Defining Three- and Four-Dimensional Geometries 99
Summary ...99
i Chapter 5: Importing Spatial Data 101
Sources of Spatial Data 101 Importing Tabular Spatial Data 102
Importing the Text File : 105 Creating a Computed Column 106 Populating a Noncomputed Column 107 Comparing Computed, Noncomputed, and Persisted Columns 108
Importing Data Using 0GR20GR 109
Obtaining and Installing the GDAL/OGR Library... 109 0GR20GR—Basic Syntax 110
Importing ESRI Shapefile Data 110
Obtaining Sample Shapefile Data 111 Loading the Data 112 Assigning an Output SRID 113 Another Example: Specifying Layer Creation Options 116
Importing Maplnfo Data 118
Obtaining Sample Maplnfo Data 118 Determining the SRID 119 Loading the Data 120
Reprojecting Data During Import 121 Exporting Spatial Data from SQL Server 122
Keyhole Markup Language 122 Exporting from SQL Server to KML with 0GR20GR 123
!CONTENTS
Spatial ETL Using SQL Server Integration Services 125
Importing Point Data Using a Custom Script Component 125 Creating a New SSIS Project 125 Creating the Text File Connection 125 Creating the SQL Server Connection 127 Adding a New Dataflow Task and Specifying the Data Source 128 Creating the Script Component 128 Specifying the Destination 132 Running the SSIS package 134 Using Third-Party Source Components 135 CodePlex SSIS Shapefile Source 135 SafeFME 135
Summary 136 • Chapter 6: Geocoding 139 The Bing Maps Geocoding Service. ...139
Obtaining a Bing Maps Key ..T. 140 Calling the Bing Maps REST Locations API .-. 141
Creating a .NET Geocoding Assembly 143
Creating a New Project 143 Configuring the Project 144 Adding a Reference to Microsoft.SqlServer.Types.dll 145 Adding a Simple Geocoding Function 146
Creating a Geocoding UDF Wrapper 148 Compiling the Assembly 149 Configuring the Database 150
Enabling CLR Support 150 Setting Security Permissions 150
Importing the Assembly 151 Creating the Geocode Function 152 Using the Geocode Function 153 Creating a Geocoding TVF Wrapper 154 Asynchronous and Batch Geocoding 158
Reverse Geocoding 159
Summary 161
• CONTENTS
i l Chapter 7: Precision, Validity, and Errors 163
Precision 163
Storage Precision 164 Practical Implications of Fixed Binary Precision 165 geometry Precision 165 geography Precision 165 Balancing Precision Against Transmission Size 168 Calculation Precision 171 Precision and Equality 174
Validity 175
Testing Whether a Geometry Is Valid 177 Finding Out Why a Geometry Is Invalid 177 Making an Object Valid 178
Handling Errors 181
Dissecting a SQLCLR Exception 181 Error-Handling Mechanisms :Z 183
Summary 185
I I Chapter 8: Transformation and Reprojection 187
Datum Transformation 187 Transformation Algorithms 190
Coordinate Offsets (Two Parameters) 190 Molodensky Transformation (Five Parameters) 191 Helmert Transformation (Seven Parameters) 192
Transforming to and from WGS84 193 Projection, Unprojection, and Reprojection 194 Performing Manual Calculations 197 Creating a Reusable Spatial Conversion Library 198
Storing Transformation Parameters 198 Compiling the Proj.NET Assembly for SQL Server 200 Creating Transformation Sinks 202
Registering the Transformation Assembly and Functions 207 Transforming Spatial Data in T-SQL 208 Architecture and Design Considerations 209 Summary 210
i CONTENTS
Chapter 9: Examining Spatial Properties ....211 Property and Method Syntax—A Reminder 211 Examining the Type of Geometry Used to Represent a Feature 212
Returning the Type of Geometry 212 Testing the Type of Geometry 213 Determining the Number of Dimensions Occupied by a Geometry 214
Testing for OGC Properties 217
Testing for Simplicity 217 Testing if a Geometry Is Closed 219 Testing Whether a LineString or Curve Is a Ring 221
Counting the Number of Points in a Geometry 221
Returning the Number of Points in a Geometry 222 Testing Whether a Geometry Is Empty 223
Returning Individual Points from a Geometry 224
Retrieving a Point by Index C. 224 Returning the Start and End Point of a Geometry 226
Determining the Center of a Geometry 227
Calculating the Centroid of a geometry Instance 227 Calculating the Envelope Center of a geography Instance 228 Returning an Arbitrary Point from a Geometry 230
Returning Coordinate Values 231
Returning geometry Coordinates 231 Returning geography Coordinates 232 Returning Extended Coordinate Values 232
Properties Describing the Extent of a Geometry 233
Calculating the Boundary of a Geometry 233 Calculating the Envelope of a Geometry 235 Calculating the Bounding Circle of a geography Instance 237
Properties Related to Surfaces ..239
Isolating the Exterior Ring of a Geometry Polygon 239 Counting the Interior Rings of a geometry 241 Isolating an Interior Ring from a geometry Polygon 242 Counting the Rings in a geography Polygon 243
• CONTENTS
Isolating a Ring from a geography Polygon 244
Properties Related to GeometryCollections 245
Counting the Number of Geometries in a Collection 246 Retrieving an Individual Geometry from a Collection 247
Calculating Metrics 248
Measuring the Length of a Geometry 248 Calculating the Area Contained by a Geometry 249
Setting or Retrieving the Spatial Reference Identifier (SRID) 251 Summary 252 I I Chapter 10: Modification and Simplification 253 Simplifying a Geometry 253
The Douglas-Peucker Algorithm 254 The ReduceO Method 255
Converting Curves to Lines 256
Linearization with STCurveToLineO 256 Approximate Linearization r. 257
Reorienting a geography Polygon 258 Densification.... 260 Buffering 264
Creating a Buffer 264 Creating a Simpler Buffer 266 Creating a Curved Buffer 267
Creating the Convex Hull of a Geometry 269 Summary 271 C Chapter 11: Aggregation and Combination 273 Creating a Union of Two Items of Spatial Data 273 Appending One Geometry onto Another 276 Subtracting One Geometry from Another 277 Determining the Unique Parts of Two Geometries 281 Defining the Intersection Between Two Geometries 282 Aggregating Columns of Spatial Data 284
UnionAggregate 286
I I CONTENTS
EnvelopeAggregate 287 CollectionAggregate 287 ConvexHullAggregate 288
Combining Spatial Result Sets 288 Joining Tables Using a Spatial Column 291 Summary 292 • Chapter 12: Testing Spatial Relationships 293 Calculating the Distance Between Two Geometries 293
Finding Nearest Neighbors: Basic Approach 295 Finding Nearest Neighbors Using a Spatial Index 296 Finding Nearest Neighbors Within a Fixed Search Zone 298 Finding Nearest Neighbors with an Expanding Search Zone 299 Nearest Neighbor Query Plan Comparison 301
Calculating the Shortest Path Between Two Geometries 304 Testing for Intersection 308
Accurate Testing for Intersection .-. 308 Approximate Testing for Intersection 310 Testing for Disjointness 313
Identifying Specific Types of Intersection .314
Determining Whether One Geometry Crosses Another 314 Finding Out Whether Two Geometries Touch 317 Testing for Overlap 318 Testing Whether One Geometry Is Contained Within Another 320 Testing Whether One Geometry Contains Another 321
Defining Custom Relationships Between Geometries 322 Summary . 325 • Chapter 13: Clustering and Distribution Analysis 327 SQL Server's Spatial Histogram Procedures 328
sp_help_spatial_geometry_histogram 328 sp_help_spatial_geography_histogram 331
Creating a Customized Distribution Analysis 335
Creating a Regular Grid 336
• CONTENTS
Creating a Bullseye Grid 340 Defining a Custom Pattern of Cells 342
k-Means Clustering 344 Summary 352
i i Chapter 14: Route Finding 353
Graph Theory 353 Modeling a Street Network 354
Defining the Street Names 355 Defining Street Segments (Edges) 356 Viewing the Network 356
Brute-Force Routing inT-SQL 357
Tracing The Route 361 Avoiding Cycles 362 Allowing for Direction 364
A*: A More Efficient Routing Algorithm? 365
Heuristics .'. .365 Restructuring the Data 367 Traversing Across the Network 369 Putting It All Together 373 Testing It Out 377 Optimizing the Code and Further Enhancements 378
The Traveling Salesman Problem ...379 Harnessing the Bing Maps Routing Service 382 Summary 386
i i Chapter 15: Triangulation and Tesselation 387
The Importance of Triangles ...387 Triangulation 388
The Delaunay Triangulation 389 Calculating the Delaunay Triangulation 390
An SQLCLR Triangulation Procedure 392
Data Structures 392 Setting Up the Points 394
3 CONTENTS
Creating the Supertriangle 395 Adding Points to the Triangulated Mesh 396 Outputting the Results 398 Registering and Testing the Function 399
Creating 3D Surface Models .401
Adapting the Triangulation Code into 3D 401 A Practical Example: Creating a 3D Surface from LIDAR Data 402 Visualizing a 3D Mesh in WPF 404
Creating Alpha Shapes (Concave Hulls).. 407
Adapting the Triangulation Code to Create Alpha Shapes 408 A Practical Example: Calculating the Outline of Massachusetts 408 Small Alpha Values 409 Large Alpha Values 410 Getting the Alpha Value "Just Right" 410
Voronoi Tessellations 412
Adapting the Triangulation Code to Create a Voronoi Tesselation 413 A Practical Example: Outbreaks of Cholera in Victorian London 414
Summary 418 Chapter 16: Visualization and User Interface ..419 The SSMS Spatial Results Tab... 419
Add a Buffer to Points 421 Create a Base Layer Against Which to Display Your Data 421 Customize the Display 423
Thematic Mapping with Google Earth. 425
Getting the Data 426 Creating a Color Range 427 Exporting to KML 428 Styling the KML File 428 Taking It Further 431 Limitations 432
Creating a Web Map Interface with Bing Maps 433
Create the SQL Server Table and Stored Procedure 433
! CONTENTS
Creating the Web Application 434 Defining the HTML/Javascript 435 Retrieving JSON Data from SQL Server to the Webpage 440 Taking It Further 443
Summary 444 Chapter 17: Reporting Services 445 Creating a Simple Report Map 445
Creating the Report Project 446 Defining the SQL Connection 447 Creating a New Report 448 Adding a Map to the Report 448 Specify a Data Source 449 Design the Query 452 Previewing the Report ; 455
Customizing the Map Viewport .C 455
General Options 456 Center and Zoom 457 Optimization 458 Fill, Border, and Shadow 458
Adding an Analytic Dataset ...459 Applying a Styling Rule 462
General Settings 463 Distribution Options 463 Legend 465
Adding a Bing Maps Tile Layer 466 Assigning Actions to Map Elements 467 Limitations of the SSRS Map Control 469 Summary 470 I i Chapter 18: Indexing 471 The Need for a Spatial Index ....471 How Does a Spatial Index Work? 472
The Primary and Secondary Filter 472
• CONTENTS
The Grid Structure of a Spatial Index 472 Refining the Grid 476 The Auto Grid 479 Optimization Rules in a Multilevel Grid Index 479 Covering Rule 480 Deepest-Cell Rule 481 Cells-Per-Object Rule 481
Creating a Spatial Index in T-SQL 481 Analysing How the Index Is Used 483
Tesselation Information 486 Primary Filter Selectivity 486 Internal Filtering 487 Secondary Filtering and Output 487 Efficiency Measures 487
Creating a geography Index 488 Designing Queries to Use a Spatial Index. 489
Supported Methods .'. 489 Correct Syntax and Query Form 490 Checking if a Spatial Index Is Being Used 491 Adding an Index Hint .493
Optimizing an Index 493
Grid Resolution 493 Bounding Box 494 Cells per Object 495
Performance Comparison.... 496 Summary 497 • Appendix 499