Advanced SQL Workshop FOSSGIS 2019

In this workshop you will analyze time series data about free parking places with some advanced SQL recipes. It covers the following SQL features:

• DISTINCT ON
• IS DISTINCT FROM
• LATERAL JOIN
• FILTER
• GROUPING SETS/ROLLUP/CUBE
• BOOL_OR/BOOL_AND
• WINDOW Functions

Background

This workshop has been held at the FOSSGIS conference 2019 in Dresden. The initial idea was to present some advanced geo queries that can be done with PostGIS. But judging from my own experience I realized that it weren't hundreds of PostGIS functions that lifted my spatial database skills to another level. It was SQL itself. It took me a while to fully understand more advanced concepts such as window functions. You certainly don't need them everywhere. But the more options you know the faster you will solve complex tasks with SQL.

I decided to leave out geomerty-related queries. So that the participants can better focus on SQL itself. I prepared some examples, nevertheless, but only as an encore (to be found in the SQL file). I believe that most of the PostGIS users know the basic tricks anyway: Spatial JOINs, GiST indexes, kNN-queries with <->, ST_Dump, ST_MakeValid ect. I hope that after this workshop they will be able to combine it with modern SQL to create new exciting forms of data analysis. It doesn't have to be R or Python all the time when it comes to data science.

Data

Data is provided by the Open Data initiative ParkenDD in Dresden. A data dump can be downloaded here.

Prerequisites

PostgreSQL min. v9.6, better 11. PostGIS extension is not a must (except for the mentioned encore queries)

How to start

I have uploaded the original workshop material, but it contains german comments. For the english version, I tried something else: a SQL notebook. I found out that this can be done with franchise.

1. Simply download franchise and open the provided HTML file
2. Create a Postgres database and connect to it from franchise
3. The DB setup and data import is explained in the notebook