Finding census block for given address using Tiger geocoder

Question

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 1

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