Most people don't know the power of SQL.  In this lecture, you'll see what is so amazing about SQL, and the spatial constructs that go along with it.  In fact, you might be tempted to ask if there is some conspiracy out there to keep people from using SQL! (LOL)!  There isn't.  It's just that the GIS field has been held back by some limited thinking.  This lecture will show you just how power, useful, and easy to use SQL is.  After viewing this lecture, you're going to want to continue diving in to using SQL as your geoprocessing tool of choice.

This lecture is going to show you how Postgres and SQL can manage numeric data.  This includes querying, but also performing on-the-fly mathematical calculations - something that is actually difficult to do with classic GIS.

This lecture is going to show you how Postgres and SQL can manage boolean data. This includes querying boolean data, and also performing evaluations on TRUE/FALSE data formats. 

This lecture is going to show you how Postgres and SQL can manage data/time data. This includes querying, date, time, and range calculations on data.  Dates are so important to the things we are trying to query, and it is a shame that most GIS software cannot handle dates in such a robust way.  Fortunately, Postgres has dozens of date/time functions that you can utilize.

One of the main reasons you are probably taking this course is so that you can work with spatial data in a SQL database.  This lecture provides a brief overview of how Postgres handles spatial data types (don't worry, we'll be diving much deeper into this topic later on in the course).  But, this lecture will really make it clear as to why the spatial constructs in SQL should become your go-to tool when performing spatial analysis.

The SELECT statement is the bread and butter of SQL.  And while you'll learn that there is so much more to SQL than the SELECT statement (INSERTS, UPDATES, VALUES, etc.), you are going to want to master the SELECT statement.  So, in this lecture we'll explore a number of ways to utilize the SELECT statement. 

Aggregate clauses like AVG(), SUM(), COUNT(), etc., are really useful tools in SQL. But, they become extremely powerful when combined with the GROUP BY statement.  In fact, a simple GROUP BY statement with an aggregate clause can accomplish what might take many pages of computer code to complete.  Not sure what those last two sentences mean?  Well, check out this lecture, and you'll be really impressed with SQL's simplicity and power.

We are not always going to just want to query data.  Oftentimes, we need to change data, or create new data.  This lecture will show how SQL is used to easily perform data manipulation and editing. 

Oftentimes we'll want more control over the queries that we perform.  In programming languages, this takes the form of if/then and while clauses.  SQL has its own ability to evaluate conditions.  In this lecture we'll explore the use of CASE statements in SQL.  We'll look at basic CASE statements, CASE statements with spatial data, and CASE statements that span multiple tables. 

Like most things in life, knowing where you are is critical in figuring out where you are going.  The same is true for GIS data.  Data stored in Postgres is defined by its geometry and coordinate system.  So, in this lecture we are going to look at the different coordinate system capabilities including how to find, set, and actually reproject data with SQL.

One of the things that most GIS users haven't had to think about are indexes.  Most of the reason for this is because the data we've worked with is small enough that most processes are fairly quick.  But, if you start working with really large data sets, then you will see a noticeable performance hit.  In this lecture, we will go over what indexes are, how they are applied in a spatial and non-spatial context, and also demonstrate the speed one can achieve when utilizing a spatial index.

For this lecture, we'll start looking at spatial operations within PostGIS.  The list is so long that we are basically going to start at the beginning of the alphabet, and move our way through.  We won't hit every command (there are just too many), but we will hit enough of them to give you an idea of how to implement SQL for spatial objects, and also how to find out how to implement others that you aren't as familiar with.

For this lecture, we'll start looking at spatial operations within PostGIS.  The list is so long that we are basically going to start at the beginning of the alphabet, and move our way through.  We won't hit every command (there are just too many), but we will hit enough of them to give you an idea of how to implement SQL for spatial objects, and also how to find out how to implement others that you aren't as familiar with.

There is one problem with the data. A couple of the geometries are non-conforming.

Rather than fixing the data before you get it, this is a good example of what the real world is like – sometimes there is messy data. A user wrote a nice post outlining the fix as follows:

————

Thanks, I actually figured it out already using the following steps:

1) Run the "Fix geometries" function in the QGIS Toolbox on tcparcel, creating a new temporary layer called "Fixed geometries";

2) Export the "Fixed geometries" layer to a new file with format "PostgreSQL SQL dump. Before export set GEOMETRY_NAME under Layer Options to "geom".

3) Import the new SQL dump file into the tompkins database using the Query Editor in pgAdmin 4. That fixed it!

Sometimes there isn't a command do complete an operation.  Rather, it might be a sequence of commands.  This is the case for many quantitative geography problems.  Things like nearest neighbor, mean center, central feature, and so on.  For this lecture, we'll start learning how to implement some classic spatial operations with SQL.  We'll start by looking at the original formulas, and then move on to implementation.  For this first lecture, we'll keep it easy.  But, later lectures will implement some more sophisticated formulas. 

Up until now, we've been using functions that other people have created for us.  But, you can create your own functions that can be called.  This short video will introduce the concept of creating custom functions, and show you how to add more complexity to a basic function, including adding spatial capabilities. 

In this lecture we will learn how to write a short SQL statement to clip our parcels with a particular watershed, then clip that with the floodzones, and finally summarize the spatial and attribute data that are selected.  This is something we also did in our Understanding the FOSS4g Stack: QGIS Desktop, but you'll see that while QGIS was really cool, nothing quite beats the speed and flexibility of an SQL query!