Skip to content

Instantly share code, notes, and snippets.

@spara
Created January 10, 2012 20:22
Show Gist options
  • Save spara/1590966 to your computer and use it in GitHub Desktop.
Save spara/1590966 to your computer and use it in GitHub Desktop.
Import 2010 Census geo_header file into Postgresql
INSERT INTO geo_header_sf1 (fileid, stusab, sumlev, geocomp, chariter, cifsn, logrecno, region, division, state, county, countycc, countysc, cousub, cousubcc, cousubsc, place, placecc, placesc, tract, blkgrp, block, iuc, concit, concitcc, concitsc, aianhh, aianhhfp, aianhhcc, aihhtli, aitsce, aits, aitscc, ttract, tblkgrp, anrc, anrccc, cbsa, cbsasc, metdiv, csa, necta, nectasc, nectadiv, cnecta, cbsapci, nectapci, ua, uasc, uatype, ur, cd, sldu, sldl, vtd, vtdi, reserve2, zcta5, submcd, submcdcc, sdelem, sdsec, sduni, arealand, areawatr, name, funcstat, gcuni, pop100, hu100, intptlat, intptlon, lsadc, partflag, reserve3, uga, statens, countyns, cousubns, placens, concitns, aianhhns, aitsns, anrcns, submcdns, cd113, cd114, cd115, sldu2, sldu3, sldu4, sldl2, sldl3, sldl4, aianhhsc, csasc, cnectasc, memi, nmemi, puma, reserved)
SELECT
trim(substring(data,1,6)) AS fileid,
trim(substring(data,7,2)) AS stusab,
trim(substring(data,9,3)) AS sumlev,
trim(substring(data,12,2)) AS geocomp,
trim(substring(data,14,3)) AS chariter,
trim(substring(data,17,2)) AS cifsn,
trim(substring(data,19,7))::integer AS logrecno,
trim(substring(data,26,1)) AS region,
trim(substring(data,27,1)) AS division,
trim(substring(data,28,2)) AS state,
trim(substring(data,30,3)) AS county,
trim(substring(data,33,2)) AS countycc,
trim(substring(data,35,2)) AS countysc,
trim(substring(data,37,5)) AS cousub,
trim(substring(data,42,2)) AS cousubcc,
trim(substring(data,44,2)) AS cousubsc,
trim(substring(data,46,5)) AS place,
trim(substring(data,51,2)) AS placecc,
trim(substring(data,53,2)) AS placesc,
trim(substring(data,55,6)) AS tract,
trim(substring(data,61,1)) AS blkgrp,
trim(substring(data,62,4)) AS block,
trim(substring(data,66,2)) AS iuc,
trim(substring(data,68,5)) AS concit,
trim(substring(data,73,2)) AS concitcc,
trim(substring(data,75,2)) AS concitsc,
trim(substring(data,77,4)) AS aianhh,
trim(substring(data,81,5)) AS aianhhfp,
trim(substring(data,86,2)) AS aianhhcc,
trim(substring(data,88,1)) AS aihhtli,
trim(substring(data,89,3)) AS aitsce,
trim(substring(data,92,5)) AS aits,
trim(substring(data,97,2)) AS aitscc,
trim(substring(data,99,6)) AS ttract,
trim(substring(data,105,1)) AS tblkgrp,
trim(substring(data,106,5)) AS anrc,
trim(substring(data,111,2)) AS anrccc,
trim(substring(data,113,5)) AS cbsa,
trim(substring(data,118,2)) AS cbsasc,
trim(substring(data,120,5)) AS metdiv,
trim(substring(data,125,3)) AS csa,
trim(substring(data,128,5)) AS necta,
trim(substring(data,133,2)) AS nectasc,
trim(substring(data,135,5)) AS nectadiv,
trim(substring(data,140,3)) AS cnecta,
trim(substring(data,143,1)) AS cbsapci,
trim(substring(data,144,1)) AS nectapci,
trim(substring(data,145,5)) AS ua,
trim(substring(data,150,2)) AS uasc,
trim(substring(data,152,1)) AS uatype,
trim(substring(data,153,1)) AS ur,
trim(substring(data,154,2)) AS cd,
trim(substring(data,156,3)) AS sldu,
trim(substring(data,159,3)) AS sldl,
trim(substring(data,162,6)) AS vtd,
trim(substring(data,168,1)) AS vtdi,
trim(substring(data,169,3)) AS reserve2,
trim(substring(data,172,5)) AS zcta5,
trim(substring(data,177,5)) AS submcd,
trim(substring(data,182,2)) AS submcdcc,
trim(substring(data,184,5)) AS sdelem,
trim(substring(data,189,5)) AS sdsec,
trim(substring(data,194,5)) AS sduni,
trim(substring(data,199,14))::integer AS arealand,
trim(substring(data,213,14))::integer AS areawatr,
trim(substring(data,227,90)) AS name,
trim(substring(data,317,1)) AS funcstat,
trim(substring(data,318,1)) AS gcuni,
trim(substring(data,319,9))::integer AS pop100,
trim(substring(data,328,9))::integer AS hu100,
trim(substring(data,337,11)) AS intptlat,
trim(substring(data,348,12)) AS intptlon,
trim(substring(data,360,2)) AS lsadc,
trim(substring(data,362,1)) AS partflag,
trim(substring(data,363,6)) AS reserve3,
trim(substring(data,369,5)) AS uga,
trim(substring(data,374,8)) AS statens,
trim(substring(data,382,8)) AS countyns,
trim(substring(data,390,8)) AS cousubns,
trim(substring(data,398,8)) AS placens,
trim(substring(data,406,8)) AS concitns,
trim(substring(data,414,8)) AS aianhhns,
trim(substring(data,422,8)) AS aitsns,
trim(substring(data,430,8)) AS anrcns,
trim(substring(data,438,8)) AS submcdns,
trim(substring(data,446,2)) AS cd113,
trim(substring(data,448,2)) AS cd114,
trim(substring(data,450,2)) AS cd115,
trim(substring(data,452,3)) AS sldu2,
trim(substring(data,455,3)) AS sldu3,
trim(substring(data,458,3)) AS sldu4,
trim(substring(data,461,3)) AS sldl2,
trim(substring(data,464,3)) AS sldl3,
trim(substring(data,467,3)) AS sldl4,
trim(substring(data,470,2)) AS aianhhsc,
trim(substring(data,472,2)) AS csasc,
trim(substring(data,474,2)) AS cnectasc,
trim(substring(data,476,1)) AS memi,
trim(substring(data,477,1)) AS nmemi,
trim(substring(data,478,5)) AS puma,
trim(substring(data,483,18)) AS reserved
FROM geo_header_staging;
@owenam
Copy link

owenam commented Mar 24, 2013

Thanks, this was a big help!

Change lines 66 and 67 to...

trim(substring(data,199,14))::bigint AS arealand,
trim(substring(data,213,14))::bigint AS areawatr,

...in order to handle places with lots of land or lots of water :)

@Irwin-hu
Copy link

Irwin-hu commented Jul 9, 2013

Hi, owenam
Can you send me the 2010 Census geo_header file to test, or give me a URL to download the test data?
Thanks a lot!

@seanknox
Copy link

+1 on changing arealand and areawatr to bigint/numeric. Very handy script. Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment