Introduction to Postgis

In this post I’m going to introduce what PostGIS is, how can use it in PostgreSQL, and how to represent spatial data in a map using QGIS.
In future posts I will explain how to manipulate spatial data to be able to do different kind of geometric analyses.


PostGIS is an extension for PostgreSQL. It adds support for geographic object like points, line-strings, polygons and more.

It also includes a large quantity of spatial functions, such as ‘get the area of a polygon’, ‘calculate the length of a line’ or ‘get the distance between two different objects’ , etc. In addition, it has different operators to combine geometries, so we can get the union of two or more geometries, get the intersected polygon or even create a buffer of a object.

Considering that you have PostgreSQL installed in your system, to install it in Ubuntu is as easy as do:
sudo apt-get install postgresql-10-postgis-2.5

In my case I’m installing the PostGIS 2.5 version in PostgreSQL 10.  The next step is to include it in our database as a new extension. Supposing that we have a database called PostgreSQL, we need to connect to it and run the query:
psql (10.4 (Ubuntu 10.4-2.pgdg16.04+1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=# CREATE EXTENSION postgis;

This will install the extension in your first schema of your search_path.
At this point we have a PostgreSQL database with PostGIS ready to be used!


A shapefile is a geospatial vector data format for geographic information system (GIS) software. It is commonly used in the GIS world to share geospatial data. But we will transform it and insert it in our own postGIS database.

I’m going to use as an example a Game of Thrones shapefile with the different regions controlled by the corresponding Houses. I obtained the shapefiles from a forum that is based on theMountainGoats map, both of them under license  CC BY-NC-SA 3.0

In my case I have the following files (some files can vary):


When we say shapefile, in reality is composed of many different files, 3 of them mandatory and the rest optional.
The mandatory files are:

  • .shp: the shapes itself. It has all the geospatial information.
  • .dfb: columnar attributes for each shape, like the names of the Houses owning each region.
  • .shx: positional index of the features.

And in my case I have 2 more that are optional:

  • .prj: projection format, it contains the coordinate system and projection information.
  • .qpj: another projection format file, but it allows more data.

The first step is to convert our shapefile to something that we can insert in our database.
To do that, we will use the command shp2pgsql, it transforms a shapefile to a SQL query to be run in our database.

shp2pgsql political > political.sql

Now we run it to insert the data in our database:
psql -h localhost -U geoblink -d postgres -p 5432 -f political.sql

Now we have a table in the database with the contents of the shapefile. In the following picture I’m showing the contents of the table.

The last column is the polygon itself in a format that PostGIS understands.


Visualizing in QGIS

QGIS is a free and open source GIS application that supports viewing, editing and analysis of geospatial data.
We could have shown directly the shapefile without inserting it in the database, but as we will do some geospatial analysis in the following post,  I decided to insert it in our database. We can also upload the shapefile to a table using different options in QGIS but I preferred to do it in a way that we can automate and include it in a pipeline.

One inside QGIS we need to ad our database as source. The easy way is to go to the Browser Panel, do right click in PostGIS and select New Connection…

A new window will open and we have to write our connection information there.

After it, in the Browser Panel we can navigate through our database and select the desired table. We double click it and it will appear automatically in the map.

Now we can try to improve the visualization changing the colors of the polygons.
To change the colors we have to follow the steps

  • Right click in our layer in the Layers Panel.
  • Click on properties.
  • Go to styles tab.
  • Click in single symbol and categorized.
  • Choose the column that we want to use to categorize, in our case claimed_by
  • Click on classify, it will show the features and the corresponding colors, we can change them if we want.
  • Finally click in Ok

At this point, each region is painted with a different color according to the house governing it, but we can’t tell which house is that. One way would be looking to the legend, but another one is adding a text label in each region:

  • Right click in our layer in the Layers Panel.
  • Click on properties.
  • Go to labels tab.
  • Click in No labels and select show labels for this layer.
  • In Label with, select to column to show, claimed_by in our case and then OK

And that’s it! We have a map with different colors for each region and the name of the owner on it. We can see that right now the Starks are the ones with more land in all Westeros!

Of course now we can keep polishing the map adding different fill or line styles, adding shadows or backgrounds to the label, etc.

In this post we learnt how to convert a shapefile into a PostGIS table and then show it in QGIS.

In the following post we will start to do a deeper analysis and get quantitative information.


Vicente ‘Tito’ Lacuesta – Senior Data Scientist