PostGIS
Preface
The following guide is designed to aid GIS professionals in setting up a PostGIS database. All software used in the guide below is free and open source, owned by their respective owners, and is provided as is without warranty of any kind. Use at your own risk. That being said, here's a quick-start walkthrough of how to set up your own PostGIS server. Note that this is only a quickstart on how to set up a PostGIS server, not a complete guide on how to architect and structure your data. That is incumbent to you, the user.
Initial Setup
It is assumed that all software will be run in the Ubuntu Linux environment, either hosted on physical hardware or a virtual machine. In this initial setup, a quick example of how to utilize Microsoft’s Hyper-V virtual machine platform is described. In order to enable this, search for “Turn Windows Features On or Off” and turn on the Hyper-V features (see screen shot in Figure 1). You may need to reboot your computer once this has been enabled.
Figure 1: Turn on Hyper-V in Windows
Once Hyper-V is installed, a virtual machine must be setup. The following steps are used to set up a virtual machine in Hyper-V
1) Download the ISO image of Ubuntu Desktop. I use the LTS, or Long Term Support, version due the length of updates and support and because this is the standard base release used in most clouds. Obtain the image version I’m using (20.04 LTS) at: https://ubuntu.com/download/desktop/thank-you?version=20.04.1&architecture=amd64
2) Create a new Virtual Machine (see highlighted “New” section in Figure 2)
Figure 2: Create a new Hyper-V machine
3) Set up a name for your server (I used GISTC) and a path that you would like the VM to be saved at (default or specified by you; either is fine).
4) Use Generation 1 option
5) Assign 4096MB for memory (or more if you need)
6) Use a shared or pre-set ethernet adaptor
7) Choose an ethernet connection
8) Create a Virtual Hard Disk (defaults are fine) with 25GB of space minimum
a. Note that you DO NOT need all space specified at this time
9) Install the OS from a bootable CD/DVD-ROM
a. Browse and select the ISO we downloaded previously
10) Review the summary and finish VM creation
11) Start the virtual machine in Hyper-V by double clicking the icon
Installing UBUntu Linux
The following describes how to quickly setup and install Ubuntu Linux.
1) Install using the English language option
2) Use the arrow keys to select the “Install Ubuntu Option”
a. Note that the version of Ubuntu I am running is “focal”
3) Use the default installation options for keyboard
4) Choose the “Minimal Installation” install option
5) Erase disk and install Ubuntu
6) Set time zone to “Los Angeles” or where you are
7) Set machine name to gistcvm
8) Set user name to gistc
9) Set pw to GIS4me!
10) Open a terminal window to update Ubuntu and type in the following:
a. sudo apt-get update
b. sudo apt-get upgrade
Note that updating a base version may take some time. You may avoid this time if you’re comfortable conducting an advanced deployment. This also can be avoided if you do not want to set your own custom settings, as would allow you to set in this walk-through, and install directly via the web using Hyper-V’s built in “Quick Create” tool and selecting the Ubuntu deployment available there.
Install Postgres/postgis
Begin installing PostGRES/PostGIS through the following steps.
First, make sure that PostGRES has the correct public key and repository set up:
Setup key with:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
sudo apt-key add -
Setup repository with:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ focal-pgdg main" >> /etc/apt/sources.list.d/postgresql.list'
Next begin installing using:
sudo apt-get update
sudo apt-get install postgresql
sudo apt install postgis postgresql-12-postgis-3
Run the sudo apt update and sudo apt upgrade commands when the above completes to ensure any updates and dependencies are also upgraded.
Install PGAdmin
PGAdmin is a tool that allows a user to administer and develop a PostGRES/PostGIS database. This software is available at https://www.pgadmin.org/. The software as installed here will be for administrative purposes only. Use the following commands to install on the VM.
sudo apt install pgadmin4
Setup the Database
Create a user to access the database besides the default postgres daemon user. I will use the user “gistc” to access/admin the database we will create named “gis” and encode it using the UTF8 standard.
sudo -u postgres createuser gistc
sudo -u postgres createdb --encoding=UTF8 --owner=gistc gis
Activate PostGIS on the newly created “gis” database; start by granting super user privileges to the user and then entering in the commands once in the database:
sudo -i -u postgres
psql -d gis
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
You should see something like:
You can check to see if PostGIS is working properly for the database by entering in the following command in the PSQL prompt (e.g. gis=#):
SELECT PostGIS_version();
Note that the psql version is 13, and that in order to remotely access the database, including by using PgAdmin4, we need to allow connections to come from any port. This is something that will not matter for our VM, however, this is important if you are creating a production server. You should only allow connections from fixed IP ranges you specify for security.
In the terminal you should navigate to the directory using:
cd etc/postgresql/13/main
Then edit the postgresql.conf file finding the listen_addresses line. Replace the “localhost” value with ‘*’ (asterisk with single quotes). Use the control key and O to write out the changes, and the control key and X to exit nano. To edit the file use the following:
sudo nano postgresql.conf
Before editing the file it should look like:
Similarly, Edit the pg_hba config file and change 127.0.0.1/32 to 0.0.0.0/0 for IPv4 and ::1/128 to ::/0 for IPv6. Save and exit as described previously.
Restart the service using:
sudo service postgresql restart
NOTES:
You may need to make sure that your network connection (PC and VM) are set to use IPv4 only. IPv6 is not currently supported and may cause issues when trying to connect, particularly with PgAdmin or other accounts! See screenshots indicating potential network connections to take a look at Figure 3 and the properties of the network connection to force Figure 4 for more information.
You can load data from QGIS for example into your PostGIS database using the Processing Tools, specifically Database -> Export to PostgreSQL tool.
Figure 3: Example of network connections to check for IPV4 settings (selected)
Figure 4: Example of allowed connection uses (IPv4 checked and highlighted, IPv6 turned off - unchecked).