I am working in Postgres 9.5. This is my table:

 gid    | integer                 | not null default nextval('mytable_gid_seq'::regclass)
 geom   | geometry                |
 county | character varying(1000) |

The geom field was created by running ST_Union over a MultiPolygon field in another table.

If I run SELECT geom FROM mytable WHERE county='Manchester' and then export the results to GeoJSON and open them in QGIS, the resulting polygons look like this.

enter image description here

Now I want to find the number of interior rings in the geometry for Manchester:

SELECT ST_NumInteriorRings(geom) FROM mytable WHERE county='Manchester';

However, it produces an empty result. I don't understand this, since I can clearly see holes in QGIS.

I've also tried ST_NumInteriorRings(ST_Multi(geom)) but that doesn't produce a result either.

What am I doing wrong? (What I would ultimately like to do, in case it helps, is find the area of the internal holes.)

  • Have a look at the actual geometry with SELECT ST_AsText(geom) FROM mytable WHERE county='Manchester'; Perhaps it helps you further. – user30184 Mar 29 '17 at 9:00
  • Please edit the question to specify what is actually returned -- "no result" and "empty result" are not clear descriptions the way "no rows" and "a row with a NULL value" would be. Try altering the query to select "county,ST_AsText(geom),ST_IsValid(geom)," plus your expression and include the full output. – Vince Mar 29 '17 at 10:46

ST_NumInteriorRings operates only on POLYGON geometries, returning NULL for any other type, including MULTIPOLYGON:

SELECT ST_NumInteriorRings('MULTIPOLYGON (((0 0, 1 0, 1 1, 0 0)))');

SELECT ST_NumInteriorRings('POLYGON ((0 0, 1 0, 1 1, 0 0))');
-- 0

If your geometries are MultiPolygons that each contain a single polygon, you can get the number of interior rings with ST_NumInteriorRings(ST_GeometryN(geom, 1)).

  • Or in the OP's case ST_Area(ST_GeometryN(geom, 1)), etc. I guess you could use generate_series in conjunction with ST_NumInteriorrings to do this cleanly. – John Powell Mar 29 '17 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.