4

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?

  • The same way with a block or group layer to query against? I don't know the details of the data you're using, but often (typically?) the smallest units of a reference system have an attribute identifying their parent unit. So if you query against a block layer, you can also get the tract and block group from the same data. Since blocks make up groups which make up tracts, you need the finer grained data to query against - you can't get a block level location from a tract level dataset. – Chris W Mar 6 '15 at 22:20
  • I am using Tiger/Line dataset and built the geocoder using PostGIS. I checked the tables it does contain block groug table. But I don't know what would be the correct join query for it. Currently, I am trying this: SELECT BG.bg_id, BG.the_geom, MYTABLE.location FROM BG, MYTABLE WHERE ST_Contains(BG.the_geom, ST_Transform(MYTABLE.location, 4269)) is true; – Akhil Mar 7 '15 at 16:42
  • 1
    It worked now. I was querying on wrong table. I had to use tabblock table in Tiger/Line dataset. It contained the census block information. The query was select name, tabblock_id, the_geom from tabblock where ST_Contains(the_geom, ST_Transform(POINT(lat, long), 4269)) is true; – Akhil Mar 7 '15 at 22:29
  • 1
    Feel free to post that as an answer - it's perfectly ok to answer your own question. – Chris W Mar 8 '15 at 21:32
3

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

  • Thanks for the answer, though usually people will not include all the prefix for county fips, tract, blockgroup etc. Each column should only take their own value. – dracodoc Nov 27 '15 at 22:12
  • There are a few mistakes in how you sliced the tabblock_id on the first query and the query results are off. Your indices are off by 1 for a few. Could be because SQL indices start at 1 and not 0? Took me a bit to figure out why my code was bombing, but tracked it down. Anyway, you seem to fix the mistake in the Discussion section as your query results are now correct. I'll edit the answer soon. Thanks. – alfonso Oct 25 '18 at 15:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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