MSDN Magazine - February 2009 - (Page 95)

BoB BeauchemIn Under The Table Spatial Data Support In SQL Server 2008 SQL Server 2008 offers a number of enhancements, including new data types, new T-SQL statements, and incremental changes to Microsoft .NET Framework support, XML support, and the Service Broker feature. By far, the biggest and most exciting change for developers is the SQL Server 2008 support for spatial data—a powerful addition to the SQL Server programmer’s toolbox. In this column, I’m going to present a short overview of spatial data and illustrate some interesting uses. Spatial data is defined as data that is represented by 2D or 3D images. SQL Server 2008 supports 2D images, and 3D may be added in a future release. Spatial data can be further subdivided into geometric data (data that can use calculations involving Euclidian geometry) and geographic data (data that identifies geographic locations and boundaries on the earth). SQL Server 2008 ships with the two data types necessary for storing spatial data: geometry and geography. Using the Geography Data Type Let’s start with a simple application of spatial data, which could be useful to most traditional online transaction processing (OLTP) systems that involve customers. Spatial data is useful in this case because every customer has an address. You usually think of addresses as street, city, state, country, and ZIP code, but an address is a point on the earth and can also be represented as a latitude/longitude pair. Technically, an address can comprise a parcel of land that can be represented as a polygon, but let’s stick to a single point in this case, just for simplicity. Knowing where a customer lives as latitude/longitude allows you to answer questions such as: • What are the three closest bank branches for a specific customer? • Which salesperson lives closest to the customer? • How many customers does your company have within a 10 mile radius of, for instance, Seattle, Washington? • How many customers live more than 2 miles from your nearest branch location? The process of converting a street address to a latitude/longitude pair is called address geocoding. A number of online services (including MapPoint, Virtual Earth, and Google Earth) provide geocoding as a service. To convert a street address in the U.S. to a point, you can encapsulate a call to the MapPoint geocoding Web service in a SQLCLR function. This is illustrated in the code sample at code.msdn.microsoft.com/mag200902DBDev. But where should you store the latitude/longitude inside your SQL Server database? SQL Server 2008 ships with the two necessary data types for storing spatial data: geometry and geography. Both data types are implemented using the .NET architecture for user-defined types, meaning that they can have properties and methods. Let’s stick with the geography data type for now because this maps more closely to the problem at hand. You can define an instance of a geography type with a simple SQL variable declaration (DECLARE @g geography) or as a column in a table, and you can initialize this data type in a variety of ways. For a geography type representing an instance of a point, the simplest way is to use the STPointFromText static method of the geography type. The STPointFromText method requires not only a textual representation of a point in the Open Geospatial Consortium’s well-known text format (WKT)—that is, POINT(x,y)— but also a spatial reference identifier (SRID). The SRID identifies the spatial reference system used for either a round-earth or flat-earth mapping and, for now, it’s enough to know that the MapPoint geocoder Web service uses GPS coordinates that correspond to SRID 4326. This represents the World Geodetic System 1984 (WGS 84). (To find all the SRIDs that SQL Server 2008 supports, simply query the metadata table sys.spatial_reference_systems.) So your code to initialize the address geography would look something like this: DECLARE @addr nvarchar(256) = 'Some sample address, City, State, Zip'; DECLARE @addr_as_xy nvarchar(30); DECLARE @g geography; SET @addr_as_xy = dbo.Geocoder(@addr); SET @g = geography::STPointFromText(@addr_as_xy, 4326); Note that because geography is implemented as a .NET-based data type, there are some interesting points to mention about the code. First, STPointFromText is a static method, so it must be called with the datatype::method syntax. In addition, the method names of .NET-based data types are case sensitive so, STPointFromText must use exact capitalization. Code download at code.msdn.microsoft.com/mag200902DBDev. February 2009 95 http://code.msdn.microsoft.com/mag200902DBDev http://code.msdn.microsoft.com/mag200902DBDev

Table of Contents Feed for the Digital Edition of MSDN Magazine - February 2009

MSDN Magazine - February 2009 Contents Toolbox CLR Inside Out Data Points Cutting Edge Patterns In Practice Best Practices .Net Interop "Oslo" Basics Patterns Silverlight Under The Table Foundations Windows With C++ .NET Matters Going Places { End Bracket }

MSDN Magazine - February 2009

For optimal viewing of this digital publication, please enable JavaScript and then refresh the page. If you would like to try to load the digital publication without using Flash Player detection, please click here.