As the problem of a proportional sum is a generic problem, it could apply to many problems. We will write the underlying proportioning as a function. A function takes inputs and returns a value. In our case, we want our proportioning function to take two geometries, that is, the geometry of our buffered trail and block groups as well as the value we want proportioned, and we want it to return the proportioned value:

CREATE OR REPLACE FUNCTION chp02.proportional_sum(geometry,   geometry, numeric) 
  RETURNS numeric AS 
$BODY$ 
-- SQL here 
$BODY$ 
  LANGUAGE sql VOLATILE; 

Now, for the purpose of our calculation, for any given intersection of buffered area and block group, we want to find the proportion that the intersection is over the overall block group. Then this value should be multiplied by the value we want to scale.

In SQL, the function looks like the following query:

SELECT $3 * areacalc FROM 
  (SELECT (ST_Area(ST_Intersection($1, $2)) / ST_Area($2)):: numeric AS areacalc 
  ) AS areac; 

The preceding query in its full form looks as follows:

CREATE OR REPLACE FUNCTION chp02.proportional_sum(geometry,   geometry, numeric) 
  RETURNS numeric AS 
$BODY$ 
    SELECT $3 * areacalc FROM 
      (SELECT (ST_Area(ST_Intersection($1,           $2))/ST_Area($2))::numeric AS areacalc 
      ) AS areac 
; 
$BODY$ 
  LANGUAGE sql VOLATILE;