SQL Basics for GIS Data in PostgreSQL: Tables, Queries, Joins and Summaries

SELECT city_name, population
FROM cities
WHERE population > 1000000
ORDER BY population DESC;

-- A schema is a folder inside the database. We create one called "gis" for this part.
CREATE SCHEMA IF NOT EXISTS gis;

-- Look in "gis" first, so "cities" means gis.cities. Repeat this line in every new Query Tool tab.
SET search_path TO gis, public;

-- Step 1: create two tables. Each column has a name and a data type (INTEGER, TEXT, NUMERIC...).
-- The words after the type are rules:
--   PRIMARY KEY  every row has a unique, non-empty id
--   NOT NULL     this value cannot be left empty
--   UNIQUE       no two rows can have the same value
--   REFERENCES   the value must exist in another table (links cities to states)
--   CHECK        the value must pass a test (latitude between -90 and 90)
CREATE TABLE states (
    state_id   INTEGER PRIMARY KEY,
    state_name TEXT NOT NULL UNIQUE,
    region     TEXT
);

CREATE TABLE cities (
    city_id    INTEGER PRIMARY KEY,
    city_name  TEXT NOT NULL,
    state_id   INTEGER REFERENCES states (state_id),
    population INTEGER,
    area_km2   NUMERIC(8, 1),                       -- up to 8 digits, 1 after the decimal point
    latitude   DOUBLE PRECISION CHECK (latitude BETWEEN -90 AND 90),
    longitude  DOUBLE PRECISION CHECK (longitude BETWEEN -180 AND 180)
);

-- Step 2: add rows. States first, because cities refer to them.
INSERT INTO states (state_id, state_name, region) VALUES
    (1, 'Karnataka', 'South'),
    (2, 'Tamil Nadu', 'South'),
    (3, 'Maharashtra', 'West'),
    (4, 'Delhi', 'North'),
    (5, 'Goa', 'West');

-- Step 3: add the cities. NULL means "no value". Ooty has no population or area on purpose.
INSERT INTO cities (city_id, city_name, state_id, population, area_km2, latitude, longitude) VALUES
    (1, 'Bengaluru',  1,  8443675,  741, 12.9716, 77.5946),
    (2, 'Mysuru',     1,   920550,  152, 12.2958, 76.6394),
    (3, 'Mangaluru',  1,   484785,  132, 12.9141, 74.8560),
    (4, 'Chennai',    2,  4646732,  426, 13.0827, 80.2707),
    (5, 'Coimbatore', 2,  1050721,  246, 11.0168, 76.9558),
    (6, 'Mumbai',     3, 12442373,  603, 19.0760, 72.8777),
    (7, 'Pune',       3,  3124458,  331, 18.5204, 73.8567),
    (8, 'Delhi',      4, 11034555, 1484, 28.6139, 77.2090),
    (9, 'Panaji',     5,   114759,   21, 15.4909, 73.8278),
    (10, 'Ooty',      2,      NULL, NULL, 11.4102, 76.6950);

-- The simplest query: SELECT = "show me", * = "all columns", FROM = "which table"
SELECT * FROM cities;

 city_id | city_name  | state_id | population | area_km2 | latitude | longitude 
---------+------------+----------+------------+----------+----------+-----------
       1 | Bengaluru  |        1 |    8443675 |    741.0 |  12.9716 |   77.5946
       2 | Mysuru     |        1 |     920550 |    152.0 |  12.2958 |   76.6394
       3 | Mangaluru  |        1 |     484785 |    132.0 |  12.9141 |    74.856
       4 | Chennai    |        2 |    4646732 |    426.0 |  13.0827 |   80.2707
       5 | Coimbatore |        2 |    1050721 |    246.0 |  11.0168 |   76.9558
       6 | Mumbai     |        3 |   12442373 |    603.0 |   19.076 |   72.8777
       7 | Pune       |        3 |    3124458 |    331.0 |  18.5204 |   73.8567
       8 | Delhi      |        4 |   11034555 |   1484.0 |  28.6139 |    77.209
       9 | Panaji     |        5 |     114759 |     21.0 |  15.4909 |   73.8278
      10 | Ooty       |        2 |            |          |  11.4102 |    76.695
(10 rows)

-- Choose columns by name. AS gives a column a nicer heading. LIMIT shows only the first 4 rows.
SELECT city_name AS place, population AS people FROM cities LIMIT 4;

   place   | people  
