{"id":1612,"date":"2014-08-13T15:11:27","date_gmt":"2014-08-13T22:11:27","guid":{"rendered":"http:\/\/blog.light42.com\/wordpress\/?p=1612"},"modified":"2014-08-14T08:50:38","modified_gmt":"2014-08-14T15:50:38","slug":"acs-5yr-viz-processing","status":"publish","type":"post","link":"http:\/\/blog.light42.com\/wordpress\/?p=1612","title":{"rendered":"ACS 5yr Viz Processing"},"content":{"rendered":"<p>A systematic way to choose, extract and visualize data from the massive <a href=\"http:\/\/www.census.gov\/newsroom\/releases\/archives\/american_community_survey_acs\/cb12-228.html\" title=\"ACS 5 Year\" target=\"_blank\"><strong>American Community Survey 5 Year<\/strong><\/a> census product is a challenge. I have written python code to ingest <a href=\"http:\/\/www2.census.gov\/acs2012_5yr\/summaryfile\/2008-2012_ACSSF_By_State_All_Tables\/\" title=\"raw inputs\" target=\"_blank\">raw inputs<\/a> into tables, and a small relational engine to handle the verbose naming.<\/p>\n<p>An extraction and visualization process is underway&#8230;  something like the following:<\/p>\n<p>0) bulk tables in all geographies for all states<br \/>\n1a)&nbsp;&nbsp;&nbsp;define a batch of tables to extract by <code>table_id<\/code><br \/>\n1b)&nbsp;&nbsp;&nbsp;choose a state or territory<br \/>\n1c)&nbsp;&nbsp;&nbsp;choose a geographic summary level<\/p>\n<p><em>for example:<\/em><\/p>\n<pre>\r\nSTATE  California (FIPS 06)\r\nTABLE  ('B01001', 'SEX BY AGE', 'Age-Sex', 'Universe:  Total population')\r\n  GEO  Tracts (Summary level 140 - State-County-Census Tract)\r\n<\/pre>\n<p>Once the choice is made, SQL + Python is executed, either as a standalone program in Linux or in the <a href=\"http:\/\/ipython.org\/notebook.html\" title=\"IPython Notebook\" target=\"_blank\"><strong>IPython Notebook<\/strong><\/a>. The code creates a working schema in PostgreSQL, copies table subsets into the new schema, and JOINs them with TIGER geometry to get spatial data. A preliminary, working version looks something like this:<\/p>\n<p><a href=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2014\/08\/domaketractstable.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2014\/08\/domaketractstable.png\" alt=\"domaketractstable\" width=\"639\" height=\"653\" class=\"alignnone wp-image-1618\" srcset=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2014\/08\/domaketractstable.png 639w, http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2014\/08\/domaketractstable-293x300.png 293w\" sizes=\"(max-width: 639px) 100vw, 639px\" \/><\/a><\/p>\n<p>graphical browsing of the results in QGis:<br \/>\n<\/p>\n<p><a href=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2014\/08\/acs5yr_viz_progress1.png\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2014\/08\/acs5yr_viz_progress1.png\" alt=\"acs5yr_viz_progress1\" width=\"592\" height=\"508\" class=\"alignnone size-full wp-image-1642\" srcset=\"http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2014\/08\/acs5yr_viz_progress1.png 592w, http:\/\/blog.light42.com\/wordpress\/wp-content\/uploads\/2014\/08\/acs5yr_viz_progress1-300x257.png 300w\" sizes=\"(max-width: 592px) 100vw, 592px\" \/><\/a><\/p>\n<p>geographic summaries defined in <strong>ACS_2008-2012_SF_Tech_Doc<\/strong>:<br \/>\n  Appendix F: ACS 5-year Summary Levels\/Components for Detailed Tables<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A systematic way to choose, extract and visualize data from the massive American Community Survey 5 Year census product is a challenge. I have written python code to ingest raw inputs into tables, and a small relational engine to handle the verbose naming. An extraction and visualization process is underway&#8230; something like the following: 0) [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,4,6],"tags":[],"_links":{"self":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1612"}],"collection":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1612"}],"version-history":[{"count":18,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1612\/revisions"}],"predecessor-version":[{"id":1643,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1612\/revisions\/1643"}],"wp:attachment":[{"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1612"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1612"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.light42.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1612"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}