Database Reference
In-Depth Information
estimatethatislikelytobebetterthanpureintersectsorcentroidqueries.Thus,we
employ a proportional sum.
How to do it...
Astheproblemofaproportionalsumisagenericproblem,wewillwritetheunder-
lyingproportioningasafunction.Afunctiontakesinputsandreturnsavalue.Inour
case, we want our proportioning function to take two geometries, that is, the geo-
metry of our buffered trail and block groups as well as the value we want propor-
tioned, 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
andblockgroup,wewanttofindtheproportionthattheintersectionisovertheover-
all 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 precedingquery in its full form looks as follows:
