liveBook · Manning

ST_Distance

As explained in Just Use Postgres!

ST_Distance is a PostGIS function that calculates the minimum distance between two geometries or geography values.

ST_Distance calculates the minimum two-dimensional Cartesian distance between two geometries. The result uses the units defined by the geometries’ spatial reference system. section 10.5.1

Find nearby restaurants

Combine ST_Distance with ST_DWithin to filter and rank restaurants around a hotel: WITH roost_hotel AS ( SELECT way FROM florida.planet_osm_point WHERE name = 'Roost Apartment Hotel' AND tourism = 'hotel' ) SELECT p.name, round(ST_Distance(h.way, p.way)) AS distance_meters, round(ST_Distance(h.way, p.way) * 3.28) AS distance_feet FROM roost_hotel AS h JOIN florida.planet_osm_point AS p ON ST_DWithin(h.way, p.way, 500) WHERE p.amenity = 'restaurant' ORDER BY distance_meters LIMIT 5; The query returns Wagamama at 68 meters, followed by Boulon Brasserie at 132 meters. section 10.5.1

Use ST_Distance to calculate exact distances after filtering candidates. It is not index-aware; using it directly in a distance predicate can trigger a parallel full-table scan, whereas ST_DWithin can use a GiST index for initial filtering. section 10.6.2

Listing 10.14 Finding the closest restaurants 
WITH roost_hotel AS (
  SELECT way FROM florida.planet_osm_point
  WHERE name = 'Roost Apartment Hotel' AND tourism='hotel'
)
SELECT p.name, p.amenity,
  round(ST_Distance(h.way,p.way)) as distance_meters,
  round(ST_Distance(h.way,p.way) * 3.28) as distance_feet
FROM roost_hotel as h 
JOIN florida.planet_osm_point AS p
  ON ST_DWithin(h.way, p.way, 500)
WHERE p.amenity = 'restaurant'
ORDER BY distance_meters LIMIT 5;

Runnable SQL and sample output demonstrate filtering within 500 meters, calculating distances in meters and feet, and ordering the nearest results. section 10.5.1

How Just Use Postgres! covers it

How deep it goes
Defines planar distance, demonstrates runnable queries, and explains index behavior without covering deeper distance algorithms.
Examples
Demonstrates nearest restaurants and shops around named locations, including meter and feet output.
What you can do after
Apply it via PostGIS SQL to calculate, display, sort, and compare distances between stored geometries.
How it connects
Connects ST_Distance with ST_DWithin, spatial reference systems, geometry values, and GiST indexes.
Cover of Just Use Postgres!
liveBookmanning.com
Read Just Use Postgres! on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Just Use Postgres!. The section links point to the parts of the book it draws on.