Spatial Databases (PostGIS) & AI-Assisted GIS for Public Health Research

Welcome! This interactive application explores the concepts and benefits of using PostGIS spatial databases in public health research. It's designed for researchers who may have limited prior experience with databases or programming but are familiar with public health data.

Navigate through the sections using the sidebar to learn about why spatial databases are crucial, understand PostGIS basics, see how to connect using R and Python, get oriented to the modern (2026) geospatial stack, explore real-world use cases, learn how to use generative AI to study GIS concepts and run spatial analyses safely, test your knowledge with a pop quiz, and find resources for further learning.

How to follow along

This is a hands-on lecture: we alternate concept → demo → you try it. When you see a Lab box, pause and run it yourself — solutions follow. Code is labelled by track so you can pick the one you know:

SQL Python R DuckDB Docker

Learning objectives

By the end of today, you should be able to:

Today's agenda & timing

Why Spatial Databases?

This section delves into the importance of spatial data in public health and the limitations of traditional data storage methods like CSVs and Shapefiles, highlighting the need for robust database solutions like PostGIS.

What is Spatial Data in Public Health?

Spatial data links information to a specific location on Earth. In public health, understanding "where" health events occur, vulnerable populations reside, and services are located is fundamental.

Examples include:

• Mapping disease outbreaks (e.g., influenza spread, cancer mortality rates by state).
• Locations of hospitals/clinics (assessing geographic accessibility).
• Patient addresses (anonymized/aggregated, for understanding distribution).
• Environmental exposure sources (e.g., pollution sites near communities).
• Demographic data aggregated to geographic areas (e.g., census tracts, zip codes).

Visualizing such data on maps can reveal important characteristics or trends not apparent from tabular data alone. This enables targeted, geographically-focused interventions.

Limitations of Traditional Data Storage & The Need for Databases

While CSVs and Shapefiles are common, they have significant limitations, especially for large, complex datasets or collaborative work.

These limitations can undermine research quality, reproducibility, and collaboration. Relational databases (like PostgreSQL) and specialized spatial databases (like PostGIS, an extension for PostgreSQL) address these challenges.

Summary of Challenges: Traditional vs. Database Approach

The question we'll answer all day

To make this concrete, we'll carry one realistic analysis through the whole lecture.

Setup — Your Lab Environment Hands-on

You'll get the most out of today if you can run queries yourself. Pick a track — or do both. The two engines share nearly identical spatial SQL, so the skill transfers either way.

Two tracks, one set of skills

Track A — DuckDB in 60 seconds DuckDB

The fastest path to running spatial SQL. No server, no accounts.

pip install "duckdb>=1.1" "geopandas>=1.0"

# then, in Python:
import duckdb
con = duckdb.connect("riverbend.duckdb")
con.sql("INSTALL spatial; LOAD spatial;")
con.sql("SELECT ST_Point(0, 0) AS p;").show()

# macOS / Linux
brew install duckdb        # or download the binary
duckdb riverbend.duckdb

-- inside the DuckDB shell:
INSTALL spatial; LOAD spatial;
SELECT ST_AsText(ST_Point(-117.4, 33.9));
-- POINT(-117.4 33.9)

Track B — Docker PostGIS in one command Docker

The official image bundles PostgreSQL 18 + PostGIS 3.6. One command, a real server.

# Pull & run a PostGIS server (downloads ~600 MB the first time)
docker run -d --name almanac-pg \
  -e POSTGRES_PASSWORD=almanac \
  -e POSTGRES_DB=riverbend \
  -p 5432:5432 \
  postgis/postgis:18-3.6

docker exec -it almanac-pg \
  psql -U postgres -d riverbend

-- Turn on PostGIS in this database (once)
CREATE EXTENSION IF NOT EXISTS postgis;
SELECT postgis_full_version();

SELECT postgis_version();
-- 3.6 USE_GEOS=1 USE_PROJ=1 ...

SELECT ST_AsText(
  ST_SetSRID(ST_MakePoint(-117.4, 33.9), 4326)
);
-- POINT(-117.4 33.9)

LOAD spatial;
SELECT version();        -- v1.1+

SELECT ST_AsText(
  ST_Point(-117.4, 33.9)
);
-- POINT (-117.4 33.9)

Loading the sample data SQL

