Introduction
ST_Equals
Relationships Between Geometries
ST_Distance and ST_Dwithin
Spatial joins practice
19. Spatial joins - exercise 1
Summary

Instruction

Good! Of course, spatial functions can be used with aggregates, ORDER BY and other SQL features. Take a look at this query:

SELECT 
  sfn.name, 
  sum(both_parents_family_household) 
FROM sf_planning_districts sfn
JOIN sf_household_types sfea
ON ST_Intersects(
    sfn.boundaries,
    sfea.block_boundaries)
GROUP BY sfn.name;

The query above shows the name of each district alongside the number of households with two parent families in that district. We used ST_Intersects(...) in the ON clause to join both tables. Then we also used a GROUP BY clause to show data about multiple districts in a single query.

Exercise

Calculate the number of hotels in each district (count all hotels that intersect with a given district). Show two columns: district name and count. Sort the results by count in descending order.

Stuck? Here's a hint!

Use tables sf_hotels and sf_planning_districts. Use COUNT(*) as well.

Console

Code editor

Result

TableConsole