Geography Reference
In-Depth Information
The Metadata Tables
All OGC-compliant GIS databases must support two core metadata tables called
geometry_columns
and
spatial_ref_sys
. Most GIS-enabled software will use the existence
of these tables to determine if it is talking to a genuine GIS database system. If these tables
don't exist, the software will often exit.
A good example of this was with early versions of MySQL where the table names were
reserved by the database engine, but did not physically exist as tables. This would cause the
MapInfo application to attempt to create the missing tables, but it would receive an error on
trying doing so, thus preventing the database from being used correctly by the software.
The
geometry_columns
table is used to record which table columns in your database
contain geospatial data along with their data type, coordinate system, dimensions, and a few
other items of related information.
The
spatial_ref_sys
table holds a list of known spatial reference systems, or coordinate
systems as they may be better known. These coordinate systems are what define
geographic locations in any GIS database; they are the glue that allows all the functionality
to work together flawlessly, even with data that may have come from different sources or
been recorded using different geographic coordinate systems.
The entries in the
spatial_ref_sys
table are indexed by a number known as the EPSG ID.
The EPSG, or European Petroleum Survey Group, is a working group of energy suppliers
from the oil and gas industry who confronted a common problem that arose when surveying
the world's oceans for oil reserves: positioning on a global scale. Some companies used one
scale, others used a different scale; some used a global coordinate system, while others
used a local one.
The group's solution was to record the differences between each scale and the information
required to convert from one scale to another reliably without any loss of precision.
Today, every GIS database that claims to be OGC compliant includes a copy of this table to
ensure that data conversions from one system to another are performed with as much
accuracy as possible.
We'll cover the actual coordinate systems a little later in the topic. For now, all you really
need to be aware of is that if the
spatial_ref_sys
table does not exist or has no data in it,
you will be unable to accurately map or make real-world translations of any data you
possess.
Also note that it is possible to save space by removing unnecessary entries from this table. If
your data only ever uses two or three different coordinate systems, it's perfectly acceptable
to remove the rest of the entries to reduce the size of the table. This can be especially useful
when working with mobile devices.
If you only work with data in your own range of values, arguably there can be no data in the
spatial_ref_sys
table at all. I would, however, caution you against removing the table
entirely. As previously mentioned, most GIS software will look for the presence of this and
the
geometry_columns
table to signify the existence of a GIS-enabled database.
