17

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?

4
  • This how I install PostGIS on Windows - in a simple installation get.enterprisedb.com/docs/… then QGIS will connect easily with the username and password you set.
    – Mapperz
    Commented Mar 21, 2017 at 15:10
  • 1
    I would definitely advise installing pgAdmin 3 to work with your database. There is a pgAdmin4 now, you could also look to install that at the same time, see which one you prefer. Commented Mar 21, 2017 at 15:28
  • Wait, so where do go in QGIS then, and which files should I install in the stackbuilder? Edit nvm: found it now I need to find which local host and service entries I must make. Commented Mar 21, 2017 at 15:44
  • Postgis will install the required 'server' locally this is way the database works - you then use QGIS to connect 'live' via the server (localhost).
    – Mapperz
    Commented Mar 21, 2017 at 17:30

2 Answers 2

28

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/pgadmin-4-windows/

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.

enter image description here

  • 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;

enter image description here

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

enter image description here

and type in

CREATE EXTENSION PostGIS

Press 'Execute Query' - this icon enter image description here

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.

enter image description here

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.

8
  • It seems they did install PQadmin 4, I am providing the password when the program ask for it but I get "could not translate host name "postgres//*communityname123" to address: Unknown server error". So what must I do here, keep in mind I am working with PQadmin4 and there is only one server PostGre SQL 9.6. So I am gonna assume here this is the PostGre QSL server they installed, but they may did something wrong with the configuration of the address. So do I need to inform them here or am I doing something wrong? *= sensor. Commented Mar 22, 2017 at 7:53
  • 1
    Try 'localhost' as the host name, just to rule that in or out. Then if not, ask your administrator for the path to where postgres is on the network, they should be able to give you this. It's probably just syntax, or missing something in the address. Commented Mar 22, 2017 at 8:00
  • Thank you that did work, turns out they made mistake by also changing the username to superuser, Postgres and so on. I will follow the rest of the instruction and see if it works. Thank you martin. Commented Mar 22, 2017 at 9:28
  • Oke it works Martin, can you please add the rest of how make the database for a group that is working here? Commented Mar 22, 2017 at 10:55
  • Quickly added some scripts, I had to wait for my lunch break, so a little rushed. They should work. Commented Mar 22, 2017 at 14:17
3

It seems that your project will work sufficient on a portable installation of postgres / postgis. I did this for my students a while ago, so that they can copy the postgresl folder on a usb stick and use it on every windows pc:

  1. Download postgresql as a zip file
  2. Unzip it
  3. Download postgis (postgis-bundle-pg96-2.3.1x32.zip or x64), according to your system
  4. Unzip it and copy it into the postgresql folder
  5. Use this explanation to "install" Postgres as a portable version: http://www.postgresonline.com/journal/archives/172-Starting-PostgreSQL-in-windows-without-install.html
  6. Optionally change pg_hba.conf and add / override "host all all 127.0.0.1/32 trust" - this allows access from your pc to the database without checking the password, which is ok in this case but it's a no-go in production environments
  7. Connect to the database using pgadmin3
  8. Create a new database and execute "CREATE EXTENSION postgis;"

Now you should be able to work with postgesql/postgis and access the db on your pc. Please be aware that this is a quick and dirty solution, which works for educational purposes but not in a production / network environment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.