[postgis-users] Stuck: Table within a table?

[Paul Ramsey]

In order to avoid edge effects for counties on the border of the  
state you might want to do something like:

   select counties.*, states.name
   from counties, states
   where
     counties.the_geom && states.the_geom
   and
     within(states.the_geom, centroid(counties.the_geom))

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

Paul

On 21-Sep-06, at 2:02 PM, David William Bitner wrote:

> You'll need to do a join limiting the data by you geographic  
> constraints like:
>
> select counties.*, states.name from counties,states where within 
> (counties.the_geom,states.the_geom)
>
> On 9/21/06, Steve Benzo <steve_benzo at yahoo.com> wrote:
> Hi,
>
> In a particular Postgis/postgres database, I have
> several  geographic tables, countya, countyb, etc. I
> would like to have a way to feed the within or contain
> function a state's extent to find out which counties
> are within a particular state. I've tried a few
> iterations of queries without any luck, and so I was
> hoping someone could point me in the right direction,
> as far as the base SQL postgis statement to allow me
> to accomplish this.
>
> So far, I was thinking I could loop through the each
> of the tables, use the within command with the state's
> extent and the county's extent, and see if it returns
> true. The problem I'm having is, like I mentioned,
> getting this SQL statement together.
>
> Many thanks
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
> -- 
> ************************************
> David William Bitner
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

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