-----------+---------
 Bengaluru | 8443675
 Mysuru    |  920550
 Mangaluru |  484785
 Chennai   | 4646732
(4 rows)

-- DISTINCT shows each value once. ORDER BY sorts the result.
SELECT DISTINCT region FROM states ORDER BY region;

 region 
--------
 North
 South
 West
(3 rows)

-- WHERE keeps only the rows that pass the test
SELECT city_name, population FROM cities WHERE population > 3000000;

 city_name | population 
-----------+------------
 Bengaluru |    8443675
 Chennai   |    4646732
 Mumbai    |   12442373
 Pune      |    3124458
 Delhi     |   11034555
(5 rows)

-- Combine tests with AND / OR.
--   IN (1, 2)        matches any value in the list
--   BETWEEN a AND b  matches a range, including both ends
SELECT city_name, population
FROM cities
WHERE state_id IN (1, 2)
  AND population BETWEEN 500000 AND 5000000;

 city_name  | population 
------------+------------
 Mysuru     |     920550
 Chennai    |    4646732
 Coimbatore |    1050721
(3 rows)

-- ILIKE matches a text pattern, ignoring capitals. % stands for "any characters".
-- 'm%' means "starts with m".
SELECT city_name FROM cities WHERE city_name ILIKE 'm%';

 city_name 
-----------
 Mysuru
 Mangaluru
 Mumbai
(3 rows)

-- ORDER BY ... DESC sorts from largest to smallest. LIMIT 3 keeps the top three.
SELECT city_name, population FROM cities ORDER BY population DESC LIMIT 3;

 city_name | population 
-----------+------------
 Ooty      |           
 Mumbai    |   12442373
 Delhi     |   11034555
(3 rows)

-- Sort by two things: first by state, then (inside each state) by population, largest first
SELECT city_name, state_id, population FROM cities ORDER BY state_id, population DESC;

 city_name  | state_id | population 
------------+----------+------------
 Bengaluru  |        1 |    8443675
 Mysuru     |        1 |     920550
 Mangaluru  |        1 |     484785
 Ooty       |        2 |           
 Chennai    |        2 |    4646732
 Coimbatore |        2 |    1050721
 Mumbai     |        3 |   12442373
 Pune       |        3 |    3124458
 Delhi      |        4 |   11034555
 Panaji     |        5 |     114759
(10 rows)

-- In a descending sort, empty (NULL) values come FIRST by default. NULLS LAST puts them at the end.
SELECT city_name, population FROM cities ORDER BY population DESC NULLS LAST LIMIT 3;

 city_name | population 
-----------+------------
 Mumbai    |   12442373
 Delhi     |   11034555
 Bengaluru |    8443675
(3 rows)

-- Empty values must be tested with IS NULL. Writing "= NULL" never works.
SELECT city_name FROM cities WHERE population IS NULL;

 city_name 
-----------
 Ooty
(1 row)

-- COALESCE(a, b) gives a, or b when a is empty. Here: show 0 instead of an empty value.
SELECT city_name, COALESCE(population, 0) AS population_or_zero
FROM cities
WHERE city_name IN ('Ooty', 'Panaji');

 city_name | population_or_zero 
-----------+--------------------
 Panaji    |             114759
 Ooty      |                  0
(2 rows)

-- A calculated column, and CASE WHEN: SQL's version of if / elif / else.
-- The first WHEN that is true decides the label.
SELECT city_name,
       ROUND(population / area_km2) AS density,       -- people per km2
       CASE
           WHEN population >= 5000000 THEN 'metro'
           WHEN population >= 1000000 THEN 'large'
           WHEN population >= 100000  THEN 'medium'
           WHEN population IS NULL    THEN 'unknown'
           ELSE 'small'
       END AS size_class
FROM cities
ORDER BY density DESC NULLS LAST;

 city_name  | density | size_class 
------------+---------+------------
 Mumbai     |   20634 | metro
 Bengaluru  |   11395 | metro
 Chennai    |   10908 | large
 Pune       |    9439 | large
 Delhi      |    7436 | metro
 Mysuru     |    6056 | medium
 Panaji     |    5465 | medium
 Coimbatore |    4271 | large
 Mangaluru  |    3673 | medium
 Ooty       |         | unknown
(10 rows)

