Now that we’ve installed PostgreSQL with PostGIS on Elementary OS or Ubuntu 18.04, we need to figure out how to start using it for our geospatial purposes. There are a few things we need to keep in mind regarding how databases work.
- Just because you have installed a RDBMS (Relational Database Management System) like PostgreSQL, does not mean you have created a database. You have only installed the framework to CREATE or use a database.
- The database is not running unless you tell PostgreSQL or your preferred database system to run, and therefore cannot be connected to without this initialization.
Initializing the database to run everytime the computer boots up
On Windows, the graphical manager for PostgreSQL allows you to start the database as Windows boots. You can still do this in Linux, it just requires a simple command line input.
$ sudo update-rc.d postgresql enable
This simple command will start the Postgres instance upon reboot or startup and will allow you to access your databases from external sources.
Creating a database and enabling PostGIS
Logging into the PostgreSQL user account
By installing PostgreSQL on your computer you are creating a postgres user account that is used to manage everying relating to the PostgreSQL system on your computer. This allows for easy management of the database without having to grant root permissions to remote users and database administrators to the core operating system.
To log into this account from the terminal type:
$ sudo -i -u postgres
This logs you into your Postgres user account and allows for manipulation of the databases installed on your machine.
You may also log directly into this account using SSH for a remotely managed computer, but for the purposes of this tutorial we are assuming a local database.
Creating a database and enabling PostGIS
Now that you are logged in as your postgres user account on your computer, we need to log into the database management system. To do this simply type:
This will bring you to the SQL interface for PostgreSQL logged in as the system administrator. This account by default uses the login credentials of the postgres user account for to authenticate. The “postgres” user is the highest level admin user for any and all databases created on your computer and may be used to create and manage new databases, edit existing ones, or make bulk changes to exisiting databases and database users. It is set as the default “OWNER” of all databases created by the system.
To create a new database named “test_gis” and enable the PostGIS extension we need to first create the database, connect to it, then enable the PostGIS extension.
# CREATE DATABASE temp_gis; # \connect temp_gis; # CREATE EXTENSION postgis;
Now that we’ve created the database and enabled PostGIS, we have to start looking at how we are going to use this database with QGIS or other software. While we CAN allow for clients to log in using the “postgres” user account, it is not necessarily a good idea.
Creating a client database user to access and manage the database
To disconnect from the temp_gis database and reenter the SQL interface type:
# \q $ psql
This should take you back to the postgres=# prompt.
We are going to create a new user “gisuser” with a password of “demo” that can be used to manage all aspects of the “temp_gis” database.
# CREATE USER gisuser WITH PASSWORD 'demo'; # GRANT ALL PRIVILEGES ON DATABASE temp_gis TO gisuser;
This is now the user account we will use to log into the database from QGIS.
Connecting to your database in QGIS 3
Finally, we can connect our database to QGIS 3 using the user we created in the last step.
Open QGIS and right click PostGIS on the browser tab then select “New Connection…”
This opens the connection window below:
The Name field is one of your choosing, pick one that makes sense to you.
Since this database is hosted on your local computer, use the loopback IP address of 127.0.0.1. This is a universal IP address on all operating systems that tells the system to look for services running locally.
Port 5432 is the default port for Postgres databases and should be used unless a different one is specified by your database admin.
We are not using SSL encryption for the connection so this should be set to disabled.
Finally, create a new authentication configuration by clicking the green “+” beside the dropdown list.
Save the settings and click OK in the connection settings menu and you should be connected. Check by looking for your database in the browser panel.
Next we will start importing layers to this database and eventually will start creating new tables and features within it.