Beginning Spatial with SQL Server 2008 (eBook)

Alastair Aitchison has more than eight years of experience as a management information consultant, specializing in the design and deployment of online reporting systems. For the last three years, he has been employed as a reporting and analysis manager at Aviva, the world's fifth largest insurance group. In this role, he has championed the use of spatial data in corporate applications including the geographic analysis of risk patterns, plotting the success of regional marketing campaigns, and understanding the impact of major weather incidents. Alastair is a Microsoft Office Specialist Master Instructor and has delivered numerous training courses to individuals and small groups on a range of software packages.

Contents at a Glance 5
Contents 7
About the Author 20
About the Technical Reviewer 21
Acknowledgments 22
Introduction 23
Who This Book Is For 24
How This Book Is Structured 24
Prerequisites 25
Downloading the Code 25
Contacting the Author 25
Defining Spatial Information 27
What Is Spatial Data? 27
Uses of Spatial Data 27
Representing Features on the Earth 28
Describing Positions Using a Coordinate System 35
Applying Coordinate Systems to the Earth 38
Using Spatial Reference Systems 48
Spatial Reference Identifiers 49
Spatial References in SQL Server 2008 49
Comparing Spatial Reference Systems 52
Summary 55
Implementing Spatial Data in SQL Server 2008 56
Understanding Datatypes 56
Comparing Spatial Datatypes 57
The geography Datatype 58
The geometry Datatype 63
Choosing the Right Spatial Datatype 67
How Spatial Data Is Stored 73
Converting Between Datatypes 74
Spatially Enabling Your Tables 75
Summary 77
Working with Spatial Data in the . NET Framework 78
What Is the . NET Framework? 78
How . NET Is Hosted 79
Why Use . NET for Spatial Functionality? 81
Applying Principles of Object Orientation 83
Instantiating Spatial Objects 87
Invoking Spatial Instance Methods 89
Accessing Properties 91
Combining T-SQL and . NET CLR Methods 92
Using Open Geospatial Consortium Methods 93
Handling Exceptions in the CLR 93
Summary 96
Creating Spatial Data Objects 99
Choosing an Appropriate Static Method 99
Creating Geometries from Well- Known Text 101
Creating Geometries from Well- Known Binary 113
Creating Geometries from Geography Markup Language 125
Summary 135
Marking Out Geometries Using Virtual Earth 136
Creating the Web Application 137
Using the Web Application 151
Creating a Geometry from the WKT Output 153
Summary 155
Importing Spatial Data 157
Sources of Spatial Data 157
Importing Tabular Spatial Data 158
Importing Data from Keyhole Markup Language 164
Importing Data from ESRI Shapefile Format 168
Using Third- Party Conversion Tools 174
Summary 175
Geocoding 176
What Is Geocoding? 176
MapPoint Web Service 177
Creating the . NET Assembly 179
Compiling the Assembly 189
Configuring the Database 190
Importing the Assembly 191
Creating the Function 192
Using the Geocode Function 193
Summary 196
Syndicating Spatial Data 198
Why Syndicate Spatial Information? 198
Syndication Formats 199
The GeoRSS Format 201
Attaching Spatial Information to a Feed 204
Creating a GeoRSS Feed 205
Serving the GeoRSS Feed 212
Testing the Feed Handler 215
Consuming the GeoRSS Feed 216
Summary 223
Presenting Spatial Data Using Web Mapping Services 224
The Application 224
Process Overview 225
Obtaining the Source Data 228
Creating a New Web Site Project 229
Creating the HTML Page 229
Creating the Static JavaScript 232
Creating the Stored Procedure 245
Creating the Web Handler 248
Viewing the Page 261
Further Applications 262
Summary 264
Visualizing Query Results in Management Studio 266
SQL Server Management Studio 266
Visualizing Spatial Results 266
Choosing Visualization Options 268
Supported Projections 269
Summary 274
Examining Properties of Spatial Objects 277
Returning the Name of a Geometry Type 278
Returning the Number of Dimensions Occupied by a Geometry 279
Testing Whether a Geometry Is of a Particular Type 280
Testing Whether a Geometry Is Simple 285
Testing Whether a Geometry Is Closed 287
Testing Whether a LineString Is a Ring 289
Counting the Number of Points in a Geometry 291
Testing Whether a Geometry Is Empty 292
Returning Cartesian Coordinate Values 294
Returning Geographic Coordinate Values 295
Returning Extended Coordinate Values 296
Returning a Specific Point from a Geometry 297
Finding the Start and End Points of a Geometry 299
Finding the Centroid of a geometry Polygon 300
Finding the Center of a geography Instance 302
Returning an Arbitrary Point from a Geometry 304
Measuring the Length of a Geometry 305
Calculating the Area Contained by a Geometry 307
Setting or Retrieving the SRID of a Geometry 309
Isolating the Exterior Ring of a Geometry Polygon 311
Counting the Interior Rings of a Geometry 312
Isolating an Interior Ring from a Polygon 314
Counting the Rings in a geography Polygon 315
Isolating a Ring from a geography Polygon 317
Identifying the Boundary of a Geometry 318
Calculating the Bounding Box of a Geometry 321
Calculating the Envelope of a geography Object 323
Counting the Elements in a Geometry Collection 324
Retrieving an Individual Geometry from a Geometry Collection 326
Summary 328
Modifying Spatial Objects 331
Ensuring That an Object Is Valid 331
Validating a Geometry 334
Combining Spatial Objects 335
Defining the Intersection of Two Geometries 339
Identifying the Difference Between Two Geometries 343
Calculating the Symmetric Difference Between Two Geometries 346
Simplifying a Geometry 348
Creating a Buffer Around an Object 350
Creating a Simpler Buffer 354
Creating the Convex Hull of a Geometry 356
Summary 359
Testing Spatial Relationships 361
Testing the Equality of Two Geometries 361
Calculating the Distance Between Geometries 364
Testing Whether Two Geometries Intersect 371
Performing a Quick Test of Intersection Between Two Geometries 374
Testing Whether Two Geometries Are Disjoint 377
Finding Out Whether One Geometry Crosses Another 378
Finding Out Whether Two Geometries Touch 381
Testing Whether One Geometry Overlaps Another 384
Testing Whether a Geometry Is Contained Within Another Geometry 386
Testing Whether a Geometry Contains Another Geometry 387
Testing Custom Relationships Between Geometries 389
Summary 392
Indexing 394
What Does a Spatial Index Do? 394
How Do Spatial Indexes Work? 395
Building an Index from the Grid 402
Applying a Grid to the geography Datatype 404
Creating a Spatial Index Using T- SQL 405
Creating a Spatial Index in SQL Server Management Studio 408
Designing Queries to Use a Spatial Index 410
Providing a Hint to a Spatial Index 412
Optimizing an Index 413
Summary 416
Index 417