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
