Kickstart 2020 with new opportunities! - 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.