Introduction
ST_Equals
Relationships Between Geometries
ST_Distance and ST_Dwithin
16. ST_DWithin additional exercise
Spatial joins practice
Summary

Instruction

That's right! The functions ST_DWithin and ST_Distance can be used interchangeably in an ON condition. If you're looking for ATMs located within 1000 meters of Nourish Hotel, you can use ST_Dwithin:

SELECT 
  sfh.name, 
  sfa.id,
  ST_Distance(
    ST_Transform(
      sfh.boundaries,26910),
    ST_Transform(
      sfa.coordinates,26910)) AS distance
FROM sf_hotels sfh 
JOIN sf_atms sfa
ON ST_DWithin(
  ST_Transform(sfh.boundaries,26910),
  ST_Transform(
    sfa.coordinates,26910),1000)
WHERE sfh.name = 'Nourish Hotel' 
ORDER BY distance DESC;

Alternatively, you can use ST_Distance to get the same:

SELECT
  sfh.name,
  sfa.id, 
  ST_Distance(
    ST_Transform(
      sfh.boundaries,26910),
    ST_Transform(
      sfa.coordinates,26910)) AS distance
FROM sf_hotels sfh 
JOIN sf_atms sfa
ON ST_Distance(
  ST_Transform(
    sfh.boundaries,26910),
  ST_Transform(
    sfa.coordinates,26910)) < 1000
WHERE sfh.name = 'Nourish Hotel' 
ORDER BY distance DESC;

Exercise

How many tram stops are located within 2 kilometers of Nebula Hotel?

Stuck? Here's a hint!

Use the COUNT(...) function and join sf_tram_stops with sf_hotels using ST_Dwithin.

Console

Code editor

Result

TableConsole