Maps That Think

Imagine you're the mayor of a growing city. A new hospital needs to be built, but where? It must be close to the most people, reachable by road, and far from flood zones. A GIS can answer that.

Your GIS Toolbox

This course is built around an open-source stack — free, professional-grade tools used worldwide.

Two Flavors of Spatial Data

Every GIS dataset is either vector or raster.

Points  ●  (hospitals, villages)
Lines   ─  (roads, rivers)
Polygons ■ (districts, forests)

Each feature has a shape
+ an attribute table (name,
population, type, etc.)

Check Your Understanding

The Rules of Good Tables

A relational database is like a library with a perfect catalog system.

Normalization: Three Cleaning Passes

Normalization is tidying a messy room in three passes — each catches a different kind of mess.

The Ice Cream Shop Example

Jen and Barry sell ice cream and need to track orders. Watch how a messy table gets normalized:

Entity-Relationship Diagrams

Before building tables, you draw an ER diagram.

Check Your Understanding

Step 1: Creating Tables

SQL is how you talk to a database. Let’s start with the most fundamental operation — creating a new table from existing data.

CREATE TABLE kelurahan AS
SELECT geom, giskemen_2, giskemen_4,
       giskemen18, giskemen19, giskemen20,
       giskemen21, giskemen27, giskemen28
FROM public.desa_batam

Step 2: Renaming Columns

Real-world data arrives with cryptic column names. SQL lets you clean house with ALTER TABLE.

ALTER TABLE public.kelurahan
  RENAME COLUMN giskemen_2
  TO nama_desa;

Step 3: Managing Primary Keys

Sometimes you need to restructure the identity of a table — change which column serves as the unique identifier.

-- Step A: Remove old primary key
ALTER TABLE desa_batam
  DROP CONSTRAINT id;

-- Step B: Rename column to something clear
ALTER TABLE desa_batam
  RENAME COLUMN giskemen_4 TO id;

-- Step C: Set new primary key
ALTER TABLE desa_batam
  ADD PRIMARY KEY (id);

Step 4: SELECT — Asking Questions

SELECT is the most-used SQL command. It retrieves data that matches your criteria.

-- Find all hospitals in the data
SELECT nama, jenis, alamat
FROM fasilitas_kesehatan
WHERE jenis = 'Rumah Sakit';

-- Count facilities per type
SELECT jenis, COUNT(*) AS jumlah
FROM fasilitas_kesehatan
GROUP BY jenis
ORDER BY jumlah DESC;

Step 5: JOINs — Combining Tables

JOINs are where relational databases shine — combining data from multiple tables.

SELECT
  k.nama_desa,
  k.jumlah_penduduk,
  f.nama AS fasilitas_terdekat
FROM kelurahan k
JOIN fasilitas_kesehatan f
  ON ST_DWithin(
    k.geom, f.geom, 5000
  );

Step 6: Views — Saved Queries

A View is a query saved as a virtual table. Every time you open it, the database re-runs the query with latest data.

CREATE VIEW TestView AS
SELECT * FROM kelurahan
WHERE ST_Touches(
  kelurahan.geom,
  (SELECT geom FROM kelurahan
   WHERE nama_desa = 'SADAI')
);

Practice: Choose the Right SQL

Finding Neighbors: ST_Touches

The training’s first spatial query finds all sub-districts adjacent to a specific area — like asking “who lives next door?”

SELECT * INTO qlayer
FROM kelurahan
WHERE ST_Touches(
  kelurahan.geometry,
  (SELECT geometry
   FROM kelurahan
   WHERE nama_desa = 'SADAI')
)

Measuring Distance: ST_Distance & ST_DWithin

Two essential spatial functions for proximity analysis:

-- How far is each village from the
-- nearest hospital? (in meters)
SELECT
  v.nama_desa,
  MIN(ST_Distance(
    v.geom, h.geom
  )) AS jarak_m
FROM kelurahan v, rumah_sakit h
GROUP BY v.nama_desa
ORDER BY jarak_m DESC;

-- Find all schools within 2km of a road
SELECT s.nama, s.jenis
FROM sekolah s, jalan j
WHERE ST_DWithin(
  s.geom, j.geom, 2000
);

Containment: ST_Contains & ST_Within

-- Count hospitals per district
SELECT
  d.nama_kecamatan,
  COUNT(h.id) AS jumlah_rs
