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

Paragon Corporation lr at pcorp.us
Wed Aug 6 15:16:45 PDT 2008


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: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] 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 rwburgholzer at deq.virginia.gov
804-698-4405
Open Source Modeling Tools:
http://sourceforge.net/projects/npsource/

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] 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
<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
>
>
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users





More information about the postgis-users mailing list