Hello,
I was reviewing our code and noticed that we were not joining on county to get
the median income by block group. A sample query to get median income by block
group should instead be:
{{{
select BLK.STUSAB,BLK.STATE,BLK.COUNTY,BLK.TRACT,BLK.BLKGRP,BLK.UHD001 as
MedianIncome,GEOKUMC.* from mpc.geocoded_kumc GEOKUMC
JOIN MPC.ACS_20135A_BLCK_GRP_150_2 BLK ON BLK.TRACT=GEOKUMC.TRACT_ID AND
BLK.STATE=GEOKUMC.FIPSST AND BLK.BLKGRP=SUBSTR(GEOKUMC.BLOCK_ID,1,1) AND
BLK.COUNTY = GEOKUMC.FIPSCO;
}}}
Best,
Maren
From: Dan Connolly
Sent: Monday, January 09, 2017 5:01 PM
To: David Van Riper <[email protected]>
Cc: <[email protected]> <[email protected]>; Sravani Chandaka
<[email protected]>; Maren Wennberg <[email protected]>; Green, Timothy A.
<[email protected]>; Alex Bokov <[email protected]>
Subject: RE: geocoding: how to join at tract, census block resolution?
Thanks, Dave (and Tom and company).
I had lost track of KUMC_census_acs_documentation.docx.
And yes, I have the block_crosswalk. It wasn't obvious to me how to use it when
I first looked at it, but perhaps I'll get it soon. Meanwhile, an example or
two would be nice to have.
Maybe we can trade?
Some notes from Maren and Sravani include:
Sample query to get median incomes by block group
{{{
select BLK.STUSAB,BLK.STATE,BLK.COUNTY,BLK.TRACT,BLK.BLKGRP,BLK.UHD001 as
MedianHouseholdIncome,GEOKUMC.* from mpc.geocoded_kumc GEOKUMC
JOIN MPC.ACS_20135A_BLCK_GRP_150_2 BLK ON BLK.TRACT=GEOKUMC.TRACT_ID AND
BLK.STATE=GEOKUMC.FIPSST AND BLK.BLKGRP=SUBSTR(GEOKUMC.BLOCK_ID,1,1);
}}}
Sample query to get median incomes by Tract
{{{
select TRACT.UHD001,GEOKUMC.* from mpc.geocoded_kumc GEOKUMC
JOIN MPC.ACS_20135A_TRACT_140_2 TRACT ON TRACT.TRACT=GEOKUMC.TRACT_ID AND
TRACT.STATE=GEOKUMC.FIPSST;
}}}
These use an MPC database schema something like:
https://informatics.gpcnetwork.org/trac/Project/attachment/ticket/509/geo_acs_tables.sql
generated using
https://github.com/dckc/i2b2-geo/blob/master/census_schema_gen.py
--
Dan
________________________________
From: David Van Riper [[email protected]]
Sent: Monday, January 09, 2017 4:46 PM
To: Dan Connolly
Cc: <[email protected]<mailto:[email protected]>>; Sravani
Chandaka; Maren Wennberg; Green, Timothy A.; Alex Bokov
Subject: Re: geocoding: how to join at tract, census block resolution?
Dear Dan,
In the Word document (KUMC_census_acs_documentation.docx) available at this
link<https://drive.google.com/folderview?id=0B_ChrVjKL2WYMXE3azlGYm54Zkk&usp=sharing>,
I included the fields that are required to uniquely identify census units in
the various .dat files. They are listed on pages 5 and 6.
Block groups are uniquely identified by concatenating STATE, COUNTY, TRACT, and
BLKGRP. Census tracts are uniquely identified by concatenating STATE, COUNTY,
and TRACT.
On the kumc_geocoded.txt you will want to do the following concatenations for
tract and block group:
FIPSSTCO + TRACT_ID = census tract
FIPSSTCO + TRACT_ID + first character of the BLOCK_ID (left most character) =
block group
FIPSTCO + TRACT_ID + BLOCK_ID = census block
Do you also have access to a zip archive called block_crosswalk.zip? It should
have been provided to each site from Justin Dale. That ZIP archive contains a
crosswalk that links census blocks to a large number of other census
identifiers. You can use the crosswalk to link census identifiers to the
geocoded data on the census block.
Yours,
Dave
On Mon, Jan 9, 2017 at 2:38 PM, Dan Connolly
<[email protected]<mailto:[email protected]>> wrote:
Hi Dave,
More questions!
I'm copying gpc-dev so we can all learn together. Before you reply all, note
the public archive http://listserv.kumc.edu/pipermail/gpc-dev/
I'm working with Maren and Sravani on this stuff. Joining on zip code was
straightforward: we used the zcta5 column of
acs_20135a/zcta_860/ge.00_file.dat.gz.
But now we're trying to join on tract and census block. What columns of
acs_20135a/blck_grp_150/ge.00_file.dat.gz and
acs_20135a/tract_140/ge.00_file.dat.gz should we use? What are their primary
key columns? Which documentation would tell us this?
BLKGRP looks like a single-digit number; it's clearly not a unique key to the
blck_grp_150 data.
TRACT is a bigger number, but it's not a unique key to tract_140 either. It
seems to be unique within state, though. Is that right? Should we join TRACT_ID
and ST_ABRV from geocoded_kumc.txt with TRACT and STUSAB of tract_140?
Have any other GPC sites figured this out yet? MU? UTHSCSA?
For reference, these are the columns of our kumc_geocoded.txt data:
'OID,Join_Count,TARGET_FID,Loc_name,Status,Score,Match_type,'
'X,Y,Match_addr,DISP_LON,DISP_LAT,SIDE,'
'ARC_Address,ARC_City,ARC_State,'
'ARC_Zip,ADDRESS,CITY,STATE,ZIP,New_X,New_Y,ID,BLOCK_ID,'
'FIPSST,FIPSCO,FIPSSTCO,TRACT_ID,ST_ABRV,CO_NAME,ST_NAME
context:
https://informatics.gpcnetwork.org/trac/Project/ticket/140
https://informatics.gpcnetwork.org/trac/Project/ticket/350
--
Dan
--
David Van Riper
Spatial Analysis Core Director
Minnesota Population Center
University of Minnesota
Email: [email protected]<mailto:[email protected]>
Phone: 612-625-7375
_______________________________________________
Gpc-dev mailing list
[email protected]
http://listserv.kumc.edu/mailman/listinfo/gpc-dev
