<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.21305" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011>Good morning Daniel:</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011>Seems to me you can add just about anything you like to
the SELECT when using the geocode function. </SPAN></FONT><FONT face=Arial
size=2><SPAN class=609550913-10102011>I happen to be looking at a table of
census block groups this morning.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011>Table: block_groups</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011>fictitious SRID: 8675309</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011>Columns: </SPAN></FONT><FONT face=Arial size=2><SPAN
class=609550913-10102011>gid, </SPAN></FONT><FONT face=Arial size=2><SPAN
class=609550913-10102011>block_group_fp, </SPAN></FONT><FONT face=Arial
size=2><SPAN class=609550913-10102011>the_geom</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011>*Grain of salt: the tiger geocoder is not loaded on the
box at which I sit, so all of the following is untested.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011>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:</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011>SELECT g.*,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011> ST_X(g.geomout) As
lon,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011> ST_Y(g.geomout) As lat,
(addy).*,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011> bg.block_group_fp</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011>FROM geocode('<address here>') As
g</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011>JOIN block_groups As bg</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011>ON (ST_Transform(g.geomout, 8675309) &&
bg.the_geom)</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011>WHERE ST_Within (ST_Transform(g.geomout, 8675309),
bg.the_geom) IS TRUE;</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011>/*</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011>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</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011>*/</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011></SPAN></FONT><FONT face=Arial size=2><SPAN
class=609550913-10102011></SPAN></FONT><FONT face=Arial size=2><SPAN
class=609550913-10102011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011>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:</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011>SELECT bg.block_group_fp</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011>FROM block_groups As bg</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011>JOIN geocode('<address here>') As
g</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011>WHERE ST_Contains (bg.the_geom, ST_Transform(g.geomout,
8675309)) IS TRUE;</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011></SPAN></FONT><FONT face=Arial size=2><SPAN
class=609550913-10102011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=609550913-10102011>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.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=609550913-10102011></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=609550913-10102011><FONT
face="Eras Demi ITC">-Rob</FONT></SPAN></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Daniel Weitzenfeld<BR><B>Sent:</B> Friday, October 07, 2011 5:18
PM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> [postgis-users]
Census Block, other fields from Tiger Geocoder?<BR></FONT><BR></DIV>
<DIV></DIV>I'm currently using this as my query:<BR><BR>SELECT g.*,
<BR> ST_X(geomout) As lon,
<BR> ST_Y(geomout) As lat,
(addy).* <BR>FROM geocode('<address here>') As g;<BR><BR>I'm
wondering what other fields I can add to the SELECT. E.g., can I get
census block? <BR><BR>I know one route is to use the lon/lat together with
the shapefiles themselves and use point-in-polygon, but that seems
circuitous. <BR><BR>Thanks!<BR><BR></BODY></HTML>