Saturday, 25 July 2015

postgis - Finding census block for given address using Tiger geocoder


I have a geocoder based on Tiger/Line data running on PostGIS. I need to find census block for a given address. How can I do it?


Like I can do for census tract:


SELECT get_tract(ST_Point(-71.101375, 42.31376) ) As tract_name;


What would be similar query for finding census block and block group?



Answer



@Akhil answered his/her own question but I wanted to post this clearly for posterity in case others find themselves combing through comments to figure this out like I did.


TL/DR answer:


SELECT tabblock_id AS Block,
substring(tabblock_id from 1 for 11) AS Blockgroup,
substring(tabblock_id from 1 for 9) AS Tract,
substring(tabblock_id from 1 for 5) AS County,
substring(tabblock_id from 1 for 2) AS State

FROM tabblock
WHERE ST_Contains(the_geom, ST_SetSRID(ST_Point(-71.101375, 42.31376), 4269))

should return something like:


      block      | blockgroup  |   tract   | county | state
-----------------+-------------+-----------+--------+-------
250251203014004 | 25025120301 | 250251203 | 25025 | 25
(1 row)

Discussion:



First, understand that the census block contains all the hierarchical information above it. For example, consider the block in @akhil's example. For this, the block is '250251203014004'.


Block:      250251203014004 (Block 4004)
Blockgroup: 250251203014 (Blockgroup 4)
Tract: 25025120301 (Census tract 1203.01)
County: 025 (County FIPS 025)
State: 25 (State FIPS 25)

Not the best example since State and County are both '25' but essentially the whole thing is a concatenation of the individual codes for state > county > tract > blockgroup > block. Breaking out the above into components makes it clear:


block ~ concat(25,025,1203,01,4004)


Anyway, with that aside, we can use substrings to get everything from the block if you want. See the TL/DR answer above for the solution using substring.


For reference, here is a more honest approach that doesn't use substring:


For census block:


SELECT name, tabblock_id
FROM tabblock
WHERE ST_Contains(the_geom, ST_SetSRID(ST_Point(-71.101375, 42.31376) ,4269))`

gives


    name    |   tabblock_id
------------+-----------------

Block 4004 | 250251203014004
(1 row)

For tract and blockgroup:


SELECT statefp, countyfp, tractce, tract_id 
FROM tract
WHERE ST_Contains(the_geom, ST_SetSRID(ST_Point(-71.101375, 42.31376), 4269))

gives:


 statefp | countyfp | tractce |  tract_id

---------+----------+---------+-------------
25 | 025 | 120301 | 25025120301
(1 row)

See that statefp, countryfp, and tractce concatenate to make the tract_id, which is also the first 11 characters of the census block.


No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...