<HTML><BODY style="word-wrap: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space; ">In order to avoid edge effects for counties on the border of the state you might want to do something like:<DIV><BR class="khtml-block-placeholder"></DIV><DIV> select counties.*, states.name </DIV><DIV> from counties, states</DIV><DIV> where</DIV><DIV> counties.the_geom && states.the_geom</DIV><DIV> and</DIV><DIV> within(states.the_geom, centroid(counties.the_geom))</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>note that I have added the && clause to use spatial indexes where available and am using the centroid of the counties to avoid any edge mismatch problems with within().</DIV><DIV><BR class="khtml-block-placeholder"></DIV><DIV>Paul</DIV><DIV><BR><DIV><DIV>On 21-Sep-06, at 2:02 PM, David William Bitner wrote:</DIV><BR class="Apple-interchange-newline"><BLOCKQUOTE type="cite">You'll need to do a join limiting the data by you geographic constraints like:<BR><BR>select counties.*, <A href="http://states.name">states.name</A> from counties,states where within(counties.the_geom,states.the_geom)<BR> <BR><DIV><SPAN class="gmail_quote">On 9/21/06, <B class="gmail_sendername">Steve Benzo</B> <<A href="mailto:steve_benzo@yahoo.com">steve_benzo@yahoo.com</A>> wrote:</SPAN><BLOCKQUOTE class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> Hi,<BR><BR>In a particular Postgis/postgres database, I have<BR>several geographic tables, countya, countyb, etc. I<BR>would like to have a way to feed the within or contain<BR>function a state's extent to find out which counties <BR>are within a particular state. I've tried a few<BR>iterations of queries without any luck, and so I was<BR>hoping someone could point me in the right direction,<BR>as far as the base SQL postgis statement to allow me<BR> to accomplish this.<BR><BR>So far, I was thinking I could loop through the each<BR>of the tables, use the within command with the state's<BR>extent and the county's extent, and see if it returns<BR>true. The problem I'm having is, like I mentioned, <BR>getting this SQL statement together.<BR><BR>Many thanks<BR><BR>__________________________________________________<BR>Do You Yahoo!?<BR>Tired of spam? Yahoo! Mail has the best spam protection around<BR><A href="http://mail.yahoo.com"> http://mail.yahoo.com</A><BR>_______________________________________________<BR>postgis-users mailing list<BR><A href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR><A href="http://postgis.refractions.net/mailman/listinfo/postgis-users"> http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR></BLOCKQUOTE></DIV><BR><BR clear="all"><BR>-- <BR>************************************<BR>David William Bitner<DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">_______________________________________________</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">postgis-users mailing list</DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><A href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A></DIV><DIV style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><A href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A></DIV> </BLOCKQUOTE></DIV><BR></DIV></BODY></HTML>