Introduction to PostgreSQL & PostGIS

Course Overview

Total Suggested Duration: 57 hours

Module 1 — Introduction to PostgreSQL

Learning Objectives

• Understand relational databases.
• Learn PostgreSQL architecture.
• Understand client/server database systems.
• Learn common PostgreSQL use cases.

Topics

• What is PostgreSQL?
• Open-source databases
• RDBMS concepts
• Tables, rows, columns, and schemas
• Transactions and ACID compliance

Module 2 — Installing PostgreSQL & pgAdmin

• Installing PostgreSQL on Windows, Linux, and Docker.
• Understanding PostgreSQL services.
• Authentication and user management.
• Creating databases and assigning privileges.

CREATE DATABASE training_db;

CREATE USER student WITH PASSWORD 'StrongPassword';
GRANT ALL PRIVILEGES ON DATABASE training_db TO student;

Module 3 — SQL Fundamentals

Students learn the core SQL operations needed to query and manage relational data.

• SELECT, INSERT, UPDATE, DELETE
• WHERE, ORDER BY, GROUP BY
• JOINs and filtering

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO students(name, email)
VALUES ('John Doe', 'john@example.com');

SELECT * FROM students;

UPDATE students
SET email = 'newemail@example.com'
WHERE id = 1;

DELETE FROM students
WHERE id = 1;

Module 4 — Database Design

• Primary keys and foreign keys
• Normalization
• Relationships
• Constraints

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id),
    amount NUMERIC(10,2)
);

Module 5 — PostgreSQL Administration

• Backups and restore
• Roles and permissions
• Transactions
• VACUUM and ANALYZE

pg_dump training_db > training_db.sql

psql training_db < training_db.sql

Module 6 — Introduction to PostGIS

Students learn how PostGIS extends PostgreSQL into a spatial database system.

• PostGIS extension
• Geometry vs Geography
• Spatial Reference Systems
• WKT and WKB

CREATE EXTENSION postgis;

Module 7 — Spatial Data Types & Geometry

• POINT
• LINESTRING
• POLYGON
• MULTIPOLYGON

CREATE TABLE parcels (
    id SERIAL PRIMARY KEY,
    parcel_no VARCHAR(50),
    geom GEOMETRY(MULTIPOLYGON, 4326)
);

INSERT INTO parcels(parcel_no, geom)
VALUES (
    'NAIROBI/BLOCK 1/23',
    ST_Multi(ST_GeomFromText(
        'POLYGON((
            36.8219 -1.2921,
            36.8220 -1.2921,
            36.8220 -1.2922,
            36.8219 -1.2922,
            36.8219 -1.2921
        ))',
        4326
    ))
);

Module 8 — Importing GIS Data

• Shapefiles
• GeoJSON
• CSV
• ogr2ogr and shp2pgsql

shp2pgsql -I parcels.shp public.parcels | psql training_db

Module 9 — Spatial Queries

• ST_Intersects
• ST_Contains
• ST_Within
• ST_Buffer
• ST_Distance
• ST_Area
• ST_Centroid

Find parcels within a county boundary

SELECT p.*
FROM parcels p
JOIN counties c
ON ST_Within(p.geom, c.geom);

Calculate parcel area in hectares

SELECT
    parcel_no,
    ST_Area(geom::geography) / 10000 AS hectares
FROM parcels;

Find nearby parcels

SELECT *
FROM parcels
WHERE ST_DWithin(
    geom::geography,
    ST_SetSRID(ST_Point(36.8219, -1.2921), 4326)::geography,
    100
);

Module 10 — Coordinate Systems & Projections

• EPSG codes
• WGS84
• EPSG:4326
• EPSG:3857
• Reprojection

SELECT ST_Transform(geom, 3857)
FROM parcels;

Module 11 — Performance & Spatial Indexing

• GIST indexes
• Query optimization
• EXPLAIN ANALYZE

CREATE INDEX parcels_geom_idx
ON parcels
USING GIST (geom);

EXPLAIN ANALYZE
SELECT *
FROM parcels
WHERE ST_Intersects(
    geom,
    ST_Buffer(
        ST_SetSRID(ST_Point(36.8219, -1.2921), 4326),
        0.01
    )
);

Module 12 — GeoServer + PostgreSQL Integration

• Installing GeoServer
• Connecting GeoServer to PostGIS
• Publishing WMS and WFS services
• Styling layers with SLD

https://server/geoserver/workspace/wms?service=WMS&request=GetMap&layers=workspace:parcels

Module 13 — Building a Parcel Database

This module applies PostgreSQL/PostGIS to a real-world land administration system.

• Parcel numbering
• Ownership records
• Spatial indexing
• Search optimization
• Audit trails

CREATE TABLE parcel_owners (
    id SERIAL PRIMARY KEY,
    parcel_id INT REFERENCES parcels(id),
    owner_name VARCHAR(200),
    national_id VARCHAR(50)
);

Module 14 — PostgreSQL/PostGIS for Web & Mobile Apps

• REST APIs
• ASP.NET Core integration
• GeoJSON output
• Elasticsearch integration
• Mobile GIS apps

SELECT json_build_object(
    'type', 'FeatureCollection',
    'features', json_agg(
        ST_AsGeoJSON(t.*)::json
    )
)
FROM (
    SELECT parcel_no, geom
    FROM parcels
) t;

Module 15 — Final Project

Recommended Software

Recommended Practical Labs

Assessment Strategy

Expected Outcomes

• Design PostgreSQL databases.
• Write SQL queries.
• Build spatial databases.
• Import GIS data.
• Perform spatial analysis.
• Publish GIS services.
• Build land information systems.
• Integrate PostgreSQL/PostGIS into mobile and web applications.