--- This is file is here mainly for completeness. There is no fancy
--- spatial data manipulations here it is an example of creating a
--- plain vanilla non spatial table is postgreSQL.  The table is
--- important to the project however, as we will need to merge
-- census data with our usa_tiger_counties file in order to produce
-- the grid that we need in order to run Rcartogram() that grid needs
-- to hold some measure akin to population density and that number is
-- going to come from our dc_2009_est table.

-- In order to deal with the variable names we also create and
-- populate a table called dc_vnames which has brief descriptions of
-- each variable.

-- NOTE that this code creates a file in the public schema. The file
-- already exists there so you do not need to create it.  And anyway
-- you can't because you only have permission to read from public.

set client_encoding to 'LATIN1';
drop table public.dc_2009_est;
create table public.dc_2009_est
(GEO_ID          varchar(20) PRIMARY KEY,
GEO_ID2            varchar(20),
SUMLEVEL      integer,
GEO_NAME      varchar(100),
T001001            integer,
T001002            integer,
T001003            integer,
T001004            integer,
T001005            integer,
T001006            integer,
T001007            integer,
T001008            integer,
T001009            integer,
T001010            integer,
T001011            integer,
T001012            integer,
T002001            integer,
T002002            integer,
T002003            integer,
T002004            integer,
T002005            integer,
T002006            integer,
T002007            integer,
T002008            integer,
T002009            integer,
T002010            integer,
T002011            integer,
T002012            integer,
T0032009001      integer,
T0032009002      integer,
T0032009003      integer,
T0032009004      integer,
T0032009005      integer,
T0032009006      integer,
T0032009007      integer,
T0042009001      integer,
T0042009002      integer,
T0042009003      integer,
T0042009004      integer,
T0042009005      integer,
T0042009006      integer,
T0042009007      integer,
T0042009008      integer,
T0042009009      integer,
T0042009010      integer,
T0042009011      integer,
T0042009012      integer,
T0042009013      integer,
T0042009014      integer,
T0042009015      integer,
T0052009001      integer,
T0052009002      integer,
T0052009003      integer,
T0052009004      integer,
T0052009005      integer,
T0062009001      integer,
T0062009002      integer,
T0062009003      integer,
T0062009004      integer,
T0062009005      integer,
T0062009006      integer,
T0062009007      integer,
T0062009008      integer,
T0062009009      integer,
T0062009010      integer,
T0062009011      integer,
T0062009012      integer,
T0062009013      integer,
T0062009014      integer,
T0062009015      integer,
T0062009016      integer,
T0062009017      integer,
T0062009018      integer,
T0062009019      integer,
T0062009020      integer,
T0062009021      integer,
T0062009022      integer,
T0062009023      integer,
T0062009024      integer,
T0062009025      integer,
T0062009026      integer,
T0062009027      integer,
T0062009028      integer,
T0062009029      integer,
T0062009030      integer,
T0062009031      integer,
T0062009032      integer,
T0062009033      integer,
T0062009034      integer,
T0062009035      integer,
T0062009036      integer,
T0062009037      integer,
T0062009038      integer,
T0062009039      integer,
T0072009001      float,
T0072009002      float,
T0072009003      float,
T0082009001      integer,
T0082009002      integer,
T0082009003      integer,
T0082009004      integer,
T0082009005      integer,
T0082009006      integer,
T0082009007      integer,
T0082009008      integer,
T0082009009      integer,
T0082009010      integer,
T0082009011      integer,
T0082009012      integer,
T0082009013      integer,
T0082009014      integer,
T0082009015      integer);

-- Now the dc_vnames table
-- in Unix:  

-- tail --lines=+3 dc_pep_2009_est_data.txt |grep -v 'Puerto Rico'> dc_pep_2009_est_data_modified.txt 
\copy dc_2009_est from '/data/commons/carlm/USA_Counties_shapefile/CountyData/dc_pep_2009_est_data1_modified.txt' delimiter as '|'


-- back in psql...

set client_encoding to 'LATIN1';
drop table public.dc_vnames;
create table public.dc_vnames
(name          varchar(20) PRIMARY KEY,
description   varchar(255));


\copy dc_vnames from '/data/commons/carlm/USA_Counties_shapefile/CountyData/vnames.txt' delimiter as '|';
