[postgis-users] Census Block, other fields from Tiger Geocoder?

[Robert_Clift at doh.state.fl.us]

Good morning Daniel:
 
Seems to me you can add just about anything you like to the SELECT when
using the geocode function. I happen to be looking at a table of census
block groups this morning.
 
Table: block_groups
fictitious SRID: 8675309
Columns: gid, block_group_fp, the_geom
 
*Grain of salt: the tiger geocoder is not loaded on the box at which I
sit, so all of the following is untested.
 
Let's say you want to return everything from your current query, plus
the full FIPS code of the block group in which the address falls:
 
SELECT g.*,
   ST_X(g.geomout) As lon,
   ST_Y(g.geomout) As lat, (addy).*,
   bg.block_group_fp
FROM geocode('<address here>') As g
JOIN block_groups As bg
ON (ST_Transform(g.geomout, 8675309) && bg.the_geom)
WHERE ST_Within (ST_Transform(g.geomout, 8675309), bg.the_geom) IS TRUE;
/*
It's my understanding that by joining based on overlapping bounding
boxes (the && operator) you allow for the use of the GIST index on your
block group geometries
*/
 
Also, you mention "one route is to use the lon/lat together with the
shapefiles themselves..." but we should be able to ignore the coordinate
pairs and shapefile since the geocode function returns a point geometry.
If you only want the block group FIPS code of the address:
 
SELECT bg.block_group_fp
FROM block_groups As bg
JOIN geocode('<address here>') As g
WHERE ST_Contains (bg.the_geom, ST_Transform(g.geomout, 8675309)) IS
TRUE;
 
Please, anyone, feel free to point out errors or inefficiencies in these
suggested queries. I'm a cartographer just learning to appreciate the
elegance of SQL in spatial data analysis and will be glad to pick up a
new trick or two as well.
 
 
-Rob

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Daniel Weitzenfeld
Sent: Friday, October 07, 2011 5:18 PM
To: PostGIS Users Discussion
Subject: [postgis-users] Census Block, other fields from Tiger Geocoder?


I'm currently using this as my query:

SELECT g.*, 
        ST_X(geomout) As lon, 
        ST_Y(geomout) As lat, (addy).*  
FROM geocode('<address here>') As g;

I'm wondering what other fields I can add to the SELECT.  E.g., can I
get census block?  

I know one route is to use the lon/lat together with the shapefiles
themselves and use point-in-polygon, but that seems circuitous.  

Thanks!


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111010/f2191209/attachment.html>