1

I'm geocoding lots of addresses and map them to census block. Some of addresses are street intersections, like this

select ST_X((geo).geomout)::numeric(8,6), ST_Y((geo).geomout)::numeric(8,6)
    from geocode_intersection(' 7TH ST NW ','G ST NW','DC','WASHINGTON','20001',1) as geo

-77.021914;38.898446;

I was using the gemout field from geocode_intersection output as the input for finding census block:

select a.addid, b.tabblock_id
        from intersection_table as a, tabblock as b 
        where ST_Contains(b.the_geom, a.geomout) 

This only return 5 records in 19 addresses.

Take the example of first address, this failed when I used 6 digits after dot

SELECT tabblock_id
    FROM tabblock 
    WHERE ST_Contains(the_geom, ST_SetSRID(ST_Point(-77.021914, 38.898446), 4269))

But it will success with 5 digits

SELECT tabblock_id
    FROM tabblock 
    WHERE ST_Contains(the_geom, ST_SetSRID(ST_Point(-77.02191, 38.89845), 4269))

'110010058002005'

Using lon and lat with 5 digits after dot will find result for all:

select a.addid, a.geomout, get_tract(a.geomout),b.tabblock_id
    from intersection_table as a, tabblock as b 
    where ST_Contains(b.the_geom, ST_SetSRID(ST_Point(a.lon, a.lat), 4269))

I can understand it's difficult to have a definitive answer for street intersections because they could be the boundary, but why using 5 digits will "solve" this problem? Should I trust the result of this method?

EDIT I found something interesting:

Result from my code:

row_seq        | 2716
street_1       |  FLORIDA AVE NW
street_2       | MASSACHUSETTS AVE NW
state          | DC
city           | WASHINGTON
zip            | 20008
addid          | 21
rating         | 3
lon            | -77.04879
lat            | 38.91150
output_address | 2198 Florida Ave NW, Washington, DC 20008
geomout        | 0101000020AD100000CB9717601F4353C0BAA29410AC744340

Use the lon/lat value to look up census block by tiger geocoder:

SELECT tabblock_id
    FROM tabblock 
    WHERE ST_Contains(the_geom, ST_SetSRID(ST_Point(-77.04879, 38.91150), 4269));
> '110010041003022'

Use this lon/lat value in census geocoder website give same 110010041003022.

However, if I used the street address in Census geocoder, it give the coordinates and census block as

X: -77.048775 Y: 38.91151
GEOID: 110010055001010

I guest it's because geocode_intersection got the two street intersection by calculation, then use the coordinates to reverse geocode an address, which is the closest one to the location.

However if you input an address to look up census block, it will get the address location first, which is not exactly the same with the street intersection coordinates.

Your Answer

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

Browse other questions tagged or ask your own question.