[postgis-users] filing the holes in the swiss cheese?

[Paul Ramsey]

try

select b.poli1 as fips, b.name, a.poli1 as contained_by_fips, a.name
from poli_bounds as a , poli_bounds as b
where a.poli1 <> b.poli1
and st_contains(a.the_geom, st_pointonsurface(b.the_geom))
and a.projectid = 1
and b.projectid = 1;

that should rid you of the boundary conditions plaguing st_contains.

P.

ps - note the "modern" st_contains(), with implicit index call.

On Wed, Aug 6, 2008 at 8:19 AM, Burgholzer,Robert
<rwburgholzer at deq.virginia.gov> wrote:
> I am trying to do a query of cities that are contained by counties (in
> Virginia, US), so that I can have a list that cross-references by FIPS these
> relationships.
>
>
>
> My query looks like this:
>
>
>
> select b.poli1 as fips, b.name, a.poli1 as contained_by_fips, a.name
>
> from poli_bounds as a , poli_bounds as b
>
> where a.poli1 <> b.poli1 and a.the_geom && b.the_geom
>
> and contains(convexhull(a.the_geom), b.the_geom)
>
> and a.projectid = 1
>
> and b.projectid = 1;
>
>
>
>
>
> This works fairly well, but omits a handful of cities that are most
> definitely within the boundaries of the county.
>
>
>
> Is "convexhull()" the correct function for this?  Are my geometries goofed
> up?
>
>
>
> Thanks!
>
>
>
> Robert W. Burgholzer
>
> Surface Water Modeler
>
> Office of Water Supply and Planning
>
> Virginia Department of Environmental Quality
>
> rwburgholzer at deq.virginia.gov
>
> 804-698-4405
>
> Open Source Modeling Tools:
>
> http://sourceforge.net/projects/npsource/
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>