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

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.