GEOG 583
Geospatial System Analysis and Design

(Spatial) Databases

PrintPrint

Now that we have seen some of the benefits and pitfalls of using a database to store your data, we can start to look at the types of spatial databases that we can consider. The first step to consider is, do you want to use a SQL database or a NoSQL database?

SQL

SQL stands for Structured Query Language and is an international standard defining a language for querying relational databases. While there are issues between implementations of SQL between different vendors, mostly over time and date values, SQL has proved to be very successful. By using SQL, external programs are able to abstract many of the issues of accessing databases and allow the user to choose the database they prefer.

To provide the same sort of standardization for spatial databases, the OGC formalized the Simple Feature for SQL specification. This specification defines the Well Known Text (WKT) and Well Known Binary (WKB) formats for storing spatial features as well as functions for importing and exporting features from/to WKT and WKB. It also defines a set of functions for manipulating and querying geometries.

Relational Databases

PostGIS

A free and open source extension to the popular PostgreSQL database. It provides full simple feature for SQL support. Here is a link to the latest version of PostGIS. PostGIS is the DB of choice for research projects in the Department of Geography at Penn State due to its excellent feature set, high reliability, and free licensing. PostGIS is also the spatial backend for Esri's current ArcGIS Server offering on Amazon's cloud (probably due to the free licensing).

SQL Server (Microsoft)

SQL Server is Microsoft's premier database offering. It is one of the "big three" commercial DBMS along with Oracle and DB2. It often provides the back-end for large Esri installations. It gained spatial support back in 2007 (!).

Oracle Spatial & Oracle Locator

Spatial and Graph are the spatial extensions to the popular, powerful, and expensive Oracle database. They provide simple features for SQL compliance for types and features, but not import and export of WKT or WKB. Oracle was first out there with good spatial support, but it seems to me like the rest have caught up.

IBM DB2 Spatial Extender

The DB2 Spatial Extender allows you to store, manage and analyze spatial data in IBM's DB2 database system. The spatial extender implements the OGC simple feature specification.

SpatiaLite

SpatiaLite is a spatial database based on the popular lightweight SQLite package, a self-contained serverless transactional SQL database engine. SQLite is open source (public domain) software, which has helped its uptake. SpatiaLite is compliant with the OGC simple feature specification (except for renaming union to gunion - due to a reserved word clash). Some commentators have predicted that SpatiaLite may finally replace the Shapefile as a data exchange format.

MySQL

MySQL claims to be the world's most popular database, but its spatial extensions are only a subset of the Simple Feature specification. All of the functions that calculate relations between geometries are implemented using bounding boxes, not the actual geometries. This may be acceptable to your application or not. Only you can decide.

Other Databases (NoSQL)

In recent years, there has been a trend to using so called “NoSQL” databases. This is a loosely related set of databases that are non-relational, distributed systems that often don't provide an ACID guarantee. ACID stands for atomicity, consistency, isolation and durability which are all great things to have when you are considering a banking system (you'd hate for money to disappear from your account but not arrive at the payee's account) but are considered unnecessary for some modern applications such as Facebook comments.

There are, as yet, no full spatial extensions for these databases, but a number provide a variant on the geohash solution. Geohashing is a way of converting a spatial location into a single text key (see the Geo Hashing wiki and Google Maps API). The other solution adopted by some of these systems is to use a spatial SQL database as a back end for the spatial queries.

Currently, none of the systems I have found seem to be able to handle anything more complex than a bounding box or proximity search. So while they currently seem to be useful only for very limited applications, that may change in the future.

NoSQL Database and its Spatial Extension
NoSQL Database Spatial Extension
couchDB GeoCouch
MongoDB Spatial Mongo
Google App Engine GeoModel, GeoDataStore

 

Subscribe to Comments for "(Spatial) Databases"