Getting Started with PostgreSQL/PostGIS

PostgreSQL (usually referred to as Postgres) is a powerful, open source object-relational database system with over 35 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. The PostGIS extension brings spatial capability to Postgres, and supports integration with many other GIS applications, including open source applications such as QGIS, GeoServer and MapServer. It runs on all major operating systems, and is widely used and supported.

Postgres uses a client/server architecture, comprising:

A server process which manages the database files, accepts connections to the database from clients, and performs database actions on behalf of the clients
A client application that contacts the server to request database operations

Typical clients are:

desktop applications such as QGIS
web servers to connect to the database to publish web pages
database management and administration tools such as pgAdmin

PostGIS is a Postgres extension which adds support for geometric and geographical objects to Postgres, spatially enabling it. Once installed, all its functionality is automatically available to end users, and Postgres becomes a fully-featured spatial database. PostGIS dates from 2001, and is maintained and updated regularly to keep in line with the core Postgres product.

This course is aimed at end users with access to a Postgres database, and either pgAdmin (a Postgres management tool), or QGIS (a desktop GIS application), or preferably both. The course runs on Postgres 13.

Architecture and Terminology

When you connect to a Postgres instance from a client application (e.g. pgAdmin or QGIS), your connection is to a database cluster which contains one or more databases. Each of these is made up of a hierarchy of database objects, as shown in the diagram below.

Within the cluster, the principal objects are as follows:

Database: contains all the data for a specific application - note that queries can not be run across multiple databases, so all the data you need to work with should be within a single database if possible

Within each database:

Schemas are the equivalent of folders in a file system, supporting the organisation of other database objects into logical groups (for example planning, education, highways)
Tables store the records or rows which make up your data, and have rows and columns like a spreadsheet
Columns define the structure of the data stored in a table, each column having a name, datatype (string, integer, date etc) and size
Constraints include requirements for records in a table - for example the primary key, or the geometry type the data must represent
Indexes are included to speed up search and retrieval of data
Triggers define actions to be taken when an event happens - for example when a new row is added to a table
Functions are equivalent to stored procedures - they are canned SQL which performs a task
Sequences implement auto-numbering
Views are virtual tables which are the result of SQL applied to one or more real tables, and ordinary views can not be edited

For a database cluster:

Group and Login Roles control access and rights to schemas and tables:
- Login Roles represent individual users, setting rights on database objects (for example CREATE, UPDATE, VIEW, DELETE)
- Group Roles can also be granted rights, and can also contain users, so many users can easily be allocated the same rights

Datatypes

PostgreSQL supports a large number of datatypes - here are some of the main ones.

Numeric

Name	Storage Size	Description	Range
integer	4 bytes	usual choice for integer	-2147483648 to +2147483647
numeric	Variable	user-specified precision, exact	no limit
double precision	8 bytes	variable-precision, inexact	15 decimal digits precision
serial	4 bytes	Auto-incrementing integer	1 to 2147483647

String

Name	Description
character varying(n), varchar(n)	variable-length with limit
character(n), char(n)	fixed-length, blank padded
text	variable unlimited length

Other

Date
Timestamp
Geometry (with PostGIS installed)

Start the Course Environment and Applications

Log on to the AppStream environment using the credentials you have been given - this will take a few minutes
Start the pgAdmin4 application - if you are asked for a master password, use pgadmin
Start the QGIS 3.28.2 application - if you are asked for a master password, use qgis
Explore the menu options in the AppStream interface, including the Switch Windows button
Let the trainer know if there are any issues or questions - if not you are ready to go into the next exercise

PGIS-01 13: Getting Started with PostgreSQL/PostGIS