Learning Open Source GIS: Spatial SQL w/ Postgres/PosGIS

Before we get started, you'll want to know what this course is all about, and in this lecture we are going to go over the goals and objectives, along with the expectations. 

Make sure to download the files in the downloadable materials section.  This includes all of our data, along with the lecture notes so that you can copy/paste the SQL commands. 

In this lecture you'll download postgres and install it on your local computer.

Simply follow the video to download the software, and remember - Postgres will require the PostGIS extension included with the Stackbuilder.  Also, please make sure to note the username and password you enter (we recommend using postgres / postgres respectively. 

In this lecture you'll download QGIS and install it on your local computer. The current directory for downloading QGIS is located at https://qgis.org/en/site/forusers/download.html

Simply follow the video to download the software, and remember - QGIS is big! This is going to take some time to complete.

--> MAKE SURE TO DOWNLOAD THE TOMPKINS.ZIP FILE IN INTRODUCTION SECTION --->

We're not going to use some small, lame, "everything works perfectly" , training data set.  That's not realistic.  When you take a training course with small, perfect data, you won't have the necessary skills to apply your use of Postgres and PostGIS in the real world.   So, we'll use a real data set of raster, vector, and attribute data from Tompkins County, NY.  This is data for a 900 square mile county, and includes hundreds of megabytes of data.   When you are done with this course, you'll have confidence to know that you can work with your own data.

To better understand the tasks ahead of us, we'll have a look at our data so that you understand the context of the different types of GIS processes we'll perform with Postgres.

Make sure to download the tompkins.zip file and place that in a directory called c:trainingtompkins  Like all of the courses in the Learning the FOSS4g Stack, we'll introduce you to the data set we'll be using. 

Because Postgres is a real enterprise class database, it doesn't work like a small time windows based application. This makes it very robust.  But, it also makes it a little more complicated at the outset.  The data is actually stored within the database, and accessible via an IP address (we'll be using 127.0.0.1).  The cool thing is, of course, is if you have a real server, you can expose that IP address to the outside world, and have users access the data remotely. 

So, naturally, there will be some preparatory work to get this ready.  In this lecture, we'll load the data, and then check to make sure everything worked.

If you try and open the pg_qgs.qgs or pg_qgs.qgz file, you may get an error. That is most likely due to the fact that I created the .qgs file using a different port connection than what you set up.  This can be frustrating.  But, it is an easy fix.  You simply have to modify the port number in the qgs file.  This short lecture will show you how to do that.

One our data is in the database, we can start accessing it with different applications.  For this class, we'll use QGIS to access the  data.  However, we could just as easily have used ArcGIS, ArcPro, Manifold GIS, MapInfo, or a whole host of other products.  So, for this lecture we'll connect QGIS to our database and start visualizing the data. 

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.

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.