Last month, I discussed how databases keep track of coordinate system metadata, providing the necessary glue to associate stored geometry with known locations on earth. Knowing where something is, is only the beginning; the next step is extracting value from your data though query and analysis. In general, spatial databases provide a rich set of functions and capabilities to support this goal.
Once again, standards are a big help; the OGC and ISO have defined a model for geometry along with a set of spatial predicates which are implemented by the major spatial databases. Jason Follas posted a nice series (4, 5, 6) on spatial predicates in SQL Server Spatial, with pictures, around the time it was released. The PostGIS documentation has a few example queries which help give a sense of what’s possible. Some products provide capabilities beyond the standards, and this example from Simon Greener demonstrates some of the complicated problems that can be solved in-database when these are pushed to the limit.
Some common use cases for spatial querying include:
- Finding data in an area of interest (e.g. to draw a map within window extents)
- Finding features near or adjacent to other features (e.g. homes near schools)
- Overlaying different layers (e.g. associating parcels with zoning areas)
- Joining non-spatial tables (e.g. looking up details about a road using its id)
- Finding or ordering features by area or length (e.g. big lakes or long trails)
Let’s consider a concrete example. Imagine the goal is to query for property parcels within 5 km of a set of airports, and then join the result to a non-spatial table by parcel id to add some additional attributes. This problem involves three sets of data: the airport boundaries, the parcels, and the additional attributes. If these are all managed by one database, it’s straightforward to write a query to produce the desired result.
Sometimes, however, your data isn’t all available in one place. For example, the airport boundaries could be stored in an ESRI Shapefile, the additional attributes in a CSV text file, and the parcel data in a spatial database. FME provides tools to do many of the same querying and spatial transformation tasks on heterogeneous inputs like these, while off-loading work to the database where possible.
Spatial databases play an important role in computer mapping and GIS: They not only provide robust, scalable storage for spatial data, but also tools to transform, synthesize, and query it.
Paul Nalos
