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

String

Other

• Date

• Timestamp

• Geometry (with PostGIS installed)

Start the Course Environment and Applications