SQL Academy · Lesson

Distance and Nearest Neighbors

Find what's close by.

Distance and Nearest Neighbors is a free SQL Academy lesson on CoddyKit — lesson 2 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 Spatial Distance?

In geospatial databases, distance is the measured separation between two geographic locations. PostGIS provides powerful functions to compute distances between points, lines, polygons, and other geometry types.

Understanding distance queries lets you answer questions like: What is the nearest restaurant to me? or Which customers are within 5 km of our warehouse?

The ST_Distance Function

ST_Distance(geom_a, geom_b) returns the minimum distance between two geometry objects. By default, when using plain geometry types, the result is in the same units as the coordinate reference system (usually degrees for EPSG:4326).

To get meaningful results in meters, you should use geography types or reproject your data.

SELECT ST_Distance(
  ST_MakePoint(28.9784, 41.0082)::geography,
  ST_MakePoint(29.0100, 41.0200)::geography
) AS distance_meters;

Setting Up a Sample Table

Let us create a simple table of places with geographic coordinates. We will use the geography type so that all distance calculations automatically return meters — no manual projection needed.

This table will hold cafes in a city, each with a name and a location stored as a point.

CREATE TABLE cafes (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  location GEOGRAPHY(Point, 4326)
);

INSERT INTO cafes (name, location) VALUES
  ('Cafe Alpha',   ST_MakePoint(28.9784, 41.0082)::geography),
  ('Cafe Beta',    ST_MakePoint(28.9900, 41.0150)::geography),
  ('Cafe Gamma',   ST_MakePoint(29.0100, 41.0200)::geography),
  ('Cafe Delta',   ST_MakePoint(28.9650, 40.9980)::geography),
  ('Cafe Epsilon', ST_MakePoint(29.0250, 41.0350)::geography);

Finding Distance from a Fixed Point

Once you have location data, you can compute the distance from every row to a reference point. Here we calculate how far each cafe is from a user standing at coordinates (28.9800, 41.0100).

The result is in meters because we used the geography type.

SELECT
  name,
  ROUND(
    ST_Distance(
      location,
      ST_MakePoint(28.9800, 41.0100)::geography
    )::NUMERIC
  ) AS distance_m
FROM cafes
ORDER BY distance_m;

Finding the Nearest Neighbor

To find the single nearest location to a given point, sort by distance and take only one row with LIMIT 1. This is the classic nearest-neighbor query.

This pattern is extremely common in location-aware applications: finding the nearest store, doctor, or transit stop.

SELECT
  name,
  ROUND(
    ST_Distance(
      location,
      ST_MakePoint(28.9800, 41.0100)::geography
    )::NUMERIC
  ) AS distance_m
FROM cafes
ORDER BY location <-> ST_MakePoint(28.9800, 41.0100)::geography
LIMIT 1;

The <-> Operator for Nearest Neighbor

PostGIS provides the <-> operator (KNN — K Nearest Neighbors) which is index-aware. Unlike ST_Distance in an ORDER BY clause, <-> can use a GiST spatial index to avoid scanning every row.

Always prefer <-> in ORDER BY when you need the nearest neighbors efficiently. Create an index like this to support it:

CREATE INDEX ON cafes USING GIST (location);

SELECT
  name,
  ROUND(
    ST_Distance(
      location,
      ST_MakePoint(28.9800, 41.0100)::geography
    )::NUMERIC
  ) AS distance_m
FROM cafes
ORDER BY location <-> ST_MakePoint(28.9800, 41.0100)::geography
LIMIT 5;

Filtering by Distance (ST_DWithin)

ST_DWithin(geom_a, geom_b, distance) returns TRUE when two geometries are within a specified distance of each other. For geography types, the distance is in meters.

This is more efficient than ST_Distance(...) < radius in a WHERE clause because ST_DWithin is index-aware and stops early once the condition is met.

SELECT
  name,
  ROUND(
    ST_Distance(
      location,
      ST_MakePoint(28.9800, 41.0100)::geography
    )::NUMERIC
  ) AS distance_m
FROM cafes
WHERE ST_DWithin(
  location,
  ST_MakePoint(28.9800, 41.0100)::geography,
  2000
)
ORDER BY distance_m;

