In modern applications, often you want to show your data on a map, using the physical location. You might also want to show the shape of the objects that your data describes. You can use spatial data for tasks like these. You can represent the objects with points, lines, or polygons. From the simple shapes you can create complex geometrical objects or geographical objects—for example, cities and roads. Spatial data appears in many contemporary databases. Acquiring spatial data has become quite simple with the Global Positioning System (GPS) and other technologies. In addition, many software packages and database management systems help you work with spatial data. SQL Server supports two spatial data types from version 2008:
- The geometry type represents data in a Euclidean (flat) coordinate system.
- The geography type represents data in a round-earth coordinate system.
We need two different spatial data types because of some important differences between them. These differences include units of measurement and orientation.
In the planar, or flat-earth, system, you define the units of measurements. The length of a distance and the surface of an area are given in the same unit of measurement as you use for the coordinates of your coordinate system. You, as the database developer, know what the coordinates mean and what the unit of measure is. In geometry, the distance between the points described with the coordinates (1, 3) and (4, 7) is 5 units, regardless of the units used. You, as the database developer who created the database where you are storing this data, know the context. You know what these 5 units mean, whether it is 5 kilometers, or 5 inches.
When talking about locations on earth, coordinates are given in degrees of latitude and longitude. This is the round-earth, or ellipsoidal system. Lengths and areas are usually measured in the metric system, in meters and square meters. However, the metric system is not used everywhere in the world for spatial data. The spatial reference identifier (SRID) of the geography instance defines the unit of measurement. Therefore, whenever measuring some distance or area in the ellipsoidal system, you should also always quote the SRID used, which defines the units.
In the planar system, the ring orientation of a polygon is not an important factor. For example, a polygon described by the points ((0, 0), (10, 0), (0, 5), (0, 0)) is the same as a polygon described by ((0, 0), (5, 0), (0, 10), (0, 0)). You can always rotate the coordinates appropriately to get the same feeling of the orientation. However, in geography, the orientation is needed to completely describe a polygon. Just think of the equator, which divides the earth into two hemispheres. Is your spatial data describing the northern or southern hemisphere?
The Wide World Importers data warehouse includes the city location in the Dimension.City table. The following query retrieves it for cities in the main part of the USA:
SELECT City,
[Sales Territory] AS SalesTerritory,
Location AS LocationBinary,
Location.ToString() AS LocationLongLat
FROM Dimension.City
WHERE [City Key] <> 0
AND [Sales Territory] NOT IN
(N'External', N'Far West');
Here is the partial result of the query:
City SalesTerritory LocationBinary LocationLongLat ------------ --------------- -------------------- --------------------- Carrollton Mideast 0xE6100000010C70... POINT (-78.651695 42.1083969) Carrollton Southeast 0xE6100000010C88... POINT (-76.5605078 36.9468152) Carrollton Great Lakes 0xE6100000010CDB... POINT (-90.4070632 39.3022693)
You can see that the location is actually stored as a binary string. When you use the ToString() method of the location, you get the default string representation of the geographical point, which is the degrees of longitude and latitude.
In SSMS, you send the results of the previous query to a grid, and in the results pane, you get an additional representation for the spatial data. Click the spatial results tab, and you can see the points represented in the longitude-latitude coordinate system, as you can see in the following screenshot:
If you executed the query, you might have noticed that the spatial data representation control in SSMS has some limitations. It can show only 5,000 objects. The result displays only the first 5,000 locations. Nevertheless, as you can see from the previous figure, this is enough to realize that these points form a contour of the main part of the USA. Therefore, the points represent the customers' locations for customers from the USA.
The following query gives you the details, such as location and population, for Denver, Colorado:
SELECT [City Key] AS CityKey, City, [State Province] AS State, [Latest Recorded Population] AS Population, Location.ToString() AS LocationLongLat FROM Dimension.City WHERE [City Key] = 114129 AND [Valid To] = '9999-12-31 23:59:59.9999999';
Spatial data types have many useful methods. For example, the STDistance() method returns the shortest line between two geography types. This is a close approximate to the geodesic distance, defined as the shortest route between two points on the Earth's surface. The following code calculates this distance between Denver, Colorado, and Seattle, Washington:
DECLARE @g AS GEOGRAPHY;
DECLARE @h AS GEOGRAPHY;
DECLARE @unit AS NVARCHAR(50);
SET @g = (SELECT Location FROM Dimension.City
WHERE [City Key] = 114129);
SET @h = (SELECT Location FROM Dimension.City
WHERE [City Key] = 108657);
SET @unit = (SELECT unit_of_measure
FROM sys.spatial_reference_systems
WHERE spatial_reference_id = @g.STSrid);
SELECT FORMAT(@g.STDistance(@h), 'N', 'en-us') AS Distance,
@unit AS Unit;
The result of the previous batch is shown here:
Distance Unit
------------- ------
1,643,936.69 metre
Note that the code uses the sys.spatial_reference_system catalog view to get the unit of measurement for the distance of the SRID used to store the geographical instances of data. The unit is a meter. You can see that the distance between Denver, Colorado, and Seattle, Washington, is more than 1,600 kms.
The following query finds the major cities within a circle of 1,000 kms around Denver, Colorado. Major cities are defined as the cities with a population larger than 200,000:
DECLARE @g AS GEOGRAPHY;
SET @g = (SELECT Location FROM Dimension.City
WHERE [City Key] = 114129);
SELECT DISTINCT City,
[State Province] AS State,
FORMAT([Latest Recorded Population], '000,000') AS Population,
FORMAT(@g.STDistance(Location), '000,000.00') AS Distance
FROM Dimension.City
WHERE Location.STIntersects(@g.STBuffer(1000000)) = 1
AND [Latest Recorded Population] > 200000
AND [City Key] <> 114129
AND [Valid To] = '9999-12-31 23:59:59.9999999'
ORDER BY Distance;
Here is the result abbreviated to the twelve closest cities to Denver, Colorado:
City State Population Distance
----------------- ----------- ----------- -----------
Aurora Colorado 325,078 013,141.64
Colorado Springs Colorado 416,427 101,487.28
Albuquerque New Mexico 545,852 537,221.38
Wichita Kansas 382,368 702,553.01
Lincoln Nebraska 258,379 716,934.90
Lubbock Texas 229,573 738,625.38
Omaha Nebraska 408,958 784,842.10
Oklahoma City Oklahoma 579,999 809,747.65
Tulsa Oklahoma 391,906 882,203.51
El Paso Texas 649,121 895,789.96
Kansas City Missouri 459,787 898,397.45
Scottsdale Arizona 217,385 926,980.71
There are many more useful methods and properties implemented in the two spatial data types. In addition, you can improve the performance of spatial queries with the help of specialized spatial indexes.