-- Text functions, one column each:
SELECT UPPER(city_name) AS upper_name,                  -- all capitals
       LENGTH(city_name) AS letters,                    -- number of characters
       SUBSTR(city_name, 1, 3) AS first_three,          -- 3 characters, starting at the 1st
       REPLACE(city_name, 'uru', 'ooru') AS respelt,    -- swap one piece of text for another
       city_name || ' (' || latitude || ')' AS label    -- || glues text together
FROM cities
WHERE city_name LIKE '%uru';                            -- names that END with "uru"

 upper_name | letters | first_three |  respelt   |        label        
------------+---------+-------------+------------+---------------------
 BENGALURU  |       9 | Ben         | Bengalooru | Bengaluru (12.9716)
 MYSURU     |       6 | Mys         | Mysooru    | Mysuru (12.2958)
 MANGALURU  |       9 | Man         | Mangalooru | Mangaluru (12.9141)
(3 rows)

-- SPLIT_PART(text, separator, n) gives the n-th piece. TRIM removes the extra space.
SELECT SPLIT_PART('Bengaluru, Karnataka', ',', 2) AS part,
       TRIM(SPLIT_PART('Bengaluru, Karnataka', ',', 2)) AS state;

    part    |   state   
------------+-----------
  Karnataka | Karnataka
(1 row)

-- Aggregate functions turn many rows into one number.
-- COUNT(*) counts all rows; COUNT(column) counts only rows where that column is filled in.
SELECT COUNT(*) AS rows_total,
       COUNT(population) AS with_population,
       SUM(population) AS total
FROM cities;

 rows_total | with_population |  total   
------------+-----------------+----------
         10 |               9 | 42262608
(1 row)

-- GROUP BY makes one result row per state, and the aggregates are calculated inside each group.
SELECT state_id,
       COUNT(*) AS n_cities,
       SUM(population) AS total_pop,
       ROUND(AVG(population)) AS mean_pop
FROM cities
GROUP BY state_id
ORDER BY total_pop DESC;

 state_id | n_cities | total_pop | mean_pop 
----------+----------+-----------+----------
        3 |        2 |  15566831 |  7783416
        4 |        1 |  11034555 | 11034555
        1 |        3 |   9849010 |  3283003
        2 |        3 |   5697453 |  2848727
        5 |        1 |    114759 |   114759
(5 rows)

-- WHERE filters rows BEFORE grouping. HAVING filters the groups AFTER grouping.
-- Here: only states that have 3 or more cities.
SELECT state_id, COUNT(*) AS n_cities
FROM cities
GROUP BY state_id
HAVING COUNT(*) >= 3
ORDER BY state_id;

 state_id | n_cities 
----------+----------
        1 |        3
        2 |        3
(2 rows)

-- FILTER counts only the rows that pass a test, so one query can show two counts side by side
SELECT state_id,
       COUNT(*) FILTER (WHERE population >= 1000000) AS big_cities,
       COUNT(*) FILTER (WHERE population <  1000000) AS smaller_cities
FROM cities
GROUP BY state_id
ORDER BY state_id;

 state_id | big_cities | smaller_cities 
----------+------------+----------------
        1 |          1 |              2
        2 |          2 |              0
        3 |          2 |              0
        4 |          1 |              0
        5 |          0 |              1
(5 rows)

-- JOIN combines two tables. ON says which columns must match (the state id in both).
-- "cities AS c" gives the table a short nickname, so we can write c.city_name.
-- INNER JOIN keeps only rows that have a match in both tables.
SELECT c.city_name, s.state_name, s.region, c.population
FROM cities AS c
INNER JOIN states AS s ON s.state_id = c.state_id
ORDER BY c.population DESC NULLS LAST
LIMIT 5;

 city_name | state_name  | region | population 
-----------+-------------+--------+------------
 Mumbai    | Maharashtra | West   |   12442373
 Delhi     | Delhi       | North  |   11034555
 Bengaluru | Karnataka   | South  |    8443675
 Chennai   | Tamil Nadu  | South  |    4646732
 Pune      | Maharashtra | West   |    3124458
(5 rows)

-- LEFT JOIN keeps every row of the left table (states), even states with no city. Goa has one; others may have none.
-- COUNT(c.city_id) counts only real matches, so a state with no cities shows 0.
SELECT s.state_name, COUNT(c.city_id) AS n_cities
FROM states AS s
LEFT JOIN cities AS c ON c.state_id = s.state_id
GROUP BY s.state_name
ORDER BY n_cities DESC, s.state_name;

 state_name  | n_cities 
