“Doing more with SQL” talk at MAC URISA

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.

This entry was posted in Conferences, PostgreSQL, Technology and tagged , , , , , , , . Bookmark the permalink.

3 Responses to “Doing more with SQL” talk at MAC URISA

  1. Its quite comforting when you see other people from different countries have the same ‘concerns’ with you. “it surprises me that many ArcGIS users have very little exposure to SQL”. I’ll second that! So much in fact, that I am preparing a presentation for the upcoming HellasGI conference on the need for GIS users to have some basic technical knowledge so as to know what all these buttons in the UI really do!

    (And also hopefully learn some basic db design principles so they don’t create a parcel owners table with fields like owner_name1, owner_surname1, owner_name2, owner_surname2,…… ad infinitum!)

    • John Reiser says:

      Pano, good luck with the conference! Please tweet/reply if you end up sharing your slides after the conference. I’d love to see what you’re doing. I’m hoping to do more to show ArcGIS users how do perform common tasks from within the database with some articles and webinars over 2015.