AW: [postgis-users] Detecting holes in geometries

Obe, Regina robe.dnd at
Fri Aug 24 05:31:21 PDT 2007

Try the following to get all the holes

 SELECT AsText(InteriorRingN(GeometryN(geom,gn.n),NumInteriorRings(GeometryN(geom,gn.n)) )) AS geometryOfHole from
areas, generate_series(1, 1000) gn(n)
Where NumGeometries(geom) >= gn.n  AND NumInteriorRings(GeometryN(geom,gn.n)) > 0

1000 is an arbitrary number here - which is just a guess of the max number of polygons per multipolygon.  Replace where I have 1000 with what you get when you do

SELECT Max(NumGeometries(geom))
FROM areas

Hope that helps,


-----Original Message-----
From: postgis-users-bounces at [mailto:postgis-users-bounces at] On Behalf Of Barend Köbben
Sent: Friday, August 24, 2007 5:32 AM
To: PostGIS Users Discussion
Subject: Re: AW: [postgis-users] Detecting holes in geometries

Well a multipolygon is just a set of polygons, so going through them and
getting the holes per polygon should work, something like:

 SELECT AsText(InteriorRingN( GeometryN(geom,1),1) AS geometryOfHole from

Should get tyou the first hole in the first polygon of the multipolygon...
Did not check, no PG access at the moment).

On 24-08-2007 11:06, "Thorsten Kraus" <Thorsten.Kraus at> wrote:

> Thanks. Unfortunately this function is only defined for polygons, but my
> geometries are of type multipolygon :(
> A cast like this did not help: SELECT AsText(InteriorRingN(geom::POLYGON,1))
> from xxxportugal2dig_s.
> Is there any other possibility to get the hole position out of a multipolygon?
> -----Ursprüngliche Nachricht-----
> Von: postgis-users-bounces at
> [mailto:postgis-users-bounces at]Im Auftrag von
> Barend Köbben
> Gesendet: Freitag, 24. August 2007 10:59
> An: PostGIS Users Discussion
> Betreff: Re: [postgis-users] Detecting holes in geometries
>  SELECT AsText(InteriorRingN(geom),1) AS geometryOfHole from areas
> Would get you the geometry of the 1st hole as OGC Well-Known Text...
> __ 
> Barend Köbben
> International Institute for Geo-information
> Sciences and Earth Observation (ITC)
> PO Box 6, 7500AA Enschede (The Netherlands)
> ph: +31 (0)53 4874253; fax: +31 (0)53 4874335
> On 24-08-2007 10:15, "Thorsten Kraus" <Thorsten.Kraus at> wrote:
>> Hello,
>> I am able to detect the number of holes in a geometry with the following sql
>> statement:
>> SELECT SUM(NumInteriorRings(geom)) AS countHoles from areas
>> Is there any possibility to get the position where the holes are?
>> Regards,
>> Thorsten
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at
> _______________________________________________
> postgis-users mailing list
> postgis-users at
> _______________________________________________
> postgis-users mailing list
> postgis-users at

postgis-users mailing list
postgis-users at

The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.

More information about the postgis-users mailing list