PostGIS is the spatial extension for the PostgreSQL database. You can use the database not only to store and query your geometries and attributes, but you can also perform typical GIS tasks directly in the database. It needs some effort to get it up and running for the first time, but then it will speed up your work. Check out this short introduction and the great official postgis tutorial.
At first, I installed a postgreSQL/postgis server directly on my Linux machine, but it was a pain to install matching software packages (including all dependencies) and to get them working together. After I finally succeeded, another problem surfaced: the PostgreSQL database stores everything in /var/lib/postgresql and my rather small root partition quickly filled up, while I have plenty of space in /home/.
Using a Docker container turned out to be more error-prone and more flexible.
Get the PostGIS container up and running
If you haven’t done this yet, install docker. I am using docker desktop for Linux, docker engine would be another option. Note that docker desktop is running containers in a small virtual machine with a separate Linux kernel; I specially like that containers and data volumes are stored in my home folder. You can use the GUI, but you don’t need to.
The following command fetches the image for the container from docker hub, creates the container and starts the container:
docker run --name postgis -e POSTGRES_PASSWORD=mypassword -p 127.0.0.1:5432:5432 -v pg_data:/var/lib/postgresql/data -d postgis/postgis
With --name postgis we set the name for the container. -e POSTGRES_PASSWORD=mypassword sets the password for the postgres user. With -p 127.0.0.1:5432:5432 we ensure that we can access the port 5432 (used by postgresql) by calling the same port of the host machine. If other computers in the network should be able to access the database, use -p 5432:5432 instead (and make sure to use a good password). Option -v pg_data:/var/lib/postgresql mounts a persisting volume for the data, so we don’t lose the database when stopping/starting the container or rebooting the machine. If it does not exist yet, the volume will be created. And -d postgis/postgis gets the docker image provided by the postgis project.
Now check the log with docker logs postgis. As soon as you see “database system is ready to accept connections” you can connect to the database.
Some important docker commands:
docker stop container
docker start container
docker rm container
Next time you want to run your postgis container, simply run:
docker run postgis
Connect with pgAdmin4
PgAdmin4 is the GUI tool to administer and query PostgreSQL databases. Unfortunately, it is not well-supported on Linux Mint. Once, I managed to install it using the Ubuntu package, but it was not straight forward and required a lot of googling. However, after a while it failed to run because of a mismatch of python versions – and I finally turned to use docker again. I will describe both options.
Using pgAdmin4 installed on the system
After installing pgAdmin4 on your system, simply run it from the menu and log in (note that your pgAdmin user/password is not the same as the postgres user/password).
Register the server: set a name for the connection (e.g. postgis-docker) and in the tab connection: host name: localhost; port: 5432; username: postgres; password: the password you used above.
Now right-click on “Databases”, create > Database and set a name (e.g. gis). Click on the new database and then on the “query tool” icon. Now load the postgis extension with the first SQL command:
CREATE EXTENSION postgis;
And hit the play button (or press F5).
Running pgAdmin4 in a docker container
Pull and run the official pgAdmin4 docker image (don’t forget to change the login credentials for pgAdmin4):
docker pull dpage/pgadmin4
docker run -p 80:80 \
-e 'PGADMIN_DEFAULT_EMAIL=user@domain.com' \
-e 'PGADMIN_DEFAULT_PASSWORD=SuperSecret' \
-d dpage/pgadmin4
Now open http://localhost:80 in your web browser, log in. Register the server the same way as described above, with one exception: The host name is not localhost, but the IP address of the container. To get the correct IP adress, run in the terminal (with posgis being the name of the container):
docker inspect postgis | grep IPAddress
Finally, add a new database and enable the postgis extension as explained above. Note: the postgres database of the docker image is already filled with some US census data…
Connect with QGIS
Simply connect to the database, e.g. from the browser: PostgreSQL > new connection. Choose a name for the connection (e.g. postgis), fill in host (localhost), port (5432) and database (e.g. gis, see above), click “Test connection” and enter username/password. Finally: OK.
Now you can simply drag and drop layers from and to the database in the explorer pane. However, I have problems to import layers with Multipolygons into the database.
Even better, you can use the DB manager to run SQL queries, and you can load any result as a layer into your project.
Load data with ogr2ogr
You can use ogr2ogr to get data from all kinds of file formats into the database. This command-line tool is a part of gdal, and you have it already installed if you use e.g. QGIS. However, using the normal syntax it tries to talk directly to a database that does not exist on the host system if we are running the database in a docker container. We have to tell it to connect to the localhost via the network (and it took me a while to find out how it works). Simply cd into the directory with e.g. a geopackage and run:
ogr2ogr -nlt PROMOTE_TO_MULTI -f PostgreSQL PG:"host='localhost' user='postgres' password='mypassword' dbname=gis" yourgeopackage.gpkg
Be sure to use the correct quotes and the correct password. With -nlt PROMOTE_TO_MULTI the tables allow multigeometries, otherwise the process will most likely fail. Note that geopackes are spatialite databases and can be queried with SQL as well – even while running ogr2ogr to load data in your postgis database. Simply add -sql your-sql-query to the command.