How to access your Linux based PostGIS database

Hey guys!
Here is the next part about PostGIS running on Ubuntu and how to use it for your everyday work with GIS on your client computer. This can be the same computer your database is running on (here Ubuntu 16.04) or any remote PC with Linux or Windows…
You don’t have a running PostGIS yet? Go to our installation tutorial for setting up a stable and up-to-date PostGIS DBMS on Ubuntu Linux!

First we need to install the Postgres Adminpack on our server system. It provides a number of support functions which pgAdmin3 and other administration tools can use to give additional functionality.

sudo -u postgres psql
CREATE EXTENSION adminpack;

Now we edit the config files of PostgreSQL to open access to external clients.

cd /etc/postgresql/9.6/main

sudo vim pg_hba.conf

Instead of vim you could use whatever editor you prefer. Let’s go to the end of the document and add the following line under # IPv4 local connections:

host    all             all             0.0.0.0/0               md5

This will allow all users with a md5 encrypted password to connect from remote. You could set whatever IP range you want to have here (e.g. for safety reasons your local network).

Now we edit postgresql.conf and change listen_addresses line to a specific IP of the server or ‘*’ to listen on all IPs:

sudo vim posgresql.conf

listen_addresses = '*'

Save and exit your editor. Do a service restart:

sudo service postgresql restart

Now we are ready to add the database in QGIS: In the Browser panel right-click on PostGIS and open “New Connection…”. Fill in your connection information:

If you are running QGIS on an external client then use the IP of your server in Host. Database and Username have to match your given designations throughout the installation process.

Successfully connected to the PostGIS database you can now add layers to it or use some of the PostGIS functions for data processing.
Let’s try a short example and convert the information of an ESRI shape file into a PostGIS vector layer.

Open QGIS and start the DB Manager (Database->DB Manager).
Click the Import layer/file button on the toolbar in the DB Manager dialog.
Select the shape file of your choice as Input. Click the Update Options button to pre-fill some of the form values. SRID fields (coordinate system) should be prefilled – if not choose the right coordinate system (e.g. WGS84 SRID 4326).
Your form could look like below:

After clicking OK you should refresh your database connection to see the new dataset. Now check if spatial reference etc. is listed correctly in Info of your DB Manager.

Drag and drop your layer from your database connection in the browser panel to your layers panel and have fun working with it!

The post How to access your Linux based PostGIS database first appeared on GIS-Blog.com.