Database Reference
In-Depth Information
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;
How it works...
Sincewehavewrittenthequeryasafunction,thequeryusesthe
SELECT
statement
toloopthroughallavailablerecordsandgiveusaproportionedpopulation.Anastute
readerwillnotethatwehavenotyetdoneanyworkonsummarization;wehaveonly
workedontheproportionalityportionoftheproblem.Wecandothesummarization
upon calling the function using PostgreSQL's built-in aggregate functions. What is
neat about this approach is that we need not just apply a sum, but could also cal-
culate other aggregates such as min or max. In the following example, we will just
apply a sum:
SELECT
ROUND(SUM(chp02.proportional_sum(a.the_geom,
b.the_geom, b.pop))) FROM
chp02.trail_buffer AS a, chp02.trail_census
as b
WHERE ST_Intersects(a.the_geom, b.the_geom)
GROUP BY a.gid;
Thevaluereturnedisquitedifferent(populationof96,081),whichismorelikelytobe
accurate.
