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

Instruction

Whoa! It looks like Mark has tons of routes to choose from. However, remember that if one of the geometries is entirely inside the other, ST_Overlaps returns FALSE. To check whether a geometry is entirely inside another one, we can use the ST_Within function, like so: ST_Within(geometryA, geometryB).

ST_Overlaps

The ST_Within function checks whether geometryA. is entirely inside geometryB. The order of the arguments matters here. If we reverse them such that geometryB comes first, then the function will return TRUE if geometryB is contained entirely within the space that geometryA occupies.

Take a look at an example query that relies on this function:

SELECT * 
FROM sf_atms sa
JOIN sf_planning_districts spd
ON ST_Within(sa.coordinates, spd.boundaries)
WHERE spd.name = 'Downtown'

The query above lists all the ATMs that are located within the Downtown district. As you see, the database engine treats spatial joins just like any other type of join. This means we can also add additional clauses to our queries, such as WHERE, GROUP BY, and so on.

Exercise

Mark is in the Northeast district and wants to know how many restaurants are worth visiting in this area. Show the number of restaurants within this district that have rating higher than 4.0.

Stuck? Here's a hint!

Use the ST_Within function. Pass in the coordinates column as the first argument, not the second.

Console

Code editor

Result

TableConsole