How Coordinates are Referenced in Databases
Earlier, I discussed how coordinates can be used to refer to locations on Earth. While a surprisingly hard problem, ultimately you end up with a named coordinate system (e.g., UTM10N/WGS84 or Irish Transverse Mercator) and move on to working with your data. In many cases, this means storing, maintaining, and analyzing spatial data within a database.
This leads to the question, “How are coordinate systems modeled in spatial databases?” and perhaps “Why do you care?”
(If you exclusively access your spatial data via GIS software, you might answer the second question, “I don’t!” If so, feel free to stop reading here.)
One common approach is to use the SPATIAL_REF_SYS metadata table defined by the OGC. In this scheme, each coordinate system in use is given a unique local ID (called a SRID), which in turn is mapped to a textual description (called “Well Known Text”; see here) and/or a second, centrally registered ID (see EPSG below). Then, each geometry in the database is associated with a SRID: directly, via a second metadata table, or both. (Well Known Text shows up in a number of non-database contexts as well, such as the “.prj” companion to ESRI Shapefiles.)
One of the strengths and weaknesses of Well Known Text is that it’s underspecified. On the plus side, it’s easy to extend – nothing prevents you from adding new projection algorithms, datums, or more. On the down side, not all applications understand all the same terms or refer to the same coordinate systems in the same way. Registries, such as the EPSG, are a big help as they authoritatively assign IDs to commonly used coordinate systems, and these can be referenced in the metadata tables, Well Known Text, and many other contexts. If you’re curious about the many flavors of Well Known Text or the plethora of other ways to describe coordinate systems, it’s worth a visit to spatialreference.org.
Coordinate system metadata is most commonly used by GIS software to present data in context (e.g., with units or layered over a base map), or to correctly overlay data referenced to different coordinate systems (as discussed earlier). However, some databases provide additional capabilities. For example, they might be able to reproject data natively, allowing for spatial queries that compare tables using different coordinate systems, or that transform results to a desired coordinate system for a lightweight client. Also, some databases endow a rich set of spatial operators with knowledge of our round world, answering queries like “How far is New York from Paris?” or “What’s the area of an airspace described by a latitude, longitude, and a radius?” while taking into account a coordinate system’s model of the Earth’s shape.
Learning how your database stores and manipulates coordinate systems will help you ensure that you’re storing this metadata correctly. Further, you may discover powerful new ways to transform and ask questions of your data.
