spatial data

Spatial Data Types
I was recently involved in a project involving law enforcement (this is not a euphemism for
being arrested), and, as part of the data architecture, we realized that it would be great if we
could identify certain types of information on a map of the area in question—items such as
locations of booted vehicles, sex offenders, or any number of other items.
Perhaps I’m working on a real estate system and want to be able to show in what school
district or congressional district a house is located. Or maybe I want to be able to fi nd retail
stores in relation to a house, or maybe the nearest airports.
And so, may I introduce to you the new spatial data types of SQL Server 2008. The
GEOGRAPHY data type is used to represent geodetic spatial data, items on the oblate spheroid,
or slightly fl attened sphere that is our planet Earth. The GEOMETRY data type represents
planar spatial data, or items on fl at surfaces. Both of these are implemented as system CLR
types, so, like HIERARCHYID, they do not require you to enable CLR in order to use it.
Note This topic could fi ll a sizable book on its own, so bear in mind that we are only scratching
the surface of spatial data in this introductory-level book.
DECLARE @TransactionToken varbinary(max);
SET @TransactionToken = GET_FILESTREAM_TRANSACTION_CONTEXT ();
--At this point, you would return these values to the client
SELECT @PathName, @TransactionToken ;
/*
4: Call the OpenSqlFilestream API to obtain a Win32 handle
by passing in the path and transaction token from above. Next
using the Win32 hanlde you got from OpenSqlFilestream,
you can call the various Win32 API functions such as ReadFile(),
WriteFile(), TransitFile(), and so on. Of course, be sure to
call CloseHandle() when you are done working with the file.
*/
--5: You must then COMMIT or ROLLBACK the transaction.
COMMIT TRANSACTION;
Chapter 3 Type System 105
Types of Spatial Data
As mentioned previously, there are two data types in SQL Server 2008 that can represent
spatial data, GEOMETRY and GEOGRAPHY. There are a total of 11 different spatial data objects,
although only 7 of them are instantiable: Point, LineString, Polygon, GeometryCollection,
MultiPoint, MultiLine, and MultiPolygon. Figure 3-6 shows the entire hierarchy of all 11 (the
instantiable ones are shaded dark gray).
Polygon
LineString
MultiSurface
MultiCurve
MultiPoint
MultiPolygon
MultiLineString
Curve
Surface
GeometryCollection
Point
Geometry
FIGURE 3-6 Spatial data type hierarchy
Working with the Spatial Data Types
To best show how these various instant types work, I am going to use the following shapes
(numbered 1 through 8):
1. Polygon: A square with a small hole in the middle.
2. Polygon: A square located within polygon 1.
3. Polygon: A square located in the hole of polygon 1.
4. Polygon: A pentagon that, although is within the outer boundaries of polygon 1, covers
parts of polygon 1 and the hole within polygon 1.
5. Polygon: A square that shares a border with polygon 1.
6. LineString: A line that touches polygon 1.
106 Introducing SQL Server 2008
7. MultiLineString: Two lines, one of which crosses polygons 1 and 5 and the other which
touches polygon 5.
8. MultiPolygon: Four squares, three of which are wholly contained within polygon 1 and
a fourth which lies outside of polygon 1.
Figure