GEOG 583
Geospatial System Analysis and Design

(Spatial) Databases

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 Tools for Spatial Data

IBM Db2 on Cloud offers capabilities for analyzing spatial data; these include the Spatial Extender (in-database functions for row-organized data) and the Geospatial Toolkit (in-application functions that run in a Spark environment).

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.

NoSQL (and Other Database structures)

Commonly used for big data applications, NoSQL databases work with rapidly changing data structures, or semi-structured and unstructured data sources. In traditional SQL databases, the structure and data types are fixed when the database is designed. In NoSQL databases, the data fields are more flexible as data might change over time, but with that flexibility, you lose some consistency. NoSQL databases are generally better suited to store millions of Facebook comments rather than a banking system. The use of this database type is still being developed for spatial applications.

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