By Jessica Kane
If you’ve worked with shapefiles before, you know that keeping them organized is difficult. Each shapefile contains the information for one data layer (e.g. roads or streams), but can be up to 12 separate files. At the very least a shapefile requires three files (.shp, .shx, .dbf). (Technically the projection file - .prj - is not required, but if you don’t know the projection of the data, you can’t do much without this one too!) This can get unwieldy if you are working with lots of data layers over multiple projects. As your GIS program or project evolves, at some point you may want to consider using a spatial database to store and manage your data.
What is a Spatial Database?
A spatial database is a database that can store and manipulate data that has a location. It can store points, lines, polygons, etc. and it allows spatial queries by software applications using the database or by a user directly querying the data with SQL. This allows queries such as “Give me all the gas stations within 1 mile of my location” or “Give me all the parks within the city boundary.” Other generic terms for spatial databases include geospatial databases, geographic databases, or geodatabases.
NOTE: Esri, the creator of ArcGIS software, uses the term ‘geodatabase’ for a number of its proprietary data storage solutions including the file geodatabase (.gdb) and personal geodatabase (.mdb) which are single-user files. Esri also offers enterprise geodatabases, which are Esri’s implementation of a multiuser geodatabase and can work with a variety of relational database management systems (RDBMS) including IBM DB2, Oracle, PostgreSQL and SQL Server.
When Should I Use a Spatial Database?
If you are completing a small, one-off GIS project with data that you will only use once or twice, using shapefiles or another geospatial file format is fine. Since you have a limited amount of data you are using, managing those few files is not a big deal. For short-term and small projects you often don’t care as much about managing data changes in the future, so the benefits of using a spatial database don’t make a big difference.
When using and managing geospatial data long-term, you may want to consider using a spatial database. Spatial databases provide you with one location for ALL of your data and an array of other benefits described in the next section. Consider using a spatial database when one or more of the following criteria are true.
Data will be used repeatedly for more than 6 months
Multiple users will be editing the same dataset
Data is being reused across multiple projects
Data integrity is crucial
Data retention is crucial (backups are needed)
Benefits of Spatial Databases
Spatial databases have a number of benefits, some of which will be readily apparent to the novice user while others are unlocked as you build more skills in SQL and database design. The following list are some of the key benefits we experience in our consulting work at Line 45.
Easy to Organize and Maintain as the System of Record: Databases are designed to organize large amounts of data. All of your data will be in one place as the authoritative data source for your organization. You won’t have to worry about random GIS files being saved in different places.
Can be Used with a Variety of Software: Once your data is in a database, you can access that data from a variety of software applications including Esri’s ArcGIS solutions (depending on your license level) and the popular open source desktop GIS application QGIS. In addition to using third-party software applications with your database, you can build your own custom applications (desktop, web or mobile) to view or edit your data - all without having to convert your data to another file format.
Multi-user: Multiple people can access and edit the data in the database at the same time, ensuring that your entire team is working off the same data.
Permissions & Security: You can set permissions on tables or even individual fields to make sure the right people have read/write access to the right data.
Maintain Data Integrity: You won’t have to worry about losing one of those files from a shapefile that makes it unusable.
Back-ups and Versioned Editing: You can back up your database to make sure you don’t lose any data in case of a crash. You can also enable versioned editing so you can revert to a previous version of your data if needed.
Spatial Data Analysis: With some minimal SQL skills, you can do some pretty cool spatial analysis directly with the data (as opposed to using an application). Don’t know any SQL? There are plenty of free tutorials online that will help you get the basics in no time.
Speed Things Up: You can create spatial indices in your database to optimize spatial queries.
Popular Spatial Databases
If you find yourself interested in adopting a spatial database system on your own, there are a number of popular database systems with spatial capabilities:
PostgreSQL/PostGIS: PostgreSQL is free and open source. The PostGIS extension adds spatial capabilities to PostgreSQL. PostGIS is one of the most robust spatial database extensions available.
MySQL: This free and open source solution currently supports the geometry data type and some spatial functions.
Microsoft SQL Server: This proprietary solution has supported spatial types since version 2008.
Oracle Spatial and Graph: Is a separately-licensed component of an Oracle database.
SQLite/SpatialLite: SpatialLite adds spatial capabilities to SQLite. This free and open source solution is different from the others as it is a single file that can be easily copied to other locations (as opposed to needing to export and import backup files as you need to do with other database systems).
This list focuses on SQL-based relational databases. While there are other options out there (such as some No-SQL options), we find that these fulfill most people’s needs best due to their availability, market presence and mix of features.
Getting Started with a Spatial Database
Setting up a spatial database and importing your existing GIS files into it takes a bit more time than just using those files directly with your GIS/mapping application. However, you might be surprised how quickly you can get started with a spatial database. This workshop is a great introduction to PostGIS. And check out this post that walks you through how to set up a PostgreSQL/PostGIS database, import data, and edit it with QGIS.
Feel free to reach out if you have questions or need help with your spatial data management. You can call us at (833) 254-6345 or send us an email at contact@line-45.com.
