Last week, I gave a 30 minute talk on “Doing More with SQL” at MAC URISA 2014. The slides from the talk are available below:
I wanted this talk to be technical, but explain some of the benefits of using SQL to a potentially non-database minded audience. GIS is such large concept that many different disciplines, theories, and software can claim some aspect of the name. While ArcGIS is the market leader and has a focus on vector (often database derived) data, it surprises me that many ArcGIS users have very little exposure to SQL. If they do, it’s through an application like Microsoft Access. This may be due to the fact that historically, you needed ArcSDE, a middleware application from ESRI, to use a RDBMS with ArcGIS. At ArcGIS 10.2, you can connect to a PostgreSQL database with PostGIS installed and store and edit spatial data. You’ll still need to purchase ArcSDE from ESRI (it comes with ArcGIS for Server) if you want additional functionality, such as versioning, topology, and network analysis.
With relatively little additional effort, a small GIS shop can add a significant amount of functionality to their GIS workflow by incorporating a spatially-enabled DBMS into their work. PostgreSQL is a robust, open source database system that has great spatial data support through the PostGIS extension. EnterpriseDB offers Windows, Linux, and Mac OS X distributions of PostgreSQL with an “Application Stack Builder” that makes it very easy to install the database and additional plugins and applications, like the PostGIS spatial extensions and the PgAdmin administration tool. Granted, jumping from a collection of File Geodatabases (no one is still working in Shapefiles, right?) into an entirely new system is not a jump one should make without careful consideration. The benefits of a managed spatial data storage system like PostgreSQL+PostGIS will outweigh the pains in moving to such a platform, especially if you have a need to edit data concurrently. Even if you don’t shell out the funds for ArcSDE, which would give you versioning capabilities, the additional functionality & ability to perform some GIS analysis and maintenance within the database should help motivate you towards using a dedicated database system to manage your spatial data.
Over the next few weeks, I am planning on releasing more tutorials on using SQL and a spatially-enabled database (namely PostgreSQL) to perform common GIS tasks. The tutorials will be geared towards those familiar with ArcGIS but unfamiliar with SQL. I welcome any suggestions and/or feedback on topics I should cover, data to use, etc.