Pro Spatial with SQL Server 2012

554 Pages·2012·43.842 MB·English

by  Aitchison, Alastair

For your convenience Apress has placed some of the front
matter material after the index. Please use the Bookmarks
and Contents at a Glance links to access them.
Contents at a Glance
Contents..................................................................................................................vii
Foreword...............................................................................................................xxi
About the Author..................................................................................................xxii
About the Technical Reviewer.............................................................................xxiii
Acknowledgments...............................................................................................xxiv
Introduction..........................................................................................................xxv
■ Chapter 1: Spatial Reference Systems.................................................................1
■ Chapter 2: Spatial Features ...............................................................................21
■ Chapter 3: Spatial Datatypes.............................................................................51
■ Chapter 4: Creating Spatial Data........................................................................77
■ Chapter 5: Importing Spatial Data...................................................................101
■ Chapter 6: Geocoding.......................................................................................139
■ Chapter 7: Precision, Validity, and Errors........................................................163
■ Chapter 8: Transformation and Reprojection...................................................187
■ Chapter 9: Examining Spatial Properties.........................................................211
■ Chapter 10: Modification and Simplification...................................................253
■ Chapter 11: Aggregation and Combination......................................................273
■ Chapter 12: Testing Spatial Relationships.......................................................293
■ Chapter 13: Clustering and Distribution Analysis............................................327
v
■ Chapter 14: Route Finding...............................................................................353
■ Chapter 15: Triangulation and Tesselation......................................................387
■ Chapter 16: Visualization and User Interface..................................................419
■ Chapter 17: Reporting Services.......................................................................445
■ Chapter 18: Indexing........................................................................................471
■ Appendix..........................................................................................................499
Index.....................................................................................................................519

vi
C H A P T E R 1

■ ■ ■

Spatial Reference Systems
Spatial data analysis is a complex subject area, taking elements from a range of academic disciplines,
including geophysics, mathematics, astronomy, and cartography. Although you do not need to
understand these subjects in great depth to take advantage of the spatial features of SQL Server 2012,
it is important to have a basic understanding of the theory involved so that you use spatial data
appropriately and effectively in your applications.
This chapter describes spatial reference systems—ways of describing positions in space—and
shows how these systems can be used to define features on the earth's surface. The theoretical concepts
discussed in this chapter are fundamental to the creation of consistent, accurate spatial data, and are
used throughout the practical applications discussed in later chapters of this book.
What Is a Spatial Reference System?
The purpose of a spatial reference system (sometimes called a coordinate reference system) is to
identify and unambiguously describe any point in space. You are probably familiar with the terms
latitude and longitude, and have seen them used to describe positions on the earth. If this is the case,
you may be thinking that these represent a spatial reference system, and that a pair of latitude and
longitude coordinates uniquely identifies every point on the earth's surface, but, unfortunately, it's
not quite that simple.
What many people don't realize is that any particular point on the ground does not have a unique
latitude or longitude associated with it. There are, in fact, many systems of latitude and longitude, and
the coordinates of a given point on the earth will differ depending on which system was used.
Furthermore, latitude and longitude coordinates are not the only way to define locations: many spatial
reference systems describe the position of an object without using latitude and longitude at all. For
example, consider the following three sets of coordinates:
• 51.179024688899524, –1.82747483253479
• SU 1215642213
• 581957, 5670386
These coordinates look very different, yet they all describe exactly the same point on the earth's
surface, located in Stonehenge, in Wiltshire, United Kingdom. The coordinates differ because they all
relate to different spatial reference systems: the first set is latitude and longitude coordinates from the
WGS84 reference system, the second is a grid reference from the National Grid of Great Britain, and
the third is a set of easting/northing coordinates from UTM Zone 30U.
Defining a spatial reference system involves not only specifying the type of coordinates used, but
also stating where those coordinates are measured from, in what units, and the shape of the earth over
which those coordinates extend.
1
CHAPTER 1 ■ SPATIAL REFERENCE SYSTEMS

Many different spatial reference systems exist, and each has different benefits and drawbacks:
some offer high accuracy but only over a relatively small geographic area; others offer reasonable
accuracy across the whole globe. Some spatial reference systems are designed for particular purposes,
such as for nautical navigation or for scientific use, whereas others are designed for general global use.
One key point to remember is that every set of coordinates is unique to a particular spatial
reference system, and only makes sense in the context of that system.
Modeling the Earth
The earth is a very complex shape. On the surface, we can see by looking around us that there are
irregular topographical features such as mountains and valleys. But even if we were to remove these
features and consider the mean sea-level around the planet, the earth is still not a regular shape. In
fact, it is so unique that geophysicists have a specific word solely used to describe the shape of the earth:
the geoid.
The surface of the geoid is smooth, but it is distorted by variations in gravitational field strength
caused by changes in the earth's composition. Figure 1-1 illustrates a depiction of the shape of the geoid.

