Aitchison Beginning Spatial with SQL Server 2008

1. ed
ISBN: 978-1-4302-1830-2
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 456 Seiten

Microsoft SQL Server 2008 introduces new geography and geometry spatial datatypes that enable the storage of structured data describing the shape and position of objects in space. This is an interesting and exciting new feature, with many potentially useful applications. Beginning Spatial with SQL Server 2008 covers everything you need to know to begin using these new spatial datatypes, and explains how to apply them in practical situations involving the spatial relationships of people, places, and things on the earth. All of the spatial concepts introduced are explained from the ground up, so you need not have any previous knowledge of working with spatial data. Every section is illustrated with code examples that you can use directly in SQL Server. All of the topics covered in this book apply to all versions of SQL Server 2008, including the freely available SQL Server 2008 Express.

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.

Autoren/Hrsg.

Aitchison, Alastair

Inhaltsverzeichnis

1;Contents at a Glance;5
2;Contents;7
3;About the Author;20
4;About the Technical Reviewer;21
5;Acknowledgments;22
6;Introduction;23
6.1;Who This Book Is For;24
6.2;How This Book Is Structured;24
6.3;Prerequisites;25
6.4;Downloading the Code;25
6.5;Contacting the Author;25
7;Defining Spatial Information;27
7.1;What Is Spatial Data?;27
7.2;Uses of Spatial Data;27
7.3;Representing Features on the Earth;28
7.4;Describing Positions Using a Coordinate System;35
7.5;Applying Coordinate Systems to the Earth;38
7.6;Using Spatial Reference Systems;48
7.7;Spatial Reference Identifiers;49
7.8;Spatial References in SQL Server 2008;49
7.9;Comparing Spatial Reference Systems;52
7.10;Summary;55
8;Implementing Spatial Data in SQL Server 2008;56
8.1;Understanding Datatypes;56
8.2;Comparing Spatial Datatypes;57
8.3;The geography Datatype;58
8.4;The geometry Datatype;63
8.5;Choosing the Right Spatial Datatype;67
8.6;How Spatial Data Is Stored;73
8.7;Converting Between Datatypes;74
8.8;Spatially Enabling Your Tables;75
8.9;Summary;77
9;Working with Spatial Data in the . NET Framework;78
9.1;What Is the . NET Framework?;78
9.2;How . NET Is Hosted;79
9.3;Why Use . NET for Spatial Functionality?;81
9.4;Applying Principles of Object Orientation;83
9.5;Instantiating Spatial Objects;87
9.6;Invoking Spatial Instance Methods;89
9.7;Accessing Properties;91
9.8;Combining T-SQL and . NET CLR Methods;92
9.9;Using Open Geospatial Consortium Methods;93
9.10;Handling Exceptions in the CLR;93
9.11;Summary;96
10;Creating Spatial Data Objects;99
10.1;Choosing an Appropriate Static Method;99
10.2;Creating Geometries from Well- Known Text;101
10.3;Creating Geometries from Well- Known Binary;113
10.4;Creating Geometries from Geography Markup Language;125
10.5;Summary;135
11;Marking Out Geometries Using Virtual Earth;136
11.1;Creating the Web Application;137
11.2;Using the Web Application;151
11.3;Creating a Geometry from the WKT Output;153
11.4;Summary;155
12;Importing Spatial Data;157
12.1;Sources of Spatial Data;157
12.2;Importing Tabular Spatial Data;158
12.3;Importing Data from Keyhole Markup Language;164
12.4;Importing Data from ESRI Shapefile Format;168
12.5;Using Third- Party Conversion Tools;174
12.6;Summary;175
13;Geocoding;176
13.1;What Is Geocoding?;176
13.2;MapPoint Web Service;177
13.3;Creating the . NET Assembly;179
13.4;Compiling the Assembly;189
13.5;Configuring the Database;190
13.6;Importing the Assembly;191
13.7;Creating the Function;192
13.8;Using the Geocode Function;193
13.9;Summary;196
14;Syndicating Spatial Data;198
14.1;Why Syndicate Spatial Information?;198
14.2;Syndication Formats;199
14.3;The GeoRSS Format;201
14.4;Attaching Spatial Information to a Feed;204
14.5;Creating a GeoRSS Feed;205
14.6;Serving the GeoRSS Feed;212
14.7;Testing the Feed Handler;215
14.8;Consuming the GeoRSS Feed;216
14.9;Summary;223
15;Presenting Spatial Data Using Web Mapping Services;224
15.1;The Application;224
15.2;Process Overview;225
15.3;Obtaining the Source Data;228
15.4;Creating a New Web Site Project;229
15.5;Creating the HTML Page;229
15.6;Creating the Static JavaScript;232
15.7;Creating the Stored Procedure;245
15.8;Creating the Web Handler;248
15.9;Viewing the Page;261
15.10;Further Applications;262
15.11;Summary;264
16;Visualizing Query Results in Management Studio;266
16.1;SQL Server Management Studio;266
16.2;Visualizing Spatial Results;266
16.3;Choosing Visualization Options;268
16.4;Supported Projections;269
16.5;Summary;274
17;Examining Properties of Spatial Objects;277
17.1;Returning the Name of a Geometry Type;278
17.2;Returning the Number of Dimensions Occupied by a Geometry;279
17.3;Testing Whether a Geometry Is of a Particular Type;280
17.4;Testing Whether a Geometry Is Simple;285
17.5;Testing Whether a Geometry Is Closed;287
17.6;Testing Whether a LineString Is a Ring;289
17.7;Counting the Number of Points in a Geometry;291
17.8;Testing Whether a Geometry Is Empty;292
17.9;Returning Cartesian Coordinate Values;294
17.10;Returning Geographic Coordinate Values;295
17.11;Returning Extended Coordinate Values;296
17.12;Returning a Specific Point from a Geometry;297
17.13;Finding the Start and End Points of a Geometry;299
17.14;Finding the Centroid of a geometry Polygon;300
17.15;Finding the Center of a geography Instance;302
17.16;Returning an Arbitrary Point from a Geometry;304
17.17;Measuring the Length of a Geometry;305
17.18;Calculating the Area Contained by a Geometry;307
17.19;Setting or Retrieving the SRID of a Geometry;309
17.20;Isolating the Exterior Ring of a Geometry Polygon;311
17.21;Counting the Interior Rings of a Geometry;312
17.22;Isolating an Interior Ring from a Polygon;314
17.23;Counting the Rings in a geography Polygon;315
17.24;Isolating a Ring from a geography Polygon;317
17.25;Identifying the Boundary of a Geometry;318
17.26;Calculating the Bounding Box of a Geometry;321
17.27;Calculating the Envelope of a geography Object;323
17.28;Counting the Elements in a Geometry Collection;324
17.29;Retrieving an Individual Geometry from a Geometry Collection;326
17.30;Summary;328
18;Modifying Spatial Objects;331
18.1;Ensuring That an Object Is Valid;331
18.2;Validating a Geometry;334
18.3;Combining Spatial Objects;335
18.4;Defining the Intersection of Two Geometries;339
18.5;Identifying the Difference Between Two Geometries;343
18.6;Calculating the Symmetric Difference Between Two Geometries;346
18.7;Simplifying a Geometry;348
18.8;Creating a Buffer Around an Object;350
18.9;Creating a Simpler Buffer;354
18.10;Creating the Convex Hull of a Geometry;356
18.11;Summary;359
19;Testing Spatial Relationships;361
19.1;Testing the Equality of Two Geometries;361
19.2;Calculating the Distance Between Geometries;364
19.3;Testing Whether Two Geometries Intersect;371
19.4;Performing a Quick Test of Intersection Between Two Geometries;374
19.5;Testing Whether Two Geometries Are Disjoint;377
19.6;Finding Out Whether One Geometry Crosses Another;378
19.7;Finding Out Whether Two Geometries Touch;381
19.8;Testing Whether One Geometry Overlaps Another;384
19.9;Testing Whether a Geometry Is Contained Within Another Geometry;386
19.10;Testing Whether a Geometry Contains Another Geometry;387
19.11;Testing Custom Relationships Between Geometries;389
19.12;Summary;392
20;Indexing;394
20.1;What Does a Spatial Index Do?;394
20.2;How Do Spatial Indexes Work?;395
20.3;Building an Index from the Grid;402
20.4;Applying a Grid to the geography Datatype;404
20.5;Creating a Spatial Index Using T- SQL;405
20.6;Creating a Spatial Index in SQL Server Management Studio;408
20.7;Designing Queries to Use a Spatial Index;410
20.8;Providing a Hint to a Spatial Index;412
20.9;Optimizing an Index;413
20.10;Summary;416
21;Index;417