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