Getting Started with PostgreSQL/PostGISPostgreSQL (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 TerminologyWhen 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: 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, including 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: DatatypesPostgreSQL 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 |
OtherStart the Course Environment and ApplicationsLog 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
| 