Versions Compared

Old Version 17

changes.mady.by.user Antony Scott

Saved on

compared with

New Version Current

changes.mady.by.user David Renn

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Numberedheadings
h6
number-formatdecimal
skip-headings
start-numbering-with2
h1
h2
h3
h4
h5
h6
enabledtrue
start-numbering-atH1

Postgres End User Tools

It is possible to interact with databases via a number of tools. Some like DBeaver and DbVisualiser are multi-platform and can connect to lots of different databases (SQL Server, Oracle, MySQL, SQLite etc.) as well as PostgreSQL Others like pgAdmin are optimised for a particular database. It is also possible to connect to Postgres using popular desktop GIS and reporting tools. We will work with both pgAdmin and QGIS in this course.

pgAdmin

pgAdmin 4 is the most popular and feature-rich open source administration and development platform for PostgreSQL. It comes as part of the PostgreSQL package, but also can be downloaded and installed separately (from www.pgAdmin.org).

Now very stable, pgAdmin is a community project relying upon developers around the world with new features and bug fixes being delivered every few weeks. It is worth updating pgAdmin periodically to benefit from these constant improvements.

The pgAdmin interface shows all objects in a tree structure, and includes specific tools for each object, usually accessible from the right-click menu.

To start the exercises, you will connect to a server, then create a database to work on during the course, using a backup of a standard database.

Connect to a server in pgAdmin

  1. Open pgAdmin to create a new connection

  2. By default you will see a Servers node in the tree on the left of the interface

  3. Right-click > Register > Server… on the Servers node to connect to a database server

  4. The connection details you will need [in square brackets] are:

    1. On the General tab:

      • Name: your name for the connection - for example Training

    2. On the Connection tab:

      • Host: Name or IP address of the server [appstream_data.astuntechnology.com]

      • Port: The default is [5432]

      • Maintenance Database: the name of the database to connect to [

training_postgis

      • postgres]

    2. On the Basic tab

      • Username: [postgis]

      • Password: [postgis]

      • Save password?: [Yes]

  2. When you have added these parameters, click Save to connect to the server and save the connection details

  3. When this is done, you will be able to see the server in the tree, and browse to the Databases it contains

Create a database

  1. Select the Databases object, then Right-click > Create > Database… to create a PostgreSQL database

    1. give the database a name based on your name and your AppStream user - for example jane_guest3

    2. make the owner postgis

    3. on the Definition tab, set the Template to training_postgis_delegates from the dropdown - this will give you a copy of the training database

    4. click Save to create the database

  2. You should now have a database to work on during the course

QGIS

QGIS is a desktop GIS application which integrates tightly with Postgres. To download QGIS or find out more about its capabilities see https://qgis.org/en/site/ .

Connect to a database in QGIS

  1. Open QGIS and find the PostgreSQL node in the Browser panel

  2. Right-click > New connection to open the dialog

  3. The connection details you will need [in square brackets] are:

    1. Name: your name for the connection - e.g. [Training]

    2. Host: Name or IP address of the server [appstream_data.astuntechnology.com]

    3. Port: The default is [5432]

    4. Database: the name you gave the database when you created it (e.g. jane_guest3)

    5. Basic tab > Username: [postgis], check Store

    6. Basic tab > Password: [postgis], check Store

  4. When you have added these parameters, click Test Connection - you will get a confirmation message

  5. Click OK to save the connection details

  6. When this is done, you will see the database in the Browser, and will be able to navigate to the tables it contains

Feature Comparison

pgAdmin gives the user (subject to permissions) full control over database administration. QGIS provides much of the same functionality, again subject to permissions, but with the focus on higher level objects such as a schemas and tables.

Feature

pgAdmin

QGIS

Browse and manage schemas, tables, columns (create, delete, rename)

Yes

Yes

Load data

Yes

Yes

Browse and manage all database objects including indexes, constraints etc.

Yes

No

Manage access control

Yes

No

Write and run SQL

Yes

Yes

View raw data

Yes

Yes

Visualise spatial data

Yes (via Geometry Viewer)

Yes (in context with Base mapping and other data if required)

Run through the following exercise in both pgAdmin and QGIS, and be aware of the differences between them.

Database familiarisation: pgAdmin and QGIS

  1. Run through the tasks in the table below in both pgAdmin and QGIS

  2. Note that by default QGIS will only show tables which are spatial, i.e. which have a column of type geometry

Task

pgAdmin

QGIS

1

Navigate to a table and view data

  • Go to [database name] > [schema name] > tables > [table name]

  • Right-click > View/Edit Data > All Rows

  • Go to [database name] > [schema name] > [table name]

  • Right-click > Layer Properties > Attributes
    See also Metadata and Preview

2

Create a table, add columns

  • Go to [database name] > [schema name] > tables

  • Right-click > Create > Table

  • Complete the dialog with suitable values, adding columns in the Columns tab (add two or three test columns)

  • Use integer, double precision, or text data types for now

  • Go to [database name] > [schema name] > [table name]

  • Right-click > New Table

  • Complete the dialog with suitable values, adding columns using Add Field (add two or three test columns)

3

Query/filter data

  • Go to [database name] > crime > neighbourhood_crime

  • Right-click > View/Edit Data > Filtered Rows

  • Enter filter - e.g. burglary > 10 and click OK

  • Edit the QGIS connection to the database so that Also list tables with no geometry is checked (otherwise you won’t be able to access the table below)

  • Go to [database name] >> crime > neighbourhood_crime

  • Double-click to load data

  • In Layers panel, right-click > Filter

  • Build filter from dialog

  • Click OK

4

Run SQL

  • Go to Tools > Query Tool (or toolbar button)

  • Type SQL, e.g.
    SELECT * FROM crime.neighbourhood_crime

  • Click F5 to execute

  • Go to Database > DB Manager

  • Navigate to database

  • Click SQL Window button in toolbar

  • Type SQL, e.g.
    SELECT * FROM crime.neighbourhood_crime

  • Click Execute

...