Introduction
ST_Equals
Relationships Between Geometries
ST_Distance and ST_Dwithin
14. ST_Distance
Spatial joins practice
Summary

Instruction

Good job! Let's move on. Another very useful spatial function is ST_Distance(geometryA, geometryB). It returns the smallest distance between two geometries. For instance:

SELECT
  ST_Distance(
    ST_Transform(
      sep.coordinates, 26910),
    ST_Transform(
      sa.coordinates, 26910))
FROM sf_atms sa
JOIN sf_restaurants sep
ON  ST_Distance(
      ST_Transform(
        sep.coordinates, 26910),
      ST_Transform(
        sa.coordinates, 26910)) < 5000
WHERE sep.id = 1

The query above returns the distance between the restaurant with id = 1 and all ATMs that are closer than 5000 meters. Note that we used ST_Transform to get the distance in meters. In the ON clause we compare the result of the function with 5000 to make a boolean expression, e.g. we check whether it is true that a given atm is closer than 5000 meters.

Exercise

Mark visits Fisherman's Wharf. Unfortunately, they only accept cash, and Mark currently doesn't have any on hand! Show the distances to ATMs machines within 1000 meters from the Fisherman's Wharf.

Stuck? Here's a hint!

You can find Fisherman's Wharf in the sf_sights table.

In the JOIN clause, use ST_Distance. Inside, remember to use ST_Transform with SRID = 26910.

Note that the name of this district has an apostrophe (Fisherman's Wharf). In order to escape it you have to double the appostrophe, e.g. WHERE x = 'Fisherman''s Wharf'

Console

Code editor

Result

TableConsole