We'll generate the synthetic Riverbend tables directly in SQL, so everyone has identical data.

CREATE TABLE clinics (
  clinic_id int PRIMARY KEY,
  name      text,
  geom      geometry(Point, 4326)
);

INSERT INTO clinics VALUES
 (1,'Riverbend Family Health',
    ST_SetSRID(ST_MakePoint(-117.40, 33.95),4326)),
 (2,'Eastside Community Clinic',
    ST_SetSRID(ST_MakePoint(-117.31, 33.92),4326)),
 (3,'North Valley Health Center',
    ST_SetSRID(ST_MakePoint(-117.36, 34.02),4326));

CREATE TABLE blocks AS
SELECT
  row_number() OVER () AS block_id,
  (50 + (random()*450))::int AS pop,
  ST_SetSRID(ST_MakePoint(
    -117.45 + gx*0.01,
     33.88 + gy*0.01), 4326) AS geom
FROM generate_series(0,18) gx,
     generate_series(0,16) gy;

Districts — simple square cells acting as administrative areas

CREATE TABLE districts AS
SELECT
  d AS district_id,
  'District ' || d AS name,
  (8000 + (random()*40000))::int AS population,
  ST_SetSRID(
    ST_MakeEnvelope(                        -- xmin, ymin, xmax, ymax
      -117.45 + (d%3)*0.10,  33.88 + (d/3)*0.08,
      -117.35 + (d%3)*0.10,  33.96 + (d/3)*0.08
    ), 4326) AS geom
FROM generate_series(0,5) d;

PostGIS Fundamentals

This section introduces PostgreSQL, the PostGIS extension, and core spatial concepts essential for using PostGIS effectively in public health research.

What is PostgreSQL and PostGIS?

PostgreSQL: A powerful, open-source object-relational database system (ORDBMS). Known for reliability, data integrity, scalability, and extensibility. It's free and community-supported.

PostGIS: An extension to PostgreSQL that "teaches" it to understand, store, manage, and analyze geographic objects. It adds:

• Spatial Data Types (e.g., `geometry`, `geography`).
• Spatial Indexes (e.g., R-Tree based on GiST for fast spatial searches).
• Spatial Functions (hundreds for analysis, e.g., distance, intersection, buffers).

Analogy: PostgreSQL is the filing cabinet; PostGIS provides special folders and tools for maps within it.

Current versions (mid-2026): PostgreSQL 18 and PostGIS 3.6.x are the current stable releases. Recent PostGIS versions keep expanding raster, vector-tile (`ST_AsMVT`), and 3D support, and modern PostgreSQL releases bring faster parallel queries and better JSON handling — all useful for large public-health datasets. You don't need the newest version to learn, but knowing what's current helps when reading docs and asking AI assistants for help.

Core Spatial Concepts in PostGIS

1. Geometry Types

PostGIS stores geographic features as specific geometry types. Click each type to see examples:

PostGIS also supports Multi-geometries (MultiPoint, MultiLineString, MultiPolygon) which are collections of the same geometry type, plus GeometryCollections.

How geometries are written — WKT & WKB

Every geometry has a human-readable and a binary form. You'll see both constantly.

POINT(-117.4 33.9)
LINESTRING(0 0, 1 1, 2 1)
POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))

-- Constructing & inspecting
SELECT ST_AsText(            -- geometry → WKT
  ST_GeomFromText('POINT(-117.4 33.9)', 4326)
);
SELECT ST_AsGeoJSON(geom)    -- → GeoJSON for web maps
FROM clinics LIMIT 1;

2. Spatial Reference Systems (SRS) / SRID

An SRS (or CRS) defines how coordinate values relate to actual locations on Earth. It's the "language" or "map projection" your data speaks.

• Crucial for: Aligning different data layers correctly and ensuring meaningful measurements (distance, area).
• SRID (Spatial Reference Identifier): A unique integer ID for each SRS in PostGIS (e.g., stored in `spatial_ref_sys` table).
• Common Examples:
  • WGS84 (EPSG:4326): Geographic (lat/lon in degrees), used by GPS.
  • Projected Systems (e.g., UTM, State Plane): Transform Earth's surface to a flat plane (units in meters/feet), better for accurate local distance/area calculations.
