An Introduction to SQL for ArcGIS Pro® for Esri Users

You can perform all of your GIS analysis in SQL for ArcGIS Pro using a single command window.  But, the product offers so much more. 

In the previous lecture, you saw a lot of the GIS technology that is in SQL for ArcGIS Pro.  But, we aren't really going to use it as a standalone GIS.  Rather, we are focusing our attention on the SQL portion.  So, virtually all of our time is going to be spent using the query component.  This short video is going to show you how to navigate around the query component, so you'll want to become familiar with it before you start writing SQL queries against your geodatabase. 

This is sort of a best practices discussion.  While you can perform all of your GIS analysis in SQL for ArcGIS Pro using a single command window, the product offers so much more.  In this lecture you'll see how to write SQL queries from scratch, leverage drag and drop features within the GUI, and utilize dialog based transforms allowing SQL for ArcGIS Pro to do the heavy lifting. 

In addition, we'll discuss special functions to improve performance as it relates caching data, running queries locally or directly in the geodatabase, and special parallel processing functions.  This is more of an introduction to the concepts, and we'll start implementing these ideas throughout the training. 

In this course you are going to get enough instructing and practice to where you can start to stand on your own two feet, and improve your workflow with SQL for ArcGIS Pro.  Standing is one thing.  Sprinting is something entirely different.  Just like anything else: skiing, golf, swimming, guitar, etc., to really master things, you're going to have to spend lots of time perfecting what you do.  But, along the way, there are great resources to help you get there.  In this lecture, I want to show you three resources: the help manual, the user community, and external sources. 

We've spent a lot of time getting up-to-speed on what SQL for ArcGIS is, what SQL is, and all of the benefits.  At this point, I hope you are excited about starting to write SQL queries with your Esri geodatabase.  So for the next few hours, we are going to roll up our sleeves and start writing queries and discussing how they work.  You'll work alongside me as you begin to better understand, and eventually become quite good at writing SQL queries. 

You are going to use the SELECT statement 99% of the time.  This lecture is going to introduce the basic select statement and some interesting parameters related to it, such as the conditional statements like WHERE, IN, <, >, =, and also how to modify the presentation to the user by using ORDER, FETCH, etc.  You'll discover that writing even the most basic SQL queries are far more flexible than using a GUI in a traditional GIS setting. 

Also, we'll look at special ways we can work with numeric, character, and date fields, and ways to even combine dissimilar data types together.   

If you can do it in a spreadsheet, you can do it in SQL.  So, if you need a quick answer like determining the ratio of sale price to assessment value for residential properties in the month of July in 2014 - no problem!  SQL does that in one easy-to-write query.  That's something that is very difficult to discover in a traditional GIS GUI, or even an Excel spreadsheet.  And the best part of it is, you don't have to create new fields for calculations or export data to different formats.  Just write the SQL on-the-fly, and you'll get your answer.

Aggregation queries are one of the more powerful functions in SQL.  If you've ever written for loops in python or another language, you'll immediately see the benefit (and simplicity) that an aggregate function like GROUP BY offers.  And, if you consider the effort you'll have to make to accomplish the same thing inside a GIS GUI, you'll be totally sold on using SQL. 

In this lecture, we'll slice and dice some of the parcel data we've been working with to aggregate our data for different property classes, and different mathematical statements.  This is definitely a lecture you don't want to miss. 

We've selected records that meet certain conditions.  We've performed calculations on those records, and we've aggregated the results.  In this lecture, we'll expand some of our special functions for Date fields to show you how all of it can be rolled together into a single query.  As you work through this, keep in mind the sheer headache this could cause using the standard GUI in the GIS.

Performing table joins is one of the more powerful features of SQL.  In a traditional GIS, you use a wizard/gui to join two tables together. But, with SQL, the options are endless: you can join all the rows into two tables based on a common identifier(s), you can link selected records from both tables, and you can perform updates and analysis based on the links.  Also, if the fields you want to relate have different data types, no problem - just CAST the column to the appropriate data type.  No need to create new columns and recalculate the field in the correct format.

This lecture is going to go through a number of scenarios with some messy and complex soil databases.  You'll be relating two, and three tables together, while also performing on-the-fly calculations in the joins.  This is the stuff that's really hard in a GIS GUI, but really easy in SQL. 

If there's one thing I hate about the standard GIS GUI's, it's updating data in bulk.  But, SQL was really designed for this, and allows you to perform all kinds of updates and inserts into databases in all kinds of creative ways.  And like all the SQL we've been exploring, when the query is written, it reads like common English.  We're getting more complex, that's for sure.  But, that complexity is still way easier than programming, and something you'll be able to use in your Esri workflow.

Before we jump into writing spatial SQL, this lecture is going to set some ground rules for applying best practices in working with the spatial components for SQL for ArcGIS Pro.  Given the newness of the product, there are some things that aren't magically handled in the background.  So, we'll learn how to deal with different datatypes and coordinate systems, and some of the internal tools to make life easier.  I fully expect that a number of these issues will go away with more user input, but for now, these tips are going to make reading and writing into geodatabases a lot easier. 

We'll also explore different options for speeding up our processes, and even using parallel processing in SQL for ArcGIS Pro.

We are going to now start writing more queries related to containment, adjacency, and overlap.  What you'll notice is that if you can write the query for contains, you can write the query for intersects, and you can write the query for touches.  The queries will all look similar which makes things really easy.  The key now is to search the help file to discover the differences among the different functions.  So, this lecture will also spend a short time diving into the help manual. 

In this lecture we are going to take a look at different ways we can perform spatial queries based on distances.  If you are starting to think that these queries are all starting to look similar, you'd be right.  And, that's a good thing because it means you can adapt and change your queries, but the general structure will continue to be familiar. 

Life comes at you fast.  In this lecture we are going to explore how SQL can be used when there's no time to waste.  We will present an emergency situation where we have to quickly scramble to supply answers after major flooding. 

John Wayne once said

life is tough, but it's tougher when you're stupid

I'd amend that and say

responding to emergencies with GIS is difficult, but it's more difficult when your GIS software is cumbersome to use

You'll see how SQL is built for this situation.

So what percentage of the black, white, and total population in Maryland are within 1, 3, and 5 miles of a COVID-19 vaccination center in Maryland?  Can we quantify these results by county?  Sounds fairly easy, right?  But, like most real-world scenarios there are minor and major complications that make solving this problem quite difficult with a traditional GIS.  But, with SQL we can navigate those complications quite easily.  Here you'll see a perfect example of where you want to plug your SQL for ArcGIS Pro into your ArcGIS Pro solution.