Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I'm trying to make a table to relate the STFID and LOGRECNO values for the US 2000 census at the block group level, nationally. I realize that the LOGRECNO value is a state specific unique variable, so I am joining it to the state ID code.

The steps that I have done so far are:

Importing all the national geo files into a database (e.g. akgeo.txt,.... to wygeo.txt)

Extracting all the blockgroup values using the following SQL command:

SELECT SF1GEO.LOGRECNO, SF1GEO.REGION, SF1GEO.STATE, SF1GEO.COUNTY, SF1GEO.TRACT, SF1GEO.BLKGRP
FROM SF1GEO
WHERE SF1GEO.BLOCK IS NULL 
AND
SF1GEO.BLKGRP IS NOT NULL
AND 
SF1GEO.TRACT IS NOT NULL
AND 
SF1GEO.COUNTY IS NOT NULL;

I want to ignore all the block level information, and only take the relevant LOGRECNO for each blockgroup (BLKGRP). Then to produce the STFID I concatenate the variables:

STATE + COUNTY + TRACT + BLKGRP 

which gives me a 12 character code (STATE is 2 characters, COUNTY is 3 characters, TRACT is 6 characters, BLKGRP is 1 character). I also combine the STATE and LOGRECNO values for future analysis, based on advice from census.gov as I want to perform national analysis.

  LOGRECNO REGION STATE COUNTY  TRACT BLKGRP        STFID LOG_STATE
1  0000038      3    01    001 021100      2 010010211002 010000038
2  0000077      3    01    001 021100      3 010010211003 010000077
3  0000107      3    01    001 021100      1 010010211001 010000107
4  0000169      3    01    001 021100      2 010010211002 010000169
5  0000231      3    01    001 021100      3 010010211003 010000231
6  0000308      3    01    001 021000      2 010010210002 010000308

At this point I have 546293 values, but only 208144 unique STFID values. One problem I have is that I can't figure out why all my STFID values are not unique.

Then, when I try to merge this table with a shapefile of blockgroups it only matches on 72972 polygons. I also can't figure out why the STFID does not match all. This is what the map looks like:

enter image description here

I'm not sure if there is an obvious pattern to the blockgroups that are included/excluded.


EDIT: I used Sean's suggestion of selecting by SUMLEV asthat makes a lot more sense than what I was doing earlier. So my SQL query is now:

SELECT SF1GEO.LOGRECNO, SF1GEO.STATE, SF1GEO.COUNTY, SF1GEO.TRACT, SF1GEO.BLKGRP
FROM SF1GEO
WHERE (((SF1GEO.SUMLEV)="150"));

However, I'm still mystified when I join this to the shapefile as I end up with the same map as above, and the same number of joins. I am manually making the code to join within the shapefile using the information within by concatenating STATE + COUNTY + TRACT + BLKGRP. What I can't understand is why 72972 values successfully join, and the rest do not.

share|improve this question
    
It is pretty fun to see your project keep developing –  dkroy Jul 26 '11 at 16:34
    
Thanks! Once complete, I hope to release some of these tables publicly as I feel like I'm recreating work people have done numerous times. –  djq Jul 26 '11 at 16:39
    
Is this the ACS 5 year data still? Their geo-files don't have the block column, or is this the summary file 1 release from the 2010 count (I see you use the prefix sf1 for your geo files)? If indeed you are using the SF1 2010 data, make sure you use the 2010 tiger geographies as well since those changed since 2000. –  ako Jul 26 '11 at 19:56
    
It's the census 2000 blockgroup which I assume the ACS data is using. –  djq Jul 26 '11 at 19:58
    
The map above is based on the join with the national geofile alone (that is, without actual data attached)? –  ako Jul 26 '11 at 20:47
show 7 more comments

2 Answers 2

up vote 6 down vote accepted

I don't know what an STFID is and I have pretty intimate knowledge of the Census counts.

The geography file has a record for every geographical unit. There's one for the entire state, for every county, for every tract, for every block etc. Each is uniquely identified by a LOGRECNO. The LOGRECNO will be unique in the geo file.

The other files have all of the counts. It's really one big table split into many files. Each record has, you guessed it, an associated LOGRECNO. The LOGRECNO will not be unique among data files.

You join on the LOGRECNO.

The quickest way to pull out Block Groups is by Summary Level. The Summary Level for Block Groups is 150. So, go through the geo file and pull out every record with a summary level of 150. Those are your Block Groups.

Block Groups are uniquely identified by Block Group number combined with state, county and tract as you have figured out. The corresponding field in the Block Group layer of TIGER is GEOID10. Join your data using GEOID10 and the STATE+COUNTY+TRACT+BG from the summary files.

share|improve this answer
    
Thanks for the SUMLEV tip, that makes a lot of sense. I can't see a GEOID10 field anywhere though. It's not in the shapefile I downloaded from the US census. Perhaps STFID is a non standard term, but I've seen it used to describe the STATE+COUNTY+TRACT+BG combination. –  djq Jul 26 '11 at 19:05
2  
There's a FID field but it's useless. There's no STFID field. Are you sure that was in the shapefile and not created by the software that imported them to the db? Make sure you downloaded from ftp2.census.gov/geo/tiger/TIGER2010/BG. All Census 2010 geography has the GEOID10 field. –  Sean Jul 26 '11 at 19:32
    
I downloaded the census 2000 files; not sure if there is a difference. I'm not using the FID fields, just the STATE/ COUNTY/TRACT/BLKGRP which are within the file. –  djq Jul 26 '11 at 19:40
    
It depends on what data you're using. I assumed you're using the 2010 data. The 2010 Census counts use 2010 geography. If you're using ACS, you would use 2000 geography from TIGER 2009. The fields are a little different. The BKGPIDFP00 field is the unique BG field for 2000 BGs. –  Sean Jul 26 '11 at 19:46
    
Do you know where I can download shapefiles with BKGPIDFP00? The ones I downloaded from census.gov for the 2000 census do not have that info. –  djq Jul 26 '11 at 19:54
show 3 more comments

Is STFID the Census tract ID or something else?

You could always concatenate them rather than adding them together (unless you were and I've misinterpreted?), therefore creating a longer number. I think it isn't linking because you didn't combine the STFID for both datasets. Or perhaps you could split the field along the lines of your combination once you've gotten the LOGRECNO added.

share|improve this answer
    
Hi Emily, I am concatenating to make a 12 digit STFID which should match the BLKGRP STFID. In principle, the STFID values from the shapefile should match the STFID from the geo table, but only a fraction are currently matching. –  djq Jul 26 '11 at 18:16
    
Is there perhaps in some of the STFIDs a null value in the geotable? I've been working for the Canadian Census and there are some strange permutations in GEOID fields that should be (rightfully) the same but differ in very small areas. edit: and by null value i meant that one of the components was null, not the whole number. –  Emily Jul 26 '11 at 18:34
    
No, I believe all the values I select are not null, as that is part of my SQL query on the census data. –  djq Jul 26 '11 at 18:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.