• Rule: All spatial data in PostGIS MUST have a defined SRID. For operations between layers, SRIDs should match or be transformable (using `ST_Transform()`).

The classic trap — degrees are not meters

Distance on lat/lon (SRID 4326) is measured in degrees, which is almost never what you want.

-- WRONG: "0.045" — that's degrees, meaningless
SELECT ST_Distance(
  ST_SetSRID(ST_MakePoint(-117.40,33.95),4326),
  ST_SetSRID(ST_MakePoint(-117.36,34.02),4326)
);

-- RIGHT (option 1): transform to a metric CRS
SELECT ST_Distance(
  ST_Transform(a.geom, 32611),   -- UTM 11N, meters
  ST_Transform(b.geom, 32611)
) FROM clinics a, clinics b
WHERE a.clinic_id=1 AND b.clinic_id=3;
-- ≈ 8100  (meters)

-- RIGHT (option 2): cast to geography
-- (meters on a sphere)
SELECT ST_Distance(
  a.geom::geography,
  b.geom::geography
) FROM clinics a, clinics b
WHERE a.clinic_id=1 AND b.clinic_id=3;
-- ≈ 8120  (meters)

geometry vs. geography — which to use

PostGIS offers two spatial types. Choosing well saves you from the degrees-vs-meters trap.

3. Spatial indexes

Like a book's index — but for locations. They dramatically speed up spatial queries ("what's near X?", "what's inside Y?").

• Analogy: a GPS that lets the database rapidly find spatial data.
• How (simplified): PostGIS builds a GiST index over each geometry's bounding box (an R-Tree), so the planner skips most rows.
• Two-phase search: a fast index filter on bounding boxes, then an exact check on the survivors.

-- Build a spatial index (do this on every geometry column you query)
CREATE INDEX blocks_geom_idx ON blocks USING GIST (geom);
ANALYZE blocks;            -- refresh planner statistics

Proving the index works — EXPLAIN ANALYZE

The planner tells you whether it used your index. Reading this is a core skill.

EXPLAIN ANALYZE
SELECT b.block_id
FROM blocks b, clinics c
WHERE c.clinic_id = 1
  AND ST_DWithin(
        b.geom::geography,
        c.geom::geography, 5000);

Spatial relationships — the vocabulary

Most spatial questions reduce to a handful of relationship predicates. They return true/false.

The workhorse — spatial joins

A spatial join connects two tables by a spatial relationship instead of a shared key. This is where PostGIS earns its keep.

-- Count population points inside each district
SELECT d.name,
       count(b.*)      AS n_blocks,
       sum(b.pop)      AS people
FROM districts d
JOIN blocks b
  ON ST_Contains(d.geom, b.geom)   -- the join!
GROUP BY d.name
ORDER BY people DESC;

Connecting to & Querying PostGIS

This section covers general principles for database connections and demonstrates how to connect to PostGIS and retrieve data using R and Python, along with a brief note on SQL.

General Connection Principles

To access any database, you need standard parameters:

• Hostname (Server Address): Network address of the database server (e.g., `localhost`, IP address, domain name).
• Port: Communication endpoint (default for PostgreSQL is `5432`).
• Database Name (dbname): The specific database you want to connect to.
• Username: Your database username.
• Password: Password for the username.

For demos, a read-only account is used to prevent accidental data modification.

# 1. Load necessary libraries
# install.packages(c("sf", "DBI", "RPostgres")) # Run once if not installed
library(sf)
library(DBI)
library(RPostgres)

# 2. Database connection parameters (REPLACE with actual details)
db_host <- "your_host_address"
db_port <- 5432
db_name <- "your_sample_db_name"
db_user <- "readonly_user"
db_password <- "readonly_password"

# 3. Establish connection
con <- dbConnect(RPostgres::Postgres(),
                 dbname = db_name,
                 host = db_host,
                 port = db_port,
                 user = db_user,
                 password = db_password)

# 4. Read a spatial table into an sf object
# Example: Read 5 features from 'health_clinics' table
sql_query_clinics <- "SELECT objectid, clinic_name, address, geom FROM health_clinics LIMIT 5;"
health_data_sf <- st_read(con, query = sql_query_clinics)

# 5. View the loaded data
print(health_data_sf)
# plot(st_geometry(health_data_sf)) # Simple plot

