Chapter 2-beginning-spatial-with-sql-server-2008-pt-i

of 32

Please download to get full document.

View again

All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
PDF
32 pages
0 downs
28 views
Share
Description
1. THE EXPERT’S VOICE® IN SQL SERVER Beginning Spatial with SQL Server 2008 Alastair Aitchison Harness the power of SQL Server’s spatial feature set to create…
Transcript
  • 1. THE EXPERT’S VOICE® IN SQL SERVER Beginning Spatial with SQL Server 2008 Alastair Aitchison Harness the power of SQL Server’s spatial feature set to create location-based analysis and visualization tools
  • 2. Beginning Spatial with SQL Server 2008 Copyright © 2009 by Alastair Aitchison All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher. ISBN-13 (pbk): 978-1-4302-1829-6 ISBN-13 (electronic): 978-1-4302-1830-2 Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1 Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. Lead Editor: Jonathan Gennick Technical Reviewer: Evan Terry Editorial Board: Clay Andres, Steve Anglin, Mark Beckner, Ewan Buckingham, Tony Campbell, Gary Cornell, Jonathan Gennick, Michelle Lowman, Matthew Moodie, Jeffrey Pepper, Frank Pohlmann, Ben Renow-Clarke, Dominic Shakeshaft, Matt Wade, Tom Welsh Project Manager: Kylie Johnston Copy Editor: Bill McManus Associate Production Director: Kari Brooks-Copony Production Editor: Katie Stence Compositor: Susan Glinert Proofreader: April Eddy Indexer: Julie Grady Cover Designer: Kurt Krames Manufacturing Director: Tom Debolski Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax 201-348-4505, e-mail orders-ny@springer-sbm.com, or visit http://www.springeronline.com. For information on translations, please contact Apress directly at 2855 Telegraph Avenue, Suite 600, Berkeley, CA 94705. Phone 510-549-5930, fax 510-549-5939, e-mail info@apress.com, or visit http:// www.apress.com. Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales–eBook Licensing web page at http://www.apress.com/info/bulksales. The information in this book is distributed on an “as is” basis, without warranty. Although every precaution has been taken in the preparation of this work, neither the author nor Apress shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in this work. The source code for this book is available to readers at http://www.apress.com. Aitschison _18296 FRONT.fm Page ii Monday, December 22, 2008 9:00 AM
  • 3. vii Contents About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvii PART 1 III Working with Spatial Data ICHAPTER 1 Defining Spatial Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 What Is Spatial Data? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Uses of Spatial Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Representing Features on the Earth . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Points . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 LineStrings. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Polygons . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Choosing the Right Geometry . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Combining Geometries in a Geometry Collection . . . . . . . . . . . . . . . . 8 Understanding Interiors, Exteriors, and Boundaries . . . . . . . . . . . . . . 9 Positioning a Geometry . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Describing Positions Using a Coordinate System . . . . . . . . . . . . . . . . . . . 11 Geographic Coordinate System. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Projected Coordinate System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Applying Coordinate Systems to the Earth . . . . . . . . . . . . . . . . . . . . . . . . . 14 Datum . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Prime Meridian . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Unit of Measurement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Projection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Using Spatial Reference Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Spatial Reference Identifiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Aitschison _18296 FRONT.fm Page vii Monday, December 22, 2008 9:00 AM
  • 4. viii ICONTENTS Spatial References in SQL Server 2008 . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Expressing Spatial References in the Well-Known Text Format . . . 26 Contrasting a Geographic and a Projected Spatial Reference . . . . . 27 Comparing Spatial Reference Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 WGS 84 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 National Grid of Great Britain. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 ICHAPTER 2 Implementing Spatial Data in SQL Server 2008 . . . . . . . . . . 33 Understanding Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Comparing Spatial Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 The geography Datatype . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Coordinate System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Unit of Measurement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Spatial Reference ID. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 Size Limitations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 Ring Orientation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 The geometry Datatype . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Coordinate System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 Unit of Measurement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 Spatial Reference ID. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Ring Orientation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 Choosing the Right Spatial Datatype . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 Consistency. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 Accuracy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 The End(s) of the World . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 Presentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 Standards Compliance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 How Spatial Data Is Stored . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 Converting Between Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 Spatially Enabling Your Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 Creating a New Table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 Adding to an Existing Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 Enforcing a Common SRID . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 Aitschison _18296 FRONT.fm Page viii Monday, December 22, 2008 9:00 AM
  • 5. ICON TEN TS ix ICHAPTER 3 Working with Spatial Data in the .NET Framework . . . . . . 55 What Is the .NET Framework? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 How .NET Is Hosted . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 Why Use .NET for Spatial Functionality? . . . . . . . . . . . . . . . . . . . . . . . . . . 58 Applying Principles of Object Orientation . . . . . . . . . . . . . . . . . . . . . . . . . . 60 Data Abstraction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 Encapsulation (Data Hiding). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 Inheritance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 Polymorphism . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Instantiating Spatial Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Using Static Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Applying a Static Method of the Appropriate Datatype. . . . . . . . . . . 65 Invoking Spatial Instance Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 T-SQL Function Syntax. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 .NET CLR Instance Method Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 Chaining Multiple Method Calls. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 Accessor and Mutator Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 Accessing Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 Read-Only and Read-Write Properties . . . . . . . . . . . . . . . . . . . . . . . . 69 Combining T-SQL and .NET CLR Methods . . . . . . . . . . . . . . . . . . . . . . . . . 69 Using Open Geospatial Consortium Methods . . . . . . . . . . . . . . . . . . . . . . 70 Handling Exceptions in the CLR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 PART 2 III Adding Spatial Data ICHAPTER 4 Creating Spatial Data Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 Choosing an Appropriate Static Method . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 Creating Geometries from Well-Known Text . . . . . . . . . . . . . . . . . . . . . . . 79 Creating a Point from WKT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 Creating a LineString from WKT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 Creating a Polygon from WKT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 Creating a MultiPoint from WKT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 Creating a MultiLineString from WKT. . . . . . . . . . . . . . . . . . . . . . . . . 86 Creating a MultiPolygon from WKT. . . . . . . . . . . . . . . . . . . . . . . . . . . 86 Creating a Geometry Collection from WKT. . . . . . . . . . . . . . . . . . . . . 87 Creating Any Kind of Geometry from WKT. . . . . . . . . . . . . . . . . . . . . 87 Representing an Existing Geometry As WKT . . . . . . . . . . . . . . . . . . . 89 Aitschison _18296 FRONT.fm Page ix Monday, December 22, 2008 9:00 AM
  • 6. x ICONTENTS Creating Geometries from Well-Known Binary . . . . . . . . . . . . . . . . . . . . . 91 Creating a Point from WKB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 Creating a LineString from WKB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96 Creating a Polygon from WKB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97 Creating a Multielement Geometry from WKB. . . . . . . . . . . . . . . . . 100 Creating Any Type of Geometry from WKB . . . . . . . . . . . . . . . . . . . 102 Representing an Existing Geometry As WKB. . . . . . . . . . . . . . . . . . 102 Creating Geometries from Geography Markup Language . . . . . . . . . . . 103 Structure of a GML Document. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104 Declaring the GML Namespace. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104 Methods to Instantiate Geometry Objects from GML . . . . . . . . . . . 106 Creating a Point from GML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 Creating a LineString from GML . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108 Creating a Polygon from GML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 Creating a MultiPoint from GML . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 Creating a MultiLineString from GML. . . . . . . . . . . . . . . . . . . . . . . . 110 Creating a MultiPolygon from GML. . . . . . . . . . . . . . . . . . . . . . . . . . 110 Creating a Geometry Collection from GML. . . . . . . . . . . . . . . . . . . . 111 Representing an Existing Geometry As GML . . . . . . . . . . . . . . . . . . 112 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 ICHAPTER 5 Marking Out Geometries Using Virtual Earth . . . . . . . . . . . . 115 Creating the Web Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 Creating a New Web Site. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 Adding a Basic Map . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 Extending the Map Functionality. . . . . . . . . . . . . . . . . . . . . . . . . . . . 121 Adding Controls to HTMLPage.htm. . . . . . . . . . . . . . . . . . . . . . . . . . 128 Using the Web Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130 Creating a Geometry from the WKT Output . . . . . . . . . . . . . . . . . . . . . . . 132 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134 ICHAPTER 6 Importing Spatial Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 Sources of Spatial Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 Importing Tabular Spatial Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138 Importing the Text File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 Adding the geography Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142 Populating the Spatial Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142 Aitschison _18296 FRONT.fm Page x Monday, December 22, 2008 9:00 AM
  • 7. ICON TEN TS xi Importing Data from Keyhole Markup Language . . . . . . . . . . . . . . . . . . 144 Comparing KML to GML. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144 Transforming KML to GML. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146 Importing Data from ESRI Shapefile Format . . . . . . . . . . . . . . . . . . . . . . 148 Obtaining Sample Shapefile Data . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 Importing Shapefile Data with Shape2SQL . . . . . . . . . . . . . . . . . . . 150 Using Third-Party Conversion Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154 Commercial Tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154 Free Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155 ICHAPTER 7 Geocoding . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157 What Is Geocoding? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157 MapPoint Web Service . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158 Accessing the MapPoint Web Service from SQL Server. . . . . . . . . 159 Signing Up for the MapPoint Web Service . . . . . . . . . . . . . . . . . . . . 160 Creating the .NET Assembly . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160 Creating a New Project . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161 Configuring the Project. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162 Adding the Geocoding Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165 Compiling the Assembly . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170 Configuring the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 Enabling CLR Support. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 Setting Security Permissions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 Importing the Assembly . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172 Creating the Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 Using the Geocode Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177 PART 3 III Presenting Spatial Data ICHAPTER 8 Syndicating Spatial Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181 Why Syndicate Spatial Information? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181 Syndication Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182 The GeoRSS Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184 GeoRSS Spatial Encodings. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185 Relating Spatial Information with the GeoRSS <where> Tag . . . . 186 Aitschison _18296 FRONT.fm Page xi Monday, December 22, 2008 9:00 AM
  • 8. xii ICONTENTS Attaching Spatial Information to a Feed . . . . . . . . . . . . . . . . . . . . . . . . . . 187 Creating a GeoRSS Feed . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188 Creating the Sample Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189 Serving the GeoRSS Feed . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 Testing the Feed Handler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198 Con
  • Related Search
    We Need Your Support
    Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

    Thanks to everyone for your continued support.

    No, Thanks