Database Reference
In-Depth Information
Using
polygon
overlays
for
proportional census estimates
PostgreSQL functions abound for the aggregation of tabular data, including sum,
count,min,max,andsoon.PostGISasaframeworkdoesnotexplicitlyhavespatial
equivalents of these, but this does not prevent us from building functions using the
aggregates in concert with PostGIS's spatial functionality.
In this recipe, we will explore spatial summarization with the United States Census
data.USCensusdata,bynature,isaggregateddata.Thisisdoneintentionallytopro-
tect the privacy of citizens. But when it comes to doing analyses with this data, the
aggregatenatureofthedatacanbecomeproblematic.Therearesometrickstodis-
aggregatedata.Amongstthesimplestoftheseistheuseofaproportionalsumbased
on area, which we will do in this exercise.
Getting ready
Theproblemathandisthataproposedtrailhasbeendrawninordertoprovideser-
vices for the public. This example could apply to road construction or even finding
sites for commercial properties for the purpose of provisioning services.
First, perform a quick data load using the following commands:
shp2pgsql -s 3734 -d -i -I -W LATIN1 -g the_geom
census chp02.trail_census | psql -U me -d
postgis_cookbook
shp2pgsql -s 3734 -d -i -I -W LATIN1 -g the_geom
trail_alignment_proposed_buffer
chp02.trail_buffer | psql -U me -d postgis_cookb
ook
shp2pgsql -s 3734 -d -i -I -W LATIN1 -g the_geom
trail_alignment_proposed
chp02.trail_alignment_prop | psql -U me -d
postgis_cookbook
The preceding commands will produce the following output:
