0Pricing
SQL Academy · Lesson

Spatial Joins and Containment

Which points fall in which areas.

Spatial Joins and Containment is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is a Spatial Join?

A spatial join combines two tables based on a geographic relationship rather than a matching key. Instead of asking "does this ID equal that ID?", you ask questions like "does this point fall inside that polygon?" or "do these two shapes overlap?"

PostGIS extends PostgreSQL with geometry types and spatial functions that make these joins possible. The result is the same as a regular SQL JOIN — rows from both tables combined — but the condition is geometric.

Setting Up Sample Tables

Let's create two tables to work with: cities holding point locations, and countries holding polygon boundaries. Both use the GEOMETRY type from PostGIS with SRID 4326 (standard WGS84 latitude/longitude).

The SRID (Spatial Reference ID) tells PostGIS which coordinate system to use. SRID 4326 is the most common for GPS data.

CREATE TABLE countries (
  id     SERIAL PRIMARY KEY,
  name   TEXT NOT NULL,
  border GEOMETRY(POLYGON, 4326)
);

CREATE TABLE cities (
  id       SERIAL PRIMARY KEY,
  name     TEXT NOT NULL,
  location GEOMETRY(POINT, 4326)
);

Inserting Sample Data

We'll use ST_GeomFromText to insert geometry values from Well-Known Text (WKT) format. WKT is a standard text representation for geometries: POINT(lon lat) for points and POLYGON((...)) for polygons.

Notice that longitude comes before latitude in WKT — this matches the X, Y convention in mathematics.

INSERT INTO countries (name, border) VALUES
  ('France', ST_GeomFromText('POLYGON((-5 42, 8 42, 8 51, -5 51, -5 42))', 4326)),
  ('Spain',  ST_GeomFromText('POLYGON((-9 36, 3 36, 3 44, -9 44, -9 36))', 4326));

INSERT INTO cities (name, location) VALUES
  ('Paris',    ST_GeomFromText('POINT(2.35 48.85)',   4326)),
  ('Madrid',   ST_GeomFromText('POINT(-3.70 40.42)',  4326)),
  ('Bordeaux', ST_GeomFromText('POINT(-0.58 44.84)',  4326)),
  ('Lisbon',   ST_GeomFromText('POINT(-9.14 38.72)',  4326));

ST_Contains: The Core Function

ST_Contains(geometry A, geometry B) returns TRUE when geometry A completely contains geometry B. For our use case, ST_Contains(country.border, city.location) returns true when a city point lies inside a country polygon.

This is the heart of containment joins in PostGIS. The function is part of the OGC standard and works for any combination of geometry types.

SELECT
  ci.name  AS city,
  co.name  AS country
FROM   cities   ci
JOIN   countries co
  ON   ST_Contains(co.border, ci.location);

ST_Within: The Reverse Perspective

ST_Within(geometry A, geometry B) is the exact opposite of ST_Contains: it returns TRUE when geometry A is entirely inside geometry B. ST_Within(city, country) is logically identical to ST_Contains(country, city).

Both functions produce the same result here. The choice between them is a matter of readability — pick whichever reads more naturally for your query.

-- These two queries return identical results:

-- Using ST_Contains (country contains city)
SELECT ci.name, co.name
FROM   cities ci
JOIN   countries co ON ST_Contains(co.border, ci.location);

-- Using ST_Within (city is within country)
SELECT ci.name, co.name
FROM   cities ci
JOIN   countries co ON ST_Within(ci.location, co.border);

LEFT JOIN to Find Unmatched Points

A regular JOIN drops cities that don't fall inside any country polygon. Use a LEFT JOIN combined with a WHERE ... IS NULL check to find points that have no containing polygon — useful for detecting data quality issues or points outside your coverage area.

SELECT
  ci.name  AS city,
  co.name  AS country
FROM   cities   ci
LEFT JOIN countries co
  ON   ST_Contains(co.border, ci.location)
ORDER BY co.name NULLS LAST;

Counting Points per Polygon

Spatial joins shine when combined with aggregation. By joining cities to countries and then grouping by country, you can count how many points fall inside each polygon. This pattern is extremely common in geographic analysis — counting stores per region, events per district, sensors per zone, and so on.

SELECT
  co.name          AS country,
  COUNT(ci.id)     AS city_count
