Instruction
Great! Of course, don't forget that you can also put spatial functions inside aggregates. Take a look at how that's done:
SELECT
sfn.name,
SUM(ST_Area(
ST_Transform(
sfh.boundaries,26910)))
FROM sf_planning_districts sfn
JOIN sf_hotels sfh
ON ST_Intersects(
sfn.boundaries,
sfh.boundaries)
GROUP BY sfn.name
ORDER BY SUM(
ST_Area(
ST_Transform(
sfh.boundaries,26910))) DESC;
The query above finds the total area of all hotels in each district. In order to find the area of each hotel, we have to first use ST_Area with a hotel's boundaries as the argument. Then, we have to sum all such areas in a given district, so we use SUM(...) with ST_Area(...) as its argument.
Exercise
For each district, show the district name and the total length (in kilometers) of all bicycle routes that are completely contained inside that district as the total_length column. Sort the results by the sum in descending order.
Stuck? Here's a hint!
You will have to use ST_Length with ST_Transform to find the lengths of bicycle routes. In the ON clause, use ST_Contains.
