Spatial in the World

Spatial in the World
All of the examples shown so far in this section have been for the GEOMETRY data type,
so I thought a good way to round out this chapter is to see some practical use of the
GEOGRAPHY data type. I have loaded several sets of GEOGRAPHY data into a database
called NewYorkCity, which contains the spatial data and related metadata for the roads and
an abridged set of key landmarks and locations in Manhattan. In this particular set of data,
roads, landmarks, and locations are represented as one or more shapes, so, for example,
Columbia University is actually spread over two rows of data, as shown by this query.
SELECT LandmarkID, Landmark.ToString() AS LandmarkText, Name
FROM dbo.LANDMARK
WHERE NAME = ‘Columbia University’
This SELECT statement returns the following data.
LandmarkID LandmarkText Name
36 POLYGON
((-73.960162 40.808155,
-73.961079 40.806824,
-73.963956 40.808001999999995,
-73.962054999999992 40.810631,
-73.959223 40.809402999999996,
-73.95966 40.808794999999996,
-73.960162 40.808155))
Columbia University
352 POLYGON
((-73.961079 40.806824,
-73.961603 40.806166999999995,
-73.96204 40.805558999999995,
-73.964894 40.806754,
-73.964392 40.807410999999995,
-73.963956 40.808001999999995,
-73.961079 40.806824))
Columbia University
So what can we do with this data? First, let’s see how big the university actually is.
SELECT SUM(Landmark.STArea()) AS LandmarkArea
FROM dbo.LANDMARK
WHERE NAME = ‘Columbia University’
SELECT LandmarkID, Landmark.ToString() AS LandmarkText, Name
FROM dbo.LANDMARK
WHERE NAME = ‘Columbia University’
LandmarkID LandmarkText Name
SELECT SUM(Landmark.STArea()) AS LandmarkArea
FROM dbo.LANDMARK
WHERE NAME = ‘Columbia University’