Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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

This comment has been minimized.

Copy link

@owenam 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

This comment has been minimized.

Copy link

@Irwin-hu 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

This comment has been minimized.

Copy link

@seanknox seanknox commented Oct 25, 2013

+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