Regina, Thanks for the response. My shapes are Multipolygons. My understanding is that if the city is inside of the county, but occupying a hole in the county that contains() will fail. This is born out by my queries, for the state of Virginia I get a result of 24 cities that are contain()ed within the convexhull of a county, but 0 contain()ed within the_geom itself.
Just now, I found that my missing city shape had a small island that lay outside of the county of interest, hence, the contains() threw a false. False alarm, I guess -- although I suppose it is important to have a sense of where one islands are. Thanks for all the input! 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 Paragon Corporation Sent: Wednesday, August 06, 2008 6:17 PM To: 'PostGIS Users Discussion' Subject: RE: [postgis-users] filing the holes in the swiss cheese? 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 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
