Introduction
ST_Equals
Relationships Between Geometries
ST_Distance and ST_Dwithin
Spatial joins practice
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.