# 6. Close the connection
dbDisconnect(con)
                        

# 1. Import necessary libraries
# pip install "geopandas>=1.0" "psycopg[binary]" "sqlalchemy>=2" geoalchemy2
# Note (2026): psycopg v3 is the current adapter; psycopg2 is legacy/maintenance-only.
import geopandas as gpd
from sqlalchemy import create_engine

# 2. Database connection parameters (REPLACE with actual details)
db_host = "your_host_address"
db_port = 5432
db_name = "your_sample_db_name"
db_user = "readonly_user"
db_password = "readonly_password"

# 3. Create a SQLAlchemy engine (the "+psycopg" suffix selects the v3 driver)
engine_str = f"postgresql+psycopg://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}"
engine = create_engine(engine_str)

# 4. Read a spatial table into a GeoDataFrame
# Example: Read 5 features from 'health_clinics' table
sql_query_clinics = "SELECT objectid, clinic_name, address, geom FROM health_clinics LIMIT 5;"
# Ensure 'geom_col' matches your geometry column name (often 'geom' or 'geometry')
health_data_gdf = gpd.read_postgis(sql_query_clinics, engine, geom_col='geom')

# 5. View the loaded data
print(health_data_gdf.head())
# health_data_gdf.explore()  # interactive Leaflet map (geopandas 1.x)
# For millions of rows, consider 'lonboard' for GPU-accelerated maps.

# 6. Engine disposal (good practice in longer scripts)
# engine.dispose()
                        

Brief Note on SQL

The `query` parameter in `st_read()` (R) and `read_postgis()` (Python) uses SQL (Structured Query Language). PostGIS extends SQL with many spatial functions (e.g., `ST_Distance`, `ST_Intersects`, `ST_Contains`, `ST_Buffer`, `ST_DWithin`).

Performing spatial operations directly in the database using these SQL functions is often very efficient, as it processes data on the server before transferring it to R/Python.

Same skills, no server — DuckDB from Python DuckDB Python

The identical analysis, running in-process over your local tables or files — no database server required.

import duckdb, geopandas as gpd

con = duckdb.connect("riverbend.duckdb")
con.sql("LOAD spatial;")

# The SAME spatial SQL you'd run in PostGIS
df = con.sql("""
  SELECT d.name, sum(b.pop) AS people
  FROM districts d
  JOIN blocks b ON ST_Contains(d.geom, b.geom)
  GROUP BY d.name ORDER BY people DESC
""").df()

# Read big open data with zero import:
con.sql("SELECT count(*) FROM 'overture_places.parquet'")

# Works against either engine — just change how you read:
sql = """SELECT d.name, sum(b.pop) AS people
         FROM districts d
         JOIN blocks b ON ST_Contains(d.geom, b.geom)
         GROUP BY d.name ORDER BY people DESC"""

# PostGIS:  df = gpd.read_postgis(sql, engine)
# DuckDB :  df = con.sql(sql).df()
print(df.head(1))     # -> the most-populated district

The Modern Geospatial Stack (2026) New

PostGIS remains a cornerstone of spatial data work, but the surrounding ecosystem has evolved quickly. This section is a map of the landscape so you know what tools exist, what they're good at, and how they fit alongside PostGIS in a public-health workflow.

PostGIS is no longer the only place to run spatial SQL

A major shift in recent years is that you can now run powerful spatial SQL without a database server at all. The biggest example is DuckDB with its spatial extension — an in-process analytical engine (think "SQLite for analytics") that speaks much of the same ST_* spatial SQL you'll learn for PostGIS.

Cloud-native geospatial formats

The "shapefile era" is fading. Modern formats are designed for the cloud, for big data, and for streaming only the bytes you need.

Spatial indexing beyond R-Trees: H3

Discrete global grid systems like H3 (hexagonal cells) have become popular for aggregating and joining point data at scale. The h3-pg extension brings H3 functions into PostGIS, which is handy for privacy-preserving aggregation of patient locations into uniform cells.

Choosing a tool: a quick guide

Case Study — Clinic Accessibility Hands-on

One question, carried end-to-end on your own Riverbend database. We'll answer it in five steps, each a short lab. By the end you'll have a reusable accessibility pipeline — every step runnable in PostGIS or DuckDB.

