QGIS & PostGIS, both have something in common. Can you take a quick guess? Yeah, you probably guessed right. It's the GIS in their names. Does this suggest that they are both GIS software? Well maybe or maybe not. What I do know is that oftentimes, these duo are enough for most of your analysis needs. Let's dive deeper and learn more about them in this article.
Contents
What is PostGIS?
Why Use PostGIS?
PostgreSQL/PostGIS Installation
QGIS Installation
Connecting QGIS to PostGIS
Uploading Shapefiles to PostGIS with the Shapefile Loader
Uploading raster to PostGIS with raster2pgsql & psql
Resources
What is PostGIS?
Before diving into PostGIS, I would like us to quickly scratch the surface of databases world. Databases are systems for storing, retrieving and sometimes performing analysis on structured data. It has three major components;
Functions: These are operations we can perform on the data stored in a database.
Data Types: These are the kinds and format of data that can be stored in a database.
Indexes: These are the faster actions and ways to retrieve data from the database.
Let's relate the whole system described above to our local banks. We use our banks to store and retrieve our money (database). There are some acceptable currencies we store in the bank e.g. USD,EUR,NGN e.t.c (Data Types), also there are some activities we can perform on our money. e.g. transfer, exchange, deposit, invest etc(Functions) and finally there are faster ways we can retrieve our money e.g. Point of Sales(POS), Automated Teller Machines(ATMs), Online Banking etc (Indexes).
There are two types of databases--relational(stores data in tabular format i.e rows and columns) and non-relational(stores data in a document based format i.e non-tabular, often called No SQL) and the common laguage used for communicating with the database is the SQL(Structured Query Language).
Just like databases we have three major components for PostGIS:
Spatial Data Types: This refer to shapes such as point, line, and polygon;
Spatial Indexes and Bounding Box: It is used for efficient processing of spatial operations.
Spatial Functions: They are used for querying of spatial properties and relationships.
The combination of spatial data types, indexes, and functions provide a flexible structure for optimized performance and analysis.
PostGIS is an extension to a popular, powerful and free open-source relational database management system called PostgreSQL. It turns the PostgreSQL Database Management System into a spatial database by adding support for the three features described above: spatial types, indexes, and functions. The open-source nature of PostGIS gives it the flexibility to use several other open source software like Geoserver, QGIS etc. PostGIS store and manipulate spatial objects like any other object in the database.
Why Use PostGIS?
Free
Paul Ramsey, the co-founder of PostGIS calls it a GIS without a GIS :)
It's easy to install( CREATE EXTENSION POSTGIS;)
It's can be used for enterprise applications
PostGIS can connect with several clients e.g. QGIS, Geoserver
and lots more...
PostgreSQL/ PostGIS installation
This video demonstrates the installation of PostgreSQL 13.1, PGAdmin 4 and PostGIS 3.1 on a windows 10 (64bit) machine using the software installation file downloaded from the official website. As a dark mode fan, I went ahead to customize my PGAdmin 4 to support dark mode(who don't like dark mode anyway?). If you have them installed already you can skip this or watch, you might probably learn a new thing.:)
Video
Useful Links
QGIS Installation
This video demonstrates the installation of QGIS 3.16 on a windows 10 (64bit) machine using the software installation file downloaded from the official QGIS website. The installation is very straightforward, thanks to QGIS contributors for the well documented website so If you want to install on another operating system, I bet you won't face any issues if you follow the documentation keenly. That was what I did :). If you have QGIS installed already you can skip to the next section.
Video
Useful Links
Uploading Shapefiles to PostGIS with the shapefile loader
PostGIS is not so useful if we stop at the installation and since it's a spatial extension to PostgreSQL I demonstrated in this video the uploading of a vector layer(shapefile) to PostGIS using the Shapefile loader that came installed with it and I also did the visualization within PostGIS. Do you know you can visualize data in PostGIS? Well, Check the video to see that.
Pre-requisites
PostGIS installation (See above)
Video
Useful Links
Connecting QGIS to PostGIS
This video demonstrates how to connect to PostGIS using the browser panel in QGIS.
Pre-requisites
PostGIS installation(See above)
QGIS installation( See above)
Video
Useful Links
Uploading raster to PostGIS with raster2pgsql & psql
This video demonstrates how to upload a raster data into PostGIS using the raster2pgsql from our command line.
Pre-requisites
PostGIS installation(See above)
QGIS installation( See above)
Video
Conclusion
In this article we looked into PostGIS and QGIS. We installed them on our computer and went ahead to upload data into PostGIS from within QGIS and the command line. I hope you found it helpful.
Resources
Curious on where I got the word GIS without a GIS? Check out this presentation from Paul Ramsey, PostGIS Co-Founder. at the STL PostGIS Day,2019
Playlist to dive deeper into Spatial SQl and Postgis by the great Dr. Quiseng Wu!
This interactive learning platform by Crunchy Data is super great to get up and running with learning PostGIS.
Official Introduction to PostGIS