tz_fbpop.sql

# Table Population: The Facebook population data, inputted by CSV. Points
# Table tzadmin: Tanzania administration boundaries. Polygons
# Table fb_pop: The sum of population for a given admin feature ID 
# Table tzadmin_fbpop: The joined table that sums the sum of population and geographic features.

#Generate the table with the right values
CREATE TABLE population
	(
  gid serial NOT NULL,
  the_geom geometry,
  population float8,

  	  CONSTRAINT population_pkey PRIMARY KEY (gid),
	  CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),
	  CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL),
	  CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326)
);


CREATE INDEX population_the_geom_gist
	  ON population
	  USING gist
	  (the_geom );

#Create the geometry by concatenating both the longitude and latitude together with their CRS

UPDATE population
SET the_geom = ST_GeomFromText('POINT(' || longitude || ' ' || latitude || ')',4326);

# Create a separate table that sums the population within each polygon, based on the ST_Contains. Replicates Point in Polygon. 
CREATE TABLE fb_pop AS
SELECT tzadmin.id, SUM(population.population)
FROM population,tzadmin
WHERE ST_Contains(tzadmin.the_geom, population.the_geom)
GROUP BY tzadmin.id

#Join the table with the 
CREATE TABLE tzadmin_fbpop AS
SELECT gid, the_geom, latitude, longitude, sum FROM 
INNER JOIN fb_pop ON (population.gid = fb_pop.id);