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. 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.

Console

Code editor

Result

TableConsole