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

Burgholzer,Robert rwburgholzer at deq.virginia.gov
Fri Aug 8 05:43:23 PDT 2008

```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
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
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: 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

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

```