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.
Next time you want to run your postgis container, click the start button in docker desktop or simply run in the terminal:
docker start postgis
Stop your container with:
docker stop postgis
And you can delete the postgis container with:
docker rm 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 --name pgadmin \ -p 80:80 \ -e 'PGADMIN_DEFAULT_EMAIL=user@domain.com' \ -e 'PGADMIN_DEFAULT_PASSWORD=SuperSecret' \ -v pgadmin_data:/var/lib/pgadmin \ -d dpage/pgadmin4
I added option -v pgadmin_data:/var/lib/pgadmin to make the settings and database connections persistent from session to session.
Now open http://localhost:80 in your web browser, log in (if you don’t get the login page yet, wait some seconds and reload the page). 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 address, 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…
To use the existing pgadmin container after rebooting, just execute:
docker start pgadmin
Connect with QGIS
Simply connect to the database, e.g. from the browser panel: right-click on “PostgreSQL”, then “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 browser panel. (However, this will fail with a layer containing multi-part geometries.)
For more options while importing into the database, use the DB manager and click on “Import Layer/File”: You might want to have “Convert field names to lowercase” and “Create spatial index” checked. With a layer without any multi-part features (multi-polygons etc.) you want “Do not promote to multi-polygon” to be checked. With a layer containing multi-polygons, it must be unchecked.
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. The option -nlt PROMOTE_TO_MULTI is only needed if you have a mix of e.g. polygons and multi-polygons. Note that ogr2ogr can filter the data while loading it into the database using an SQL query: Simply add -sql your-sql-query to the command. This even works if the input file is not a database, e.g. with shapefiles.
Update your container to the latest version
Although your containers have the tag “latest”, they are not updated; it simply was the latest version when you pulled the image.
To update your postgis container: pull the latest image with:
docker pull postgis/postgis
Delete your old container (note: the volume with data will stay and can be used by a new container):
docker rm postgis
And create and start a new container with the same docker run as above.
Increase shared memory
Sooner or later, a query or process might fail with “ERROR: could not resize shared memory segment … No space left on device.” This can be avoided by increasing the shared memory of the docker container (only 64 MB by default).
In theory, this can be done by passing --shm-size=1G as another parameter to docker run when creating the container. However, the resulting container exited with an error.
Instead, it is possible to change the default for all containers in the settings of docker desktop: go to docker engine and add:
"default-shm-size": "1G",
… into the main body of the JSON. Now stop and delete your postgis container, and create a new one with docker run as above.