Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
ST_Equals
Relationships Between Geometries
10. ST_Intersects
ST_Distance and ST_Dwithin
Spatial joins practice
Summary

Instruction

Alright! As you hopefully noticed, these four functions we just covered are all very similar, but they each display unique behavior. All of them allow you to examine relations between various geometries in greater detail.

Sometimes, though, you don't really want to be so meticulous, or your geometry structures are not very precise and you can't be sure whether two geometries touch or overlap each other. What to do, then?

There is another function called ST_Intersects(geometryA, geometryB), which is a generalized version of the three we got to know before. It checks if two geometries have any space in common. If they do, it returns TRUE. In other words, if the geometries overlap OR touch each other OR one of them is within the other, ST_Intersects returns TRUE.

ST_Intersects

Take a look at the following query:

SELECT 
  COUNT(*) 
FROM sf_planning_districts spd
JOIN sf_bicycle_routes sbr
  ON ST_Intersects(
    spd.boundaries,
    sbr.course)
WHERE spd.name = 'Northeast'

This query will return the number of bicycle routes that are completely or partially inside the Northeast district.

Exercise

Mark is planning a sightseeing trip. He wants to know how many attractions there are in each district to plan his trip as efficiently as possible.

Show the name of a district, together with the number of attractions in that district. Order the results in descending order, based on the number of attractions.

Stuck? Here's a hint!

Use ST_Intersects in the ON clause.

You also have to use a GROUP BY clause.