In this four part tutorial series, I document the process of creating an open source GIS server using Ubuntu Server, PostgreSQL/PostGIS, Geoserver, and QGIS as the desktop front end. In the first part, I documented the basic process of installing Ubuntu Server on VirtualBox. In this second part, I will document the process of setting up PostgreSQL and PostGIS, the spatial extension.
To begin, start your GIS test server VM in VirtualBox and log into the server.
1) To install PostgreSQL and PostGIS, run a system update and upgrade all necessary files using the following command.
sudo apt-get update sudo apt-get upgrade
Assuming you did not run into any errors, your system should now be updated.
2) Unless previously installed, install PostgreSQL and PostGIS. PostgreSQL (aka Postgres or PG) is the database server that will run the PostGIS geospatial extension.
sudo apt-get install postgresql postgresql-contrib postgis
3) Modify the default admin user (postgres) on the database server using psql, the terminal-based front-end to PostgreSQL. To modify the existing user postgres, run the following command:
Enter psql as the user postgres:
sudo -u postgres psql postgres
Change the password for the user postgres with the following command and follow the instructions (you will need to enter the password twice):
\password postgres
Quit psql with the following command:
\q
4) Create a new database and install the PostGIS extension. Enter psql once again as postgres.
sudo -u postgres psql postgres
In psql, create a database called ‘gis’ with the following command:
CREATE DATABASE gis;
Run the command \l to verify that the database was created correctly. You should see that the database gis is owned by postgres.
Exit psql with \q.
5) Enter psql as user postgres and connect to the newly created gis database.
sudo -u postgres psql gis
Run the following commands to enable PostGIS on the gis database:
CREATE EXTENSION postgis; CREATE EXTENSION postgis_topology; CREATE EXTENSION postgis_sfcgal;
The first command enables PostGIS with raster support, the second enables topology, and the third enables PostGIS Advanced 3D and other geoprocessing algorithms.
Note that you will need to enable the PostGIS extensions on each new database. Hence, if you create a new database for a project, you will need to enable the extensions after creating the database.
6) By default, PostgreSQL only enables connections from the localhost. To enable remote connections (e.g. from your host machine or another machine on your network), two config files need to be modified, namely postgresql.conf and pg_hba.conf. In this step, the database server will be opened to any named user. In an enterprise environment, this is not recommended.
To enable remote access to the database server, you must first add a listening address. To listen to any address (*), edit the postgresql.conf file with the following command:
sudo nano /etc/postgresql/9.5/main/postgresql.conf
Note that if you run the previous command and the file appears to be empty, you did something wrong. You either have a different version of PostgreSQL (9.5) or you simply mistyped the command. Exit nano with ctrl-x, do not save the changes, and try again.
Once in the postgresql.conf file, scroll down to the Connection Settings section and change the line that reads:
#listen_addresses = ‘localhost’
To…
listen_addresses = '*'
Make sure you remove the # at the start of the line. Press ctrl-x to exit. Save the changes when prompted.
To grant access to the database server for all users, edit the pg_hba.conf file and add the line ‘host all all 0.0.0.0/0 md5’.
Edit the pg_hba.conf file
sudo nano /etc/postgresql/9.5/main/pg_hba.conf
Add the following line at the end of the document:
host all all 0.0.0.0/0 md5
Exit nano with ctrl-x. Save changes when prompted. Note that this will allow any named user to access the database from any IP address. If you wish to lock this down a little, you can add access for just the postgres user or specify an IP address in the postgresql.conf file.
In order for these changes to take effect, PostgreSQL needs to be restarted. Do so with the following command:
sudo service postgresql restart
7) In this optional step, you can test the connection from the host machine if you have QGIS installed. First, you need to determine the local IP address of the GIS test server. In the server’s terminal, get the current IPV4 address with the following command:
ifconfg
The IP needed is after ‘inet addr’. In the example below, the IP address of the machine is 10.100.99.107.
In QGIS, locate the Browser Panel and right-click on PostGIS.
Click “new connection”
Enter a name for the connection (e.g. GIS test server), the IP address for the Host (inet addr determined in previous step), do not change the port from 5432, and use gis as the Database.
Enter postgres as the username and its password — do not mistakenly use the server password here as you need to use the one associated with the postgres user. Check both to save the credentials (again, not secure, but fine for a test server).
If all goes well, when you click on the Test Connection button, you should see “Connection to gis was successful”.
Things to note: database names, usernames, and passwords are all case sensitive with PostgreSQL. I try to use lowercase words consistently throughout the database for every database, table, schema, group, and user. If for some reason you cannot connect to the database, go back to the beginning of this tutorial and make sure you followed each step.
At this point, the geodatabase should be up and running. You can explore the database in QGIS using the DB Manager. Try to create new schemas and if you are feeling adventurous, try uploading a vector layer to the database using the ‘Import layer/file’ function. In the fourth part of this blog series, I will document some of the basic functions of PostGIS in QGIS. But first, we must get Geoserver up and running!
Photo by Tobias Fischer on Unsplash
