Using new location-aware data types in SQL Server 2008
As affordable, GPS based devices start becoming commonplace in the consumer space, providing solutions that understand the importance of location becomes more and more important. Similarly, in a business setting, location can play a key role in logistics, manufacturing and support, to name a few.
Storing location data is, in its simplest form, a matter of storing the latitude and longitude of the location. Using simple floating point fields allows you to store this information, and for simple applications this can be enough. However, if you want to create more advanced applications, or use geometric algorithms to locate data points, then you must code the necessary routines yourself. For instance, how would you go on and find all stored locations within a 10 mile radius of a given spot?
In such a situation, wouldn't it be nice if the database itself could handle some of the work for you? This is what the latest release of SQL Server aims to do. With SQL Server 2008, you will have new built-in support for location based data types and supporting geospatial features. Next you will learn how these new data types work.
Two types, two models
SQL Server 2008 supports two similar but still distinct data types called geometry and geography. If simplifying things a bit, both are able to store x and y values and support a set of operations to do calculations based on these values. However, what separates these types from each other is that geometry is based on calculations on a straight surface (the "flat Earth" model), but geography does calculations based on the fact that Earth is round.
This distinction is important to remember, as for example taking the shortest route can be fundamentally different depending on whether you calculate Earth to be flat or round. This can easily be seen in shortest routes in inter- continental flights (Figure 1). In fact, SQL Server can base its calculations on dozens of different roundness and coordinate calculations. Such support is needed because traditionally different countries have had slightly different ways of calculating the shape of Earth. These differences are minute, but nonetheless can affect your results if you are not careful.
Click here for larger image
Figure 1. Which route is actually the shortest one?
SQL Server uses a notion of Spatial Reference Identifiers (abbreviated as SRID) which you must use when using either of the data types. However, for the simpler geometry data type, the SRID value is ignored, and can be set to zero. For the geography type, you have to explicitly set the SRID value. SQL Server prefers to use a coordinate system called WGS 84 (World Geodetic System 1984), which is assigned to the SRID magic value 4326, which itself is just an arbitrary number. A list of all the supported SRIDs can be found from the
sys.spatial_reference_systems view in the Master database (Figure 2).
Click here for larger image
Figure 2. SQL Server supports many different SRIDs.
Nowadays, the importance of different coordinates has diminished, as the WGS 84 system is used globally in the GPS system (Global Positioning System). Thus most online mapping sites are based on the same reference system.