Step 1 — inventory & sanity-check SQL

Before any analysis: confirm what you have. Counts, coordinate systems, and validity.

-- How much data, and what SRID?
SELECT 'clinics' t, count(*), ST_SRID(geom) FROM clinics GROUP BY 3
UNION ALL
SELECT 'blocks', count(*), ST_SRID(geom) FROM blocks GROUP BY 3
UNION ALL
SELECT 'districts', count(*), ST_SRID(geom) FROM districts GROUP BY 3;

-- Any invalid polygons? (real data often has them)
SELECT district_id
FROM districts
WHERE NOT ST_IsValid(geom);

Step 2 — population within 5 km of a clinic SQL

The core accessibility measure: which population points are "served"?

-- Mark each block as served / unserved, using true meters via geography
SELECT
  sum(pop)                                   AS total_pop,
  sum(pop) FILTER (WHERE served)             AS served_pop,
  round(100.0 * sum(pop) FILTER (WHERE served) / sum(pop), 1) AS pct_served
FROM (
  SELECT b.pop,
         EXISTS (
           SELECT 1 FROM clinics c
           WHERE ST_DWithin(b.geom::geography, c.geom::geography, 5000)
         ) AS served
  FROM blocks b
) s;

Step 3 — service areas as buffers SQL

Sometimes you want the zone itself — to map it, or intersect it with other layers.

-- A 5 km service area around each clinic
SELECT clinic_id,
       ST_Buffer(geom::geography, 5000)::geometry AS svc
FROM clinics;

-- Union them into one combined coverage polygon
SELECT ST_Union(
         ST_Buffer(geom::geography, 5000)::geometry
       ) AS coverage
FROM clinics;

Step 4 — access rate by district SQL

Now the decision-relevant output: a rate per district, so we compare fairly across populations.

SELECT
  d.name,
  d.population,
  sum(b.pop)                                   AS pop_in_blocks,
  sum(b.pop) FILTER (WHERE served)             AS served_pop,
  round(100.0 * sum(b.pop) FILTER (WHERE served)
        / nullif(sum(b.pop),0), 1)             AS pct_served
FROM districts d
JOIN blocks b ON ST_Contains(d.geom, b.geom)        -- points-in-polygons
CROSS JOIN LATERAL (
  SELECT EXISTS (SELECT 1 FROM clinics c
    WHERE ST_DWithin(b.geom::geography, c.geom::geography, 5000)) AS served
) x
GROUP BY d.name, d.population
ORDER BY pct_served ASC;          -- worst-served first

Step 5 — validate before you trust it SQL

A number is not an answer until you've checked it. Build the habit now.

-- One block you can reason about by hand
SELECT b.block_id,
  ST_Distance(b.geom::geography,
              c.geom::geography)::int AS m
FROM blocks b
JOIN clinics c ON c.clinic_id = 1
ORDER BY m
LIMIT 3;       -- nearest blocks to clinic 1

Extending the pattern — more public-health questions

The same building blocks answer many questions. Notice the recurring verbs: contain, within-distance, buffer, intersect, aggregate.

SELECT d.name, count(c.*) AS cases
FROM districts d
JOIN cases c ON ST_Contains(d.geom,c.geom)
GROUP BY d.name;     -- ÷ population → rate

SELECT sum(b.pop) AS exposed
FROM blocks b, sources s
WHERE ST_DWithin(
  b.geom::geography,
  s.geom::geography, 2000);   -- 2 km

Learning GIS with Generative AI 🤖 New

Generative AI assistants (such as Claude, ChatGPT, Gemini, and coding tools like GitHub Copilot, Cursor, and Claude Code) have become some of the most effective ways to learn spatial concepts and to accelerate real GIS analysis. They are especially valuable for public-health researchers who know their domain deeply but are newer to databases and code. This section shows you how to use them well — and, just as importantly, how to use them safely.

Four ways AI helps with GIS

Click each card to expand. These map to the whole learning-to-doing journey.

How to write a good GIS prompt

The quality of the answer depends heavily on the context you give. A reliable recipe:

• Role & level: "You are a PostGIS expert helping a public-health researcher new to SQL."
• The environment: tool and version ("PostGIS 3.6, PostgreSQL 18"), language (SQL / R sf / Python geopandas).
• The data: table names, key columns, geometry column name, geometry type, and SRID of each layer.
• The goal: the precise question and the units you want (meters? rate per 1,000?).
• The ask: "explain each step", "use a spatial index", "keep it to one query".

