Robert, Are your boundaries lines or polygons. I'm a little confused why you need a convex hull at all and why Paul's example didn't work unless your bounds are LINE STRINGs. Why doesn't contains just work?
Assuming polygons, one thought is to try relaxing your condition a bit. 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.projectid = 1 AND b.projectid = 1 AND a.poli1 <> b.poli1 and a.the_geom && b.the_geom AND contains(a.the_geom, b.the_geom); Or if as Paul suggests you are seeing boundary conditions where the bounds are right on the surface or something 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.projectid =1 AND b.projectid = 2 AND a.poli1 <> b.poli1 and a.the_geom && b.the_geom AND (contains(a.the_geom, b.the_geom) or contains(buffer(a.the_geom,1), b.the_geom)); Hope that helps, Regina -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Burgholzer,Robert Sent: Wednesday, August 06, 2008 3:08 PM To: PostGIS Users Discussion Subject: RE: [postgis-users] filing the holes in the swiss cheese? Thanks Paul, but this gives me no records at all. BTW - the dirty secret exposed by the lack of ST_ is that I am running an older version of PostgreSQL/PostGIS - 8.2 with posstgis_version(): 1.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 I wonder if there is some problem that this version has? r.b. Robert W. Burgholzer Surface Water Modeler Office of Water Supply and Planning Virginia Department of Environmental Quality [EMAIL PROTECTED] 804-698-4405 Open Source Modeling Tools: http://sourceforge.net/projects/npsource/ -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Paul Ramsey Sent: Wednesday, August 06, 2008 2:16 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] filing the holes in the swiss cheese? 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 <[EMAIL PROTECTED]> 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 > > [EMAIL PROTECTED] > > 804-698-4405 > > Open Source Modeling Tools: > > http://sourceforge.net/projects/npsource/ > > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
