Spatial Databases

Spatial Databases

Now that we have reviewed some of the core characteristics of spatial data storage and handling, we can start to look at the types of spatial databases that are in common use today. One key consideration is to choose between a Structured Query Language (SQL) database structure or a NoSQL database structure. 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.

SQL Options

SQL 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 allows users 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.

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 back end for Esri's current ArcGIS Server offering on Amazon's cloud.

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 an early proponent of explicit spatial support, but others have quickly caught up in recent years.

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. 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 for your application or not.

NoSQL (and other Database Structures)

Commonly used in big data applications, NoSQL databases work with rapidly changing data structures, or semi-structured and unstructured data sources. In NoSQL databases, flexibility with respect to data fields can provide advantages in terms of adapting to changing input streams, but with that flexibility you lose some consistency. NoSQL databases are generally better suited to store things like millions of Facebook comments, as opposed to records in a banking system. The use of the NoSQL database type is not widely prevalent for spatial applications, but new examples of use cases are emerging.