Copy-ready prompt templates

Fill in the [brackets] and paste into your AI assistant of choice.

A safe AI-assisted workflow

Use AI as a loop, not a one-shot. A dependable pattern:

Trust, but verify: common AI pitfalls in GIS

🔒 Data privacy & ethics (read this twice)

The AI tooling landscape for GIS

Emerging generative-AI applications in the field New

Beyond chat assistants, generative AI is showing up inside the geospatial workflow. These are real, active areas in 2026 — useful to know about even if you don't use all of them yet. Click a card to expand.

Pop Quiz: Test Your Knowledge!

Let's see what you've learned. For each question, consider the options and then click "Show Answer" to see the correct choice and explanation.

Summary & Resources

This final section summarizes the key benefits of PostGIS for public health research and provides a list of resources for continued learning.

Quick Summary & Benefits Recap

• Overcomes Flat File Limitations: Structured, secure, scalable data storage; better data integrity, attribute handling, concurrent access.
• Enables Complex Spatial Analysis: Rich library of spatial SQL functions for proximity, containment, overlay, buffering directly in the database.
• Promotes Data Integrity and Scalability: Enforces data types, relationships; efficiently manages very large datasets.
• Facilitates Collaboration: Multi-user environment for centralized, consistent data access.
• Integrates with Familiar Analysis Environments: Easy access from R (sf, DBI) and Python (geopandas, SQLAlchemy, psycopg v3).
• Fits a Modern Stack: Complements cloud-native formats (GeoParquet), serverless engines (DuckDB + spatial), and open base data (Overture Maps) — the spatial SQL you learn transfers across them.
• Pairs Well with Generative AI: AI assistants accelerate learning and analysis — when you supply rich context (versions, schema, SRIDs), verify results, and protect sensitive data.

Q&A and Further Resources

To continue your learning journey:

Official Websites:

• PostGIS: postgis.net
• PostgreSQL: postgresql.org

Key R Packages Documentation:

• `sf` (Simple Features): r-spatial.github.io/sf/
• `DBI` (DataBase Interface): dbi.r-dbi.org
• `RPostgres`: rpostgres.r-dbi.org

Key Python Packages Documentation:

• `geopandas`: geopandas.org
• `SQLAlchemy`: sqlalchemy.org
• `psycopg2` (PostgreSQL adapter): psycopg.org/docs/ (psycopg2/3)

Modern Geospatial Stack: New

• DuckDB Spatial: duckdb.org → spatial
• GeoParquet: geoparquet.org
• Overture Maps (open base data): overturemaps.org
• Cloud-Native Geospatial Forum: cloudnativegeo.org
• Spatial SQL reference (PostGIS functions): postgis.net/docs/reference.html

Learning & Doing GIS with AI: New

• Use the copy-ready prompt templates in the "Learning GIS with AI" section as starting points.
• Always state your tool + version (e.g., "PostGIS 3.6, PostgreSQL 18") and your schema + SRIDs in prompts.
• Verify AI-suggested function names against the official PostGIS reference, and check the units of any distance/area result.
• Privacy: never paste PHI/PII or precise patient coordinates into AI tools — share structure and synthetic examples, follow your institution's data policy, and prefer approved/enterprise or local models near sensitive data.

Emerging GenAI-in-geospatial to explore:

• Prithvi geospatial foundation model: ibm-nasa-geospatial (Hugging Face)
• Clay foundation model: madewithclay.org
• segment-geospatial (SAM for imagery): samgeo.gishub.org
• QGIS LLM plugins (GeoAgent, Promptly, IntelliGeo): QGIS natural-language plugins
• pgvector (embeddings in PostgreSQL): github.com/pgvector/pgvector

General Tutorial Search Terms:

• "PostGIS Introduction tutorial"
• "GeoPandas PostGIS tutorial"
• "R sf PostGIS tutorial"
• "DuckDB spatial GeoParquet tutorial"
• "spatial SQL ST_DWithin example"

Hands-on practice with the provided sample database details and code snippets is highly encouraged — and try working through one of the use cases with an AI assistant as your tutor!