FROM kecamatan d
LEFT JOIN rumah_sakit h
  ON ST_Contains(d.geom, h.geom)
GROUP BY d.nama_kecamatan
ORDER BY jumlah_rs DESC;

Creating Buffers: ST_Buffer

-- Create 500m buffer around rivers
SELECT
  nama_sungai,
  ST_Buffer(geom, 500) AS geom
INTO sempadan_sungai
FROM sungai;

-- Count houses in the flood plain
SELECT COUNT(*) AS rumah_di_sempadan
FROM rumah r, sempadan_sungai s
WHERE ST_Within(r.geom, s.geom);

Area & Length Calculations

-- Calculate area of each district in km²
SELECT
  nama_kecamatan,
  ST_Area(geom) / 1000000 AS area_km2
FROM kecamatan
ORDER BY area_km2 DESC;

-- Calculate road length per type
SELECT
  road_type,
  SUM(ST_Length(geom)) / 1000
    AS total_km
FROM jalan
GROUP BY road_type;

PostGIS Function Reference

Here’s your quick-lookup card for the most important PostGIS spatial functions:

Practice: Spatial SQL Challenges

The Problem

A health department needs to know: How many people live within reach of each clinic? Which areas are underserved?

Step 1: Design the ER Diagram

Before writing any SQL, sketch the relationships using pgModeler:

Step 2: Create the PostGIS Database

-- Connect to PostgreSQL via pgAdmin4
-- Create database with PostGIS extension
CREATE DATABASE health_service_db;

-- Enable spatial support
\c health_service_db
CREATE EXTENSION postgis;

-- Create facilities table
CREATE TABLE fasilitas (
  id SERIAL PRIMARY KEY,
  nama VARCHAR(255),
  jenis VARCHAR(50),
  tempat_tidur INTEGER,
  kecamatan_id INTEGER,
  geom GEOMETRY(Point, 32648)
);

Step 3: Load Data from OpenStreetMap

Data sources from the training:

Step 4: Run the Service Area Analysis

-- After service areas are computed,
-- count population per service zone
SELECT
  sa.facility_name,
  SUM(
    ST_SummaryStats(
      ST_Clip(pop.rast, sa.geom)
    )
  ).sum AS pop_served
FROM service_areas sa,
     worldpop pop
WHERE ST_Intersects(
  pop.rast, sa.geom)
GROUP BY sa.facility_name;

Practice: Database Design

The Regression Model

The training includes a delivery-time prediction model showing how GIS data feeds statistics:

LTIME =
  +0.2663 * CARVOLUME
  +0.6984 * LLENGTH
  +0.0203 * LPOP
  +0.0605 * TWOWAY
  +0.1681 * SCHOOL
  +0.0317 * RESIDENTIAL
  -0.5497

Check Your Understanding

Feature Selection: Extracting Villages

The training extracts village locations from a massive OpenStreetMap dataset for protected area monitoring.

Dissolve: Merging Districts into Provinces

Dissolve merges polygons by attribute. The training uses Laos boundaries (gadm36_LAO_2.shp):

Terrain Analysis: Slope & Hillshade

The training covers DEM-derived analyses using the Processing Toolbox:

Density Analysis: Heatmaps

When you have thousands of points (noise complaints, crime locations, facility positions), heatmaps reveal patterns invisible in the raw data.

Geoprocessing Reference

Practice: Geoprocessing

How WebGIS Works

OGC Standards

Deploying GeoServer

<multipart-config>
  <!-- Increase upload limit -->
  <max-file-size>
    183886080
  </max-file-size>
  <max-request-size>
    183886080
  </max-request-size>
</multipart-config>

Check Your Understanding

Layer Organization

Think of layers like transparencies stacked on a projector — order matters.

The Seven Renderers

Tutorial: Graduated Renderer

Create a population map where bigger dots = more people:

Tutorial: Heatmap Renderer

The training overlays a heatmap of villages on Hin Nam No National Park:

Labeling: Making Maps Readable

Labels turn data into information. Without them, polygons are just colored shapes.

Raster Styling: DEM + Hillshade

The training creates a stunning terrain visualization by combining two techniques:

Practice: Visualization

SQL Command Reference

PostGIS Spatial Functions

Common CRS Codes

Congratulations!

You’ve completed the GIS Analyst Training course — from database design through spatial SQL, geoprocessing, web mapping, and cartographic visualization.

Built from training materials by Firman Hadi
Geomatics Engineering, Politeknik Negeri Batam