SQL Course of the Month – PostGIS

It's summer, and it's travel time ... at least it would be under normal conditions. The pandemic is still going on, but that doesn't stop us from dreaming about distant trips and visits to beautiful places. For now, most of us will have to do it on a map. Did you know that databases can store geographical data? With interactive maps, if you find a nice summer house on the internet, you can accurately track it and plan the best route there. PostGIS is behind everything—what exactly is it, and why is it worth learning? I asked Agnieszka Kozubek-Krycuń, Chief Content Officer at Vertabelo about this. Her answer: you should take the PostGIS interactive course at LearnSQL.com.

What Is PostGIS? Help me Understand—I Am not an IT Expert.

PostGIS is a geospatial extension for the PostgreSQL database. It is an add-on for a PostgreSQL database that you install separately, allowing you to store and process geographical data in PostgreSQL.

Databases have different data types: numeric, text, date and time, etc. PostGIS adds new data types to store geographical/spatial data. For example, you can store geographical coordinates (latitude and longitude) for points on the map and boundaries of areas (for example, areas for buildings, cities, and states) with PostGIS. It extends PostgreSQL with new functions to compute distances and areas, compute the intersection of two areas, and check if a point is within a given area, among others.

Geographical data sets are usually large, often in hundreds of gigabytes or sometimes even terabytes. PostGIS allows you to store and process these large data sets efficiently, and it comes with spatial indices specifically designed to process geographical data.

An operation performed using PostGIS that takes several seconds may take hours without this add-on, and some operations may not be completed at all. The difference is clear—we quickly conclude that PostGIS is indeed worth the effort.

So, where can we find PostGIS? Do we use it day to day in some apps?

PostGIS is used in many different GIS (Geographic Information Systems) software. A well-known piece of software that uses PostGIS is ArcGIS, a popular commercial mapping and analytics platform. As an example, a well-publicized visualization of the COVID-19 pandemic prepared by John Hopkins university runs on ArcGIS.

PostGIS is used also by OpenStreetMap, an open-source map of the world. PostGIS can also be a data format for QGIS, an open-source mapping software.

Source: www.openstreetmap.org

Sounds Encouraging, but why Should I Learn It?

PostGIS provides you with so many functionalities that it is indeed worth the effort to learn. PostGIS queries allow you to answer questions such as:

• What is the size of this state?
• How close is the nearest shop?
• What is the distance between the place of registration and the place of first purchase?

Even if you don’t use PostGIS, it is helpful to know what geographical extensions have to offer. The exact syntax of your extension may not be the one used in PostGIS, but the general concepts are all in common.

Preparing your reports in SQL gives you more flexibility than using ready-made GIS software. This includes saving your queries so that you can run them again to make reproducible reports.

What Will I Learn in This Course? How Long Does it Take?

Our PostGIS interactive course teaches how to write SQL queries that process geometrical and spatial data. In PostGIS there is a difference between geometry and geography. Geometry is a data type that supports regular 2D geometry that you learned in school. It’s fast and good enough for small areas, like a single city. Geography is a data type to store geographical data. When you work with large areas (like a country or a continent), regular 2D geometry is not accurate enough. As you probably know, the Earth is round. You must use spherical geometry for your computations when you process geographical data. Geography data type is slower than geometry but is more precise.

We start by introducing geometry data type and the common shapes: points, LineStrings, and polygons. Then, we show functions that check the relationships between geometries—for example, whether they are touching or overlapping, or whether one shape is within a given distance from another shape. We also teach functions that create new geometries from existing ones, such as creating a union or an intersection of multiple geometries and finding a centroid (geometrical center) of a geometry.

But... don't be scared! It won't be like school; you won't have to stand at the blackboard. You will learn everything from 148 logically arranged, practical interactive exercises. You will learn to work with geography data types and its functions. We estimate that it takes about 12 hours to complete the course.

Why Should I Select LearnSQL.com to Learn PostGIS?

The course is interactive and fun. You get to work on exercises about local attractions in San Francisco—railways, tram stops, and sights—and on exercises about U.S. states and their capitals. Then, you can see the results of your SQL queries on a map.

It is true that there are several tutorials on the web, and you can, of course, find some interesting PostGIS books on the market. However, our course is the only interactive PostGIS course that I know of.

Who Should Take This Course?

I recommend this course to data analysts who work with geographical data. Preparing your reports in SQL gives you more flexibility than using ready-made GIS software. You can save your queries and run the same queries again to make reproducible reports.

I also recommend the course to all PostGIS fans; the members of this community are special. PostGIS, like PostgreSQL, is open source, so it is free, and developers around the world are always making it better. What more could you want?

I Am a SQL Beginner. Will I Be Able to Complete the PostGIS Course?

It is recommended that you know the basics of SQL, including the JOIN and the GROUP BY clauses. Otherwise, it will be difficult for you to do the exercises. Don't know if you are at the right level? Take the short quiz at the very beginning of the course—it will help you determine whether you're up to the PostGIS adventure ahead or if you should first complete some of the other courses we offer.

If at any point in this short quiz you get the feeling that it's too difficult for you, try our SQL Basics course first.

At the end of Other LearnSQL.com Courses, I Received Certificates That I Could Post on my LinkedIn Profile. Will I Get one for This Course?

Yes! You will get a certificate after completing the course. We encourage our users to display these certificates on their CVs and in their LinkedIn profiles. You can also show off your new accomplishment on social media.

How Did This Course Come About?

The idea for creating a PostGIS course came to us after preparing custom SQL courses for Uber. These included topics on processing geographical data in SQL.

We thought that processing geographical data in a database was an interesting topic to cover in a SQL course. You can visualize your query results on a map; seeing real world objects on a map is a lot of fun.

Finding the geographical data suitable for the course was a challenge. We wanted to find data for real-world objects to make the course realistic. On the other hand, we wanted to keep the data sets small, by showing the data on the map to avoid sending gigabytes of data back and forth between the server and the user browser. In the end, we found data for local attractions in San Francisco, as well as data about the boundaries of U.S. states and their respective capitals.

I remember we had to redo a significant portion of the course. The first version of the course, which we did not release, was very theoretical and not a lot of fun.

We want our SQL courses to be realistic. So, we reworked the course to make the tasks more business-like, and this is the version we have published.

Speaking of Geographical Data, Do you Have a Place you Would Like to Go?

I’d like to go here: 32°38'00.0"N 16°56'43.3"W (32.633336, -16.945350):