Improve readme

[iandees]

These are notes for what I had to do when ingesting the 2013 1-yr dataset:

Adding new data release

• Update census-table-metadata:
  • Makefile
  • add new line in all section
  • add new clean-all step
  • add _merge_5_6.xls and _table_shells.xls download steps
  • add census_table_metadata.csv step
  • census_metadata.sql
  • add table/column metadata creates
  • census_metadata_drop.sql
  • add table/column metadata drops
  • census_metadata_load.sql
  • add COPYs
  • Discover that the shells .xls file they include is actually an .xlsx and that python xlrd can't read the formatting info
  • Use Excel to save it as a normal .xls and replace the file
• Update census-postgres
  • modify meta-scripts/build_sql_files.py:
  • add a new key in config for the new release
  • Open the Sequence_Number_and_Table_Number_Lookup.xls file and save it as a CSV
  • Copy CSV to /mnt/tmp/acs2013_1yr
  • Make sure it's named .txt, not .csv
  • Make sure you've unzipped /mnt/tmp/acs2013_1yr/All_Geographies
  • using census-postgres as your working dir:
  • mkdir acs2013_1yr
  • python meta-scripts/build_sql_files.py --working_dir=acs2013_1yr acs2013_1yr
  • copy non-changing sql files from previous release to this one:
  • cd acs2013_1yr
  • cp ../acs2012_1yr/create_geoheader.sql \ ../acs2012_1yr/create_tmp_geoheader.sql \ ../acs2012_1yr/geoheader_comments.sql \ ../acs2012_1yr/parse_tmp_geoheader.sql \ ../acs2012_1yr/README.md \ .
  • update copied sql files to point to new release's schema
  • vi create_geoheader.sql # find/replace 2012 with 2013
  • vi create_tmp_geoheader.sql
  • vi geoheader_comments.sql
  • vi parse_tmp_geoheader.sql
  • vi README.md
• Update census-postgres-scripts
  • make a copy of a 02_download script and modify it for the new release
  • make a copy of a 03_import script and modify it for the new release
  • using census-postgres-scripts as your working dir:
  • (Normally you would run ./02_download_acs_2013_1yr.sh here to fetch the data, but you've already done that because you had to copy the data from embargo site manually: can't wget embargoed stuff)
  • (Also make sure you have a .pgpass file with your postgres database credentials in it so you don't haev to type your password a dozen times in the import script)
  • ./03_import_acs_2013_1yr.sh
  • (You'll see a lot of NOTICEs flow by, but it's only important if it's an ERROR)
• Insert the table metadata
  • from census_metadata.sql
  • copy and execute in a psql terminal the CREATE TABLE and CREATE INDEX's for the new release
  • from census_metadata_load.sql
  • (Using a psql terminal, execute the following:. Note that a straight COPY won't work from non-superuser.)
  • \copy acs2013_1yr.census_table_metadata FROM '/home/ubuntu/census-table-metadata/precomputed/acs2013_1yr/census_table_metadata.csv' WITH csv ENCODING 'utf8' HEADER
  • \copy acs2013_1yr.census_column_metadata FROM '/home/ubuntu/census-table-metadata/precomputed/acs2013_1yr/census_column_metadata.csv' WITH csv ENCODING 'utf8' HEADER
• Create a new database dump
  • pg_dump -h localhost -U census -n acs2013_1yr | gzip -c > acs2013_1yr_backup.sql.gz
• After embargo, remember to check in your work:
  • census-postgres/acs2013_1yr
  • census-table-metadata/precomputed/acs2013_1yr

[JoeGermuska]

ok, here's the justification for the metadata working group right here.

[iandees]

This doesn't include all the work that's happened in the table metadata parsers. Those XLS files are different every single release...

[iandees]

This information is present in DATA_UPDATES.md.