-------------+----------
 Karnataka   |        3
 Tamil Nadu  |        3
 Maharashtra |        2
 Delhi       |        1
 Goa         |        1
(5 rows)

-- Join first, then group: cities per region, with the total population
SELECT s.region, COUNT(*) AS n_cities, SUM(c.population) AS total_pop
FROM cities c
JOIN states s ON s.state_id = c.state_id
GROUP BY s.region
ORDER BY s.region;

 region | n_cities | total_pop 
--------+----------+-----------
 North  |        1 |  11034555
 South  |        6 |  15546463
 West   |        3 |  15681590
(3 rows)

-- WITH ... AS creates a named temporary result (a CTE), so a long query can be built in readable steps.
-- Step 1 (state_totals): total population of each state.
WITH state_totals AS (
    SELECT state_id, SUM(population) AS total_pop
    FROM cities
    GROUP BY state_id
)
-- Step 2: join the totals to the state names and work out each state's share of the whole country list.
-- 100.0 (with the .0) makes the division give decimals. The inner SELECT in brackets is the grand total.
SELECT s.state_name, t.total_pop,
       ROUND(100.0 * t.total_pop / (SELECT SUM(population) FROM cities), 1) AS share_pct
FROM state_totals t
JOIN states s ON s.state_id = t.state_id
ORDER BY share_pct DESC;

 state_name  | total_pop | share_pct 
-------------+-----------+-----------
 Maharashtra |  15566831 |      36.8
 Delhi       |  11034555 |      26.1
 Karnataka   |   9849010 |      23.3
 Tamil Nadu  |   5697453 |      13.5
 Goa         |    114759 |       0.3
(5 rows)

-- INSERT adds a row.
INSERT INTO cities (city_id, city_name, state_id, population, area_km2, latitude, longitude)
VALUES (11, 'Test town', 5, 1000, 5, 15.3, 73.9);

-- UPDATE changes values. ALWAYS add WHERE, or every row will change!
UPDATE cities SET population = 120000 WHERE city_name = 'Panaji';

-- DELETE removes rows. ALWAYS add WHERE here too, or every row will be deleted!
DELETE FROM cities WHERE city_name = 'Test town';

-- Check the result
SELECT city_name, population FROM cities WHERE city_name = 'Panaji';

 city_name | population 
-----------+------------
 Panaji    |     120000
(1 row)

-- This is meant to FAIL. Latitude 120 breaks the CHECK rule from the table definition,
-- so PostgreSQL refuses the row. The rules protect your data.
INSERT INTO cities (city_id, city_name, state_id, latitude, longitude)
VALUES (12, 'Nowhere', 1, 120, 77);

ERROR:  new row for relation "cities" violates check constraint "cities_latitude_check"
DETAIL:  Failing row contains (12, Nowhere, 1, null, null, 120, 77).

-- A table with a DATE column. Dates are written as text in the form 'year-month-day'.
CREATE TABLE inspections (
    city_id      INTEGER REFERENCES cities (city_id),
    inspected_on DATE
);

INSERT INTO inspections VALUES
    (1, '2025-01-15'), (1, '2025-03-02'), (2, '2024-11-20'), (4, '2025-03-15');

-- EXTRACT pulls a part (year, month) out of a date. Subtracting two dates gives the number of days between them.
SELECT city_id,
       inspected_on,
       EXTRACT(YEAR FROM inspected_on)  AS year,
       EXTRACT(MONTH FROM inspected_on) AS month,
       DATE '2025-04-10' - inspected_on AS days_ago
FROM inspections
WHERE inspected_on >= DATE '2025-01-01'           -- only inspections from 2025 on
ORDER BY inspected_on;

 city_id | inspected_on | year | month | days_ago 
---------+--------------+------+-------+----------
       1 | 2025-01-15   | 2025 |     1 |       85
       1 | 2025-03-02   | 2025 |     3 |       39
       4 | 2025-03-15   | 2025 |     3 |       26
(3 rows)

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg2://postgres:YOUR_PASSWORD@localhost:5432/gis_training")

df = pd.read_sql_query(
    "SELECT city_name, latitude, longitude, population FROM gis.cities WHERE population IS NOT NULL",
    engine,
)
print(df.shape)
print(list(df.columns))