Top-N Nearest with a Radius Guard

A common production pattern combines ST_DWithin as a pre-filter with <-> ordering. The ST_DWithin check uses the spatial index to quickly discard far-away rows, and then the remaining candidates are sorted by exact distance.

This gives you the N nearest locations within a maximum radius very efficiently.

SELECT
  name,
  ROUND(
    ST_Distance(
      location,
      ST_MakePoint(28.9800, 41.0100)::geography
    )::NUMERIC
  ) AS distance_m
FROM cafes
WHERE ST_DWithin(
  location,
  ST_MakePoint(28.9800, 41.0100)::geography,
  5000
)
ORDER BY location <-> ST_MakePoint(28.9800, 41.0100)::geography
LIMIT 3;

Distance Between Two Tables (Cross Distance)

You can compute distances between rows in two different tables using a JOIN combined with ST_Distance. This is useful for matching, for example, each customer to their nearest warehouse or each incident to the nearest hospital.

Below we find the nearest cafe for each user in a users table using a lateral join — a powerful PostgreSQL pattern for per-row subqueries.

SELECT
  u.username,
  c.name AS nearest_cafe,
  ROUND(ST_Distance(u.location, c.location)::NUMERIC) AS distance_m
FROM (
  VALUES
    ('alice', ST_MakePoint(28.9810, 41.0095)::geography),
    ('bob',   ST_MakePoint(29.0200, 41.0300)::geography)
) AS u(username, location)
CROSS JOIN LATERAL (
  SELECT name, location
  FROM cafes
  ORDER BY location <-> u.location
  LIMIT 1
) c;

Formatting Distance Output

Raw distances in meters can be hard to read. You can format them as kilometers or add human-friendly labels using CASE expressions and string formatting. Here is a query that presents distance in meters for short distances and kilometers for longer ones.

SELECT
  name,
  CASE
    WHEN ST_Distance(location, ST_MakePoint(28.9800, 41.0100)::geography) < 1000
    THEN ROUND(ST_Distance(location, ST_MakePoint(28.9800, 41.0100)::geography)::NUMERIC)
         || ' m'
    ELSE ROUND((ST_Distance(location, ST_MakePoint(28.9800, 41.0100)::geography) / 1000.0)::NUMERIC, 2)
         || ' km'
  END AS formatted_distance
FROM cafes
ORDER BY location <-> ST_MakePoint(28.9800, 41.0100)::geography;

Assigning a Rank by Distance

Window functions like RANK() and ROW_NUMBER() pair perfectly with distance queries. You can assign a proximity rank to each location relative to a user, which is useful for building ranked recommendation lists or search results sorted by closeness.

SELECT
  name,
  ROUND(
    ST_Distance(
      location,
      ST_MakePoint(28.9800, 41.0100)::geography
    )::NUMERIC
  ) AS distance_m,
  ROW_NUMBER() OVER (
    ORDER BY location <-> ST_MakePoint(28.9800, 41.0100)::geography
  ) AS proximity_rank
FROM cafes;

Knowledge Check

Test your understanding of distance queries and nearest-neighbor lookups in PostGIS.

Lesson Recap

In this lesson you learned how to work with spatial distances and nearest-neighbor queries in PostGIS:

ST_Distance computes the exact distance between two geometries; use the geography type to get meters automatically.
ST_DWithin efficiently filters rows within a radius using the spatial index — prefer it over ST_Distance < radius in WHERE clauses.
The <-> operator (KNN) in ORDER BY is index-aware and is the fastest way to retrieve the nearest N neighbors.
Combine ST_DWithin as a pre-filter with <-> ordering for the most efficient top-N within radius queries.
CROSS JOIN LATERAL lets you find the nearest match from another table on a per-row basis.
Window functions like ROW_NUMBER() can assign proximity ranks to your distance-ordered results.

These patterns form the backbone of location-aware features in real-world applications.

Frequently asked questions

Is the “Distance and Nearest Neighbors” lesson free?

Yes — the full text of “Distance and Nearest Neighbors” 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 “Distance and Nearest Neighbors”?

Find what's close by. 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 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Distance and Nearest Neighbors” 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

← Back to SQL Academy