For our analysis, we will use the proportional_sum function from Chapter 2, Structures That Work, so if you have not added this to your PostGIS tool belt, run the following commands:

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;

The proportional_sum function will take our input geometry into account and the count value of the population and return an estimate of the proportional population.

Now we need to load our census data. Use the following command:

shp2pgsql -s 3734 -d -i -I -W LATIN1 -g the_geom census chp06.census | psql -U me -d postgis_cookbook -h localhost

Also, if you have not yet loaded the data mentioned in the Loading data from OpenStreetMap and finding the shortest path A* recipe, take the time to do so now.

Once all the data is entered, we can proceed with the analysis.