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