Figure 1-1. The irregular shape of the earth.
In order to describe the location of an object on the earth’s surface with maximum accuracy, we
would ideally define its position relative to the geoid itself. However, even though scientists have
recently developed very accurate models of the geoid (to within a centimeter accuracy of the true
shape of the earth), the calculations involved are very complicated. Instead, spatial reference systems
normally define positions on the earth's surface based on a simple model that approximates the geoid.
This approximation is called a reference ellipsoid.
■ Note Not only is the geoid a complex shape, but it is also not constant. Astronomical and geophysical forces,
climatic changes, and volcanic activity all contribute to changes in the earth's structure that continuously alter the
shape of the geoid.
2
CHAPTER 1 ■ SPATIAL REFERENCE SYSTEMS

Approximating the Geoid
Many early civilizations believed the world to be flat (and a handful of modern day organizations still
do, e.g., the “Flat Earth Society,” http://www.theflatearthsociety.org). Our current understanding of
the shape of the earth is largely based on the work of Ancient Greek philosophers and scientists,
including Pythagoras and Aristotle, who scientifically proved that the world is, in fact, round. With this
fact in mind, the simplest reference ellipsoid that can be used to approximate the shape of the geoid is a
perfect sphere. Indeed, there are some spatial reference systems that do use a perfect sphere to model
the geoid, such as the system on which many Web-mapping providers, including Google Maps and
Bing Maps, are based.
Although a sphere would certainly be a simple model to use, it doesn't really match the shape of the
earth that closely. A better model of the geoid, and the one most commonly used, is an oblate spheroid. A
spheroid is the three-dimensional shape obtained when you rotate an ellipse about its shorter axis. In
other words, it's a sphere that has been squashed in one of its dimensions. When used to model the
earth, spheroids are always oblate—they are wider than they are high—as if someone sat on a beach
ball. This is a fairly good approximation of the shape of the geoid, which bulges around the equator.
The most important property of a spheroid is that, unlike the geoid, a spheroid is a regular shape
that can be exactly mathematically described by two parameters: the length of the semi-major axis
(which represents the radius of the earth at the equator), and the length of the semi-minor axis (the
radius of the earth at the poles). The properties of a spheroid are illustrated in Figure 1-2.

Figure 1-2. Properties of a spheroid.
■ Note A spheroid is a sphere that has been “flattened” in one axis, and can be described using only two
parameters. An ellipsoid is a sphere that has been flattened in two axes; that is, the radii of the shape in the x-, y-,
and z-axes are all different. Although referred to as a reference ellipsoid, in practice most models of the earth are
actually spheroids, because ellipsoid models of the world are not significantly more accurate at describing the
shape of the geoid than simpler spheroid models.
3
CHAPTER 1 ■ SPATIAL REFERENCE SYSTEMS

The flattening ratio of an ellipsoid, f, is used to describe how much the ellipsoid has been
“squashed,” and is calculated as
f = (a – b ) / a
where a = length of the semi-major axis; b = length of the semi-minor axis.
In most ellipsoidal models of the earth, the semi-minor axis is only marginally smaller than the
semi-major axis, which means that the value of the flattening ratio is also small, typically around
0.003. As a result, it is sometimes more convenient to state the inverse flattening ratio of an ellipsoid
instead. This is written as 1/f, and calculated as follows.
1 / f = a / (a – b)
The inverse-flattening ratio of an ellipsoid model typically has a value of approximately
300.Given the length of the semi-major axis a and any one other parameter, f, 1/f, or b, we have all the
information necessary to describe a reference ellipsoid used to model the shape of the earth.
Regional Variations in Ellipsoids
There is not a single reference ellipsoid that best represents every part of the whole geoid. Some
ellipsoids, such as the World Geodetic System 1984 (WGS84) ellipsoid, provide a reasonable
approximation of the overall shape of the geoid. Other ellipsoids approximate the shape of the geoid
very accurately over certain regions of the world, but are much less accurate in other areas. These local
ellipsoids are normally only used in spatial reference systems designed for use in specific countries,
such as the Airy 1830 ellipsoid commonly used in Britain, or the Everest ellipsoid used in India.
Figure 1-3 provides an (exaggerated) illustration of how different ellipsoid models vary in accuracy
over different parts of the geoid. The dotted line represents an ellipsoid that provides the best accuracy
over the region of interest, whereas the dash-dotted line represents the ellipsoid of best global accuracy.

Figure 1-3. Comparison of cross-sections of different ellipsoid models of the geoid.
4
CHAPTER 1 ■ SPATIAL REFERENCE SYSTEMS

It is important to realize that specifying a different reference ellipsoid to approximate the geoid
will affect the accuracy with which a spatial reference system based on that ellipsoid can describe the
position of features on the earth. When choosing a spatial reference system, we must therefore be
careful to consider one that is based on an ellipsoid suitable for the data in question.
SQL Server 2012 recognizes spatial reference systems based on a number of different reference
ellipsoids, which best approximate the geoid at different parts of the earth. Table 1-1 lists the
properties of some commonly used ellipsoids that can be used.
Table 1-1. Properties of Some Commonly Used Ellipsoids
Ellipsoid Name Semi-Major Axis (m) Semi-Minor Axis (m) Inverse Flattening Area of Use
Airy (1830) 6,377,563.396 6,356,256.909 299.3249646 Great Britain
Bessel (1841) 6,377,397.155 6,356,078.963 299.1528128 Czechoslovakia,
Japan, South Korea
Clarke (1880) 6,378,249.145 6,356,514.87 293.465 Africa
NAD 27 6,378,206.4 6,356,583.8 294.9786982 North America
NAD 83 6,378,137 6,356,752.3 298.2570249 North America
WGS 84 6,378,137 6,356,752.314 298.2572236 Global
Realizing a Reference Ellipsoid Model with a Reference Frame
Having established the size and shape of an ellipsoid model, we need some way to position that model
to make it line up with the correct points on the earth's surface. An ellipsoid is just an abstract
mathematical shape; in order to use it as the basis of a spatial reference system, we need to correlate
coordinate positions on the ellipsoid with real-life locations on the earth. We do this by creating a
frame of reference points.
Reference points are places (normally on the earth's surface) that are assigned known
coordinates relative to the ellipsoid being used. By establishing a set of points of known coordinates,
we can use these to "realize" the reference ellipsoid in the correct position relative to the earth. Once
the ellipsoid is set in place based on a set of known points, we can then obtain the coordinates of any
other points on the earth, based on the ellipsoid model.
Reference points are sometimes assigned to places on the earth itself; The North American Datum
of 1927 (NAD 27) uses the Clarke (1866) reference ellipsoid, primarily fixed in place at Meades Ranch
in Kansas. Reference points may also be assigned to the positions of satellites orbiting the earth, which
is how the WGS 84 datum used by GPS-positioning systems is realized.
When packaged together, the properties of the reference ellipsoid and the frame of reference
points form a geodetic datum. The most common datum in global use is the World Geodetic System of
1984, commonly referred to as WGS 84. This is the datum used in handheld GPS systems, Google Earth,
and many other applications.
A datum, consisting of a reference ellipsoid model combined with a frame of reference points,
creates a usable model of the earth used as the basis for a spatial reference system.
We now need to consider what sort of coordinates to use to describe positions relative to the
chosen datum.
5
CHAPTER 1 ■ SPATIAL REFERENCE SYSTEMS

There are many different sorts of coordinate systems, but when you use geospatial data in SQL
Server 2012, you are most likely to use a spatial reference system based on either geographic or
projected coordinates.
Geographic Coordinate Systems
In a geographic coordinate system, any location on the surface of the earth can be defined using two
coordinates: a latitude coordinate and a longitude coordinate.
The latitude coordinate of a point measures the angle between the plane of the
equator and a line drawn perpendicular to the surface of the earth to that point.
The longitude coordinate measures the angle (in the equatorial plane)
between a line drawn from the center of the earth to the point and a line
drawn from the center of the earth to the prime meridian. The prime meridian
is an imaginary line drawn on the earth's surface between the North Pole and
the South Pole (so technically it is an arc, rather than a line) that defines the
axis from which angles of longitude are measured.
The definitions of the geographic coordinates of latitude and longitude are illustrated in Figure 1-4.

Figure 1-4. Describing positions using a geographic coordinate system.
■ Caution Because a point of greater longitude lies farther east, and a point of greater latitude lies farther north,
it is a common mistake for people to think of latitude and longitude as measured on the earth's surface itself, but
this is not the case: latitude and longitude are angles measured from the plane of the equator and prime meridian
at the center of the earth.
6