FROM   countries co
LEFT JOIN cities  ci
  ON   ST_Contains(co.border, ci.location)
GROUP BY co.name
ORDER BY city_count DESC;

Using a Spatial Index for Performance

Spatial joins can be slow on large datasets because every point is tested against every polygon. A GIST index (Generalized Search Tree) lets PostGIS use a bounding-box pre-filter to skip most comparisons before running the precise ST_Contains test.

Always create a GIST index on geometry columns you join or filter by. The query planner will use it automatically.

-- Create GIST indexes on both geometry columns
CREATE INDEX idx_countries_border
  ON countries USING GIST (border);

CREATE INDEX idx_cities_location
  ON cities USING GIST (location);

-- The same join now benefits from index acceleration
SELECT ci.name, co.name
FROM   cities ci
JOIN   countries co
  ON   ST_Contains(co.border, ci.location);

ST_Intersects: Overlapping Geometries

ST_Intersects(A, B) returns TRUE if two geometries share any point in common — including just touching at a boundary. It is more permissive than ST_Contains: two overlapping polygons intersect even if neither fully contains the other.

For point-in-polygon tests, ST_Intersects and ST_Contains are equivalent, but ST_Intersects uses the GIST index more efficiently and is often preferred in practice.

-- ST_Intersects is index-friendly and equivalent
-- to ST_Contains for point-in-polygon tests
SELECT
  ci.name  AS city,
  co.name  AS country
FROM   cities   ci
JOIN   countries co
  ON   ST_Intersects(co.border, ci.location);

Joining Points to the Nearest Polygon

When a point lies exactly on a boundary or very close to multiple polygons, you may want the nearest polygon instead of all matches. ST_Distance measures the distance between two geometries, enabling ORDER BY + LIMIT patterns or the specialized LATERAL join with ORDER BY ... LIMIT 1.

This is called a nearest-neighbor join and is common when snapping GPS traces to road segments or assigning a point to the closest region.

-- For each city, find the single closest country centroid
SELECT DISTINCT ON (ci.name)
  ci.name                              AS city,
  co.name                              AS nearest_country,
  ST_Distance(ci.location, ST_Centroid(co.border)) AS dist
FROM   cities   ci
CROSS JOIN countries co
ORDER BY ci.name, dist;

Practical Example: Restaurants in Districts

Here is a realistic end-to-end example: finding which city district each restaurant belongs to, then counting restaurants per district. This pattern applies to any point-in-polygon scenario — ATMs in neighborhoods, accidents in precincts, orders in delivery zones.

The query uses ST_Within inside a spatial join and groups results for a summary report.

-- Assume tables: districts(id, name, boundary GEOMETRY)
--                restaurants(id, name, location GEOMETRY)

SELECT
  d.name                 AS district,
  COUNT(r.id)            AS restaurant_count,
  STRING_AGG(r.name, ', ' ORDER BY r.name) AS names
FROM   districts    d
LEFT JOIN restaurants r
  ON   ST_Within(r.location, d.boundary)
GROUP BY d.name
ORDER BY restaurant_count DESC;

Quick Check

Test your understanding of spatial joins and containment in PostGIS.

Recap: Spatial Joins and Containment

In this lesson you learned how to answer the question "which points fall in which areas?" using PostGIS spatial joins.

Key takeaways:

  • ST_Contains(polygon, point) — returns true when the polygon fully contains the point.
  • ST_Within(point, polygon) — the reverse; logically equivalent to ST_Contains.
  • ST_Intersects — more general overlap test; index-friendly for point-in-polygon.
  • GIST indexes on geometry columns are essential for performance at scale.
  • Combine spatial joins with GROUP BY to count or aggregate points per region.
  • Use LEFT JOIN to detect points that fall outside all polygons.

Spatial joins unlock geographic analysis directly in SQL — no external GIS tool required.

Frequently asked questions

Is the “Spatial Joins and Containment” lesson free?

Yes — the full text of “Spatial Joins and Containment” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Spatial Joins and Containment”?

Which points fall in which areas. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Spatial Joins and Containment” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Spatial Data Types
  2. Distance and Nearest Neighbors
  3. Spatial Joins and Containment
  4. Spatial Indexes (GiST)
← Back to SQL Academy