Setting up PostGIS database and opening it in QGIS on Windows?

I have tried to follow the manuals for setting up a PostGIS database to be accessed by users with QGIS, but at the moment I cannot understand enough to progress?

My situation; I am a trainee responsible for creating a PostGIS database for the digitising of green objects (such as trees) and running queries - like linking a street list, with image files, to the attribute table.

I have "shapefile skeletons" (currently empty with no objects, just the attribute frame" where the current and future georeferenced objects (a different shapefile for each type) will be pasted/created). I trust I can just import those skeleton shapefiles into the database?

The situation;

We work in a separate building with our own LAN and internet connection with a backup server for every file. So we have local file access and files that we share over an internet connection

The setup of the database has been done by an administrator via remote access, using the latest .exe execution files of PostGIS and PostgreSQL (postgresql-9.6.2-3-windows-x64 & postgis-bundle-pg96x64-setup-2.3.2-1) for my 64-bit machine. The administrator provided me with a text file with login information. The training manuals here are pretty confusing since they deal with Ubuntu.

I am stuck as I do not know where to use the login information that I have written down? I assume I need to use pgAdmin?

Answer

It sounds as if your administrator has installed the database successfully and provided you with the details you will need. They may have also configured PostGIS during the install as well, but if not, you can check that as you go through these steps.

First ask your administrator to install pgAdmin this will allow you to access your PostgreSQL database with a graphical interface.

https://www.pgadmin.org/download/windows.php

Open pgAdmin

If your PostgreSQL has been installed;

• on your computer it will show up as localhost, you should be able to double click on 'PostgreSQL (version no.) (localhost:5432)' to connect to it.

• If it has been installed on the network, you will need your administrator to let you know the host address (if you don't already have that), then you will need to go to File > Add Server and fill in the fields;

Once connected go to Edit > New Object > New Database

Give it a name. It will appear in your server list.

Double-click to connect to it. Check the Extensions to see if it lists postgis.

If not;

Open up the SQL window by clicking on the SQL icon

and type in

CREATE EXTENSION PostGIS

Press 'Execute Query' - this icon

Right-click on the database in the list and disconnect, and then reconnect. Extensions should now have postgis listed.

You could set up a new Group Role, and login role at this point, it sounds as if you should given the security needs, and your users across the network.

Or if you just want to test it you can continue on with the default role postgres.

Different Groups can be set up to have different permissions e.g. admin and user. User logins can then be set up and assigned to whatever group fits the permission level you want to give the user, or you can give access to all tables to admin, and only some to users etc. You may not need an admin group.

Open up the SQL window and execute these queries;

CREATE ROLE user_group_name NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE admin_group_name NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;


GRANT user_group_name TO admin_group_name

GRANT ALL ON DATABASE database_name TO admin_group_name;

Set up an admin login (logged in as the postgres/superuser)

CREATE ROLE admin_name LOGIN PASSWORD 'their_password' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT admin_group_name TO admin_name;

Set up a user login (logged in as the postgres/superuser)

CREATE ROLE user_name LOGIN PASSWORD 'their_password' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT users_group_name TO user_name;

Open QGIS. Start a new project.

Click on the 'Add PostGIS layer' icon in the left hand toolbar.

Click 'New' connection, and enter the connection details as above (if your database is local then host is just localhost).

You can test the connection at this point. If you check the 'Save' boxes by the username and password field it will store your credentials in your profile so you don't need to enter each time. Up to you depending on your security needs.

Press Ok and close down the 'Add PostGIS table window'

Then go to Database > DB Manager > DB Manager (Recent QGIS versions have DB Manager installed as standard, earlier versions you will need to install the DB Manager plugin)

Select PostGIS. The default public schema will be shown. It sounds as if you should create a new schema rather than use the public one.

Schema > Create schema - Give it a name (no_spaces_or_use_underscores)

Once created, you can check by going back to pgAdmin, disconnect, reconnect. The new schema should now be there.

Back to QGIS DB Manager to upload a shapefile into a table in the database.

Click the down arrow 'Import layer/file' browse to the shapefile that you want to import. Click the `...`` icon to open browse.

Give the table a name (again_no_spaces_or_use_underscores)

Select Primary key

Select Geometry Column

Select SRID and enter the CRS code

For now leave everything else and OK.

If it's a large dataset, it could take a while, especially across a network/internet connection

Again you can go back to pgAdmin, press refresh this time, and the new table will appear in Tables.

If you have set up group roles and users you will need to grant access to the schema, for example;

GRANT ALL ON SCHEMA schema_name TO admin_group_name;
GRANT USAGE ON SCHEMA schema_name TO user_group_name;

Back to QGIS and 'Add PostGIS layer'. This time select Connect. Open up the schema and highlight the table and click 'Add' and it will open up in your project space.