⭐ Guide: Spatial SQL Essentials (JOIN, Intersects, Intersection, Within)

1️⃣ JOIN — the foundation

A JOIN combines rows from two tables. In spatial work, the JOIN condition is usually a spatial predicate instead of an ID match.

🔹 Normal JOIN (non‑spatial)

sql

SELECT *
FROM table_a a
JOIN table_b b ON a.id = b.id;

🔹 Spatial JOIN (your world)

sql

SELECT *
FROM analysis.station_windows sw
JOIN processed.roads r
  ON ST_Intersects(sw.window_geom, r.geom);

Here:

• The JOIN condition is ST_Intersects
• The JOIN matches rows based on geometry relationships
• You get all roads that touch each station window

Key idea: JOIN decides which rows to combine — not what to compute.

2️⃣ ST_Intersects — boolean test

This function answers a yes/no question:

“Do these two geometries touch each other in any way?”

It returns TRUE or FALSE.

🔹 Typical use (JOIN or WHERE)

sql

ST_Intersects(sw.window_geom, r.geom)

🔹 Why it belongs in JOIN or WHERE

Because JOIN and WHERE need boolean conditions.

🔹 Example: find roads touching station windows

sql

SELECT sw.puic, r.geom
FROM analysis.station_windows sw
JOIN processed.roads r
  ON ST_Intersects(sw.window_geom, r.geom);

Result: All roads that intersect each station’s window.

3️⃣ ST_Intersection — geometry output

This function returns the actual overlapping geometry:

“Give me the piece of geometry that lies inside the other.”

It returns a geometry, not a boolean.

🔹 Typical use (SELECT)

sql

ST_Intersection(r.geom, sw.window_geom) AS geom

🔹 Why it belongs in SELECT

Because SELECT is where you compute new values.

🔹 Example: clip roads to station windows

sql

SELECT sw.puic,
       ST_Intersection(r.geom, sw.window_geom) AS geom
FROM analysis.station_windows sw
JOIN processed.roads r
  ON ST_Intersects(sw.window_geom, r.geom);

Result: Each road is clipped to the part inside the window.

4️⃣ ST_Within — boolean test

This function checks containment:

“Is geometry A completely inside geometry B?”

It returns TRUE or FALSE.

🔹 Typical use (WHERE)

sql

WHERE ST_Within(s.geometry, b.geom)

🔹 Example: count stations inside NL

sql

SELECT COUNT(*)
FROM processed.stations s,
     processed.nl_boundary b
WHERE ST_Within(s.geometry, b.geom);

Result: 408 stations inside NL.

🔥 Argument order matters

sql

ST_Within(s.geometry, b.geom)   -- correct
ST_Within(b.geom, s.geometry)   -- always false

⭐ 5️⃣ Putting it all together (your real workflow)

🔹 Step 1 — Find roads touching station windows

sql

SELECT sw.puic, r.geom
FROM analysis.station_windows sw
JOIN processed.roads r
  ON ST_Intersects(sw.window_geom, r.geom);

🔹 Step 2 — Clip each road to the window

sql

SELECT sw.puic,
       ST_Intersection(r.geom, sw.window_geom) AS geom
FROM analysis.station_windows sw
JOIN processed.roads r
  ON ST_Intersects(sw.window_geom, r.geom);

🔹 Step 3 — Measure clipped road length

sql

SELECT sw.puic,
       ST_Length(ST_Intersection(r.geom, sw.window_geom)) AS length
FROM analysis.station_windows sw
JOIN processed.roads r
  ON ST_Intersects(sw.window_geom, r.geom);

🔹 Step 4 — Sum length per station

sql

SELECT sw.puic,
       SUM(ST_Length(ST_Intersection(r.geom, sw.window_geom))) AS total_length
FROM analysis.station_windows sw
JOIN processed.roads r
  ON ST_Intersects(sw.window_geom, r.geom)
GROUP BY sw.puic;

🔹 Step 5 — Determine which stations belong in NL

sql

SELECT COUNT(*)
FROM processed.stations s,
     processed.nl_boundary b
WHERE ST_Within(s.geometry, b.geom);

⭐ 6️⃣ Quick reference cheatsheet

Boolean spatial predicates (JOIN / WHERE)

• ST_Intersects(a, b) → do they touch?
• ST_Within(a, b) → is A inside B?

Geometry‑producing functions (SELECT)

• ST_Intersection(a, b) → overlapping piece
• ST_Buffer(a, d) → create window
• ST_Length(a) → measure geometry

Where they belong

Function	Returns	Use in
ST_Intersects	TRUE/FALSE	JOIN / WHERE
ST_Within	TRUE/FALSE	JOIN / WHERE
ST_Intersection	geometry	SELECT
ST_Length	number	SELECT

If you want, I can turn this into a PDF‑style study sheet, or add diagrams explaining each function visually.