SQL Academy · Lesson

Spatial Joins and Containment

Which points fall in which areas.

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)
);

All lessons in this course

← Back to SQL Academy