0

I am using PostGIS v2.0.3, the guide say that ST_NumInteriorRings "will work with both POLYGON and MULTIPOLYGON types but only looks at the first polygon. Return NULL if there is no polygon in the geometry"... But my query returns only NULLs,

SELECT gid, 
       ST_NRings(geom) as nrings, 
       ST_NumInteriorRings(geom), ST_NumGeometries(geom) as ngeoms 
FROM t 
WHERE ST_NRings(geom) >1 or ST_NumGeometries(geom)>1

for all gid returns nrings=ngeoms>2 and ST_NumInteriorRings=NULL... But I see with my GIS viewer the holes there (!).


Example (req. by @JakubKania):

SELECT gid, ST_SRID(geom), ST_Geometrytype(geom), 
       ST_NumGeometries(geom) as ngeoms, geom::text
FROM kx.quadrasc 
WHERE ST_NRings(geom) >1 or ST_NumGeometries(geom)>1
LIMIT 2;
  2;31996;"ST_MultiPolygon";2;"0106000020FC7C0000020000000103000000010000004D000000ACC4E8BCDCD82241AE3B4A8C039359412D2281C0DCD822417EA0DE8E0393594170F08509E7D8224146B6F341089359413BA85897EED82241EA2145430B935941E5D45897EED822410B3145430B93594198D380AD26D9224110743093219359411AC884B626D92241E0D9D5952193594191180ACD26D92241D40D3F95219359415F3DF733A9D92241556F12761C935941C236BE3AA9D92241C9EFCF751C935941E7434D68C1D92241B7F06C8E1B935941D82803CBC1D92241E51F068C1B935941BE03932FC2D922413395148C1B9359413BB11B92C2D92241D5C1978E1B9359414815D0EEC2D92241E2D576931B93594186A51C42C3D9224159B5819A1B93594104BBCA88C3D9224142D372A31B935941A64E20C0C3D9224190E0F1AD1B935941D1E6FAE5C3D922413D3497B91B93594179A316B1C4D9224125876E0F1C935941804FBDB9C4D92241FD5E18121C9359410A4B54D0C4D92241E5858E111C9359413B605ED2D2D92241F4397A8D1B935941A600B2E7D2D92241C2E7658C1B935941690A63E3D2D9224147A892891B93594189B402E6D1D922413B1B151E1B935941404237D3D1D92241E011C2111B935941CA6F04D4D1D92241B85236051B935941995A62E8D1D92241304BEDF81A935941BDA3880FD2D9224154D85FED1A9359412923F647D2D92241C3A1FFE21A935941E7B47F8FD2D9224186BB32DA1A9359419B8E65E3D2D92241D1B94FD31A935941744B6E40D3D922413F5D9ACE1A935941EDCBF3A6DCD92241CCBAF8751A935941359FF5A6DCD9224167A6F8751A9359417779E62AF6D922413695638519935941B55EE72AF6D92241378B6385199359416417D9AE0FDA2241C5CCCC94189359419019D9AE0FDA2241B1CDCC941893594158B4D9AE0FDA2241FDC6CC94189359413849E2F02CDA22418153ED801793594198E535062DDA2241DA00D97F179359412FEFE6012DDA2241B2C1057D179359417241A5AB24DA2241D73F9AF3139359413170FDAA24DA2241A67352F3139359410ACFCBAD1CDA2241FC42D88010935941E0578AAC1CDA224182314B8010935941A2B46C9719DA224181A107200F9359412E00B98D19DA2241C93D001D0F935941E3F3017519DA2241E8AEF31B0F935941EB57DCE4F5D9224123BE081E0E9359417B537B59F5D92241A9515C170E935941008308C4E7D922410E6907230D9359410F92F1B0E7D92241CE39D2210D9359416C2A77AEE7D92241D746B3210D935941BEB36E98E7D9224168D2E9200D93594135B9926BD5D9224140D613990C9359419DD1AB45D5D922414EFFC6970C935941EF7F0629B7D922416ACA1F670B9359414CD66A25B7D92241F4D0FA660B9359419990E13C9DD92241B472175A0A9359415805E9239DD92241DE3FFD580A9359414D34F06777D922416982178B089359414D8B0F6577D92241F5AAFE8A08935941942D7B3977D92241F3C04289089359410DD1603677D92241B32F1E890893594121A9D0D94ED9224196E4713D0793594166062EC04ED92241D44F883C07935941BA2F08B21BD92241B5BEFF3C05935941336CD5A61BD9224174B7B53C059359413E819B801BD92241AA8A873B0593594108EC6C7E1BD922414F6A733B059359413EB2CC89FFD822413C38529F04935941CC56E466FFD822417CE4669E04935941FBECA0D1DCD822411007AD8C03935941ACC4E8BCDCD82241AE3B4A8C039359410103000000010000000400000070F08509E7D8224146B6F341089359412C5DC008E7D82241E577FA41089359419088C208E7D822410763FB410893594170F08509E7D8224146B6F34108935941"

  12;31996;"ST_MultiPolygon";3;"0106000020FC7C0000030000000103000000010000003C000000C396357D77C62241B141A01D82925941D4C3748E77C62241619A871F8292594158302AE997C62241F241CF0A8592594174232AE997C622412E44CF0A85925941150D2BE997C62241DC55CF0A859259418647B520A8C622413A0E608186925941B0D1B620A8C622413B2E608186925941064E5158B8C622417C8FEFF787925941F9DBDE8FC8C622413FF17E6E899259412D1EDF8FC8C622419FF67E6E899259419975FB5FD8C62241B7B4B8DB8A925941916ADE73D8C6224148042BDD8A925941F02D1983D8C62241721E03DB8A925941DCABE85AE8C62241ED209B1C889259418BE67864E8C6224180CB001B88925941D04D6230F0C622419E5E29D7869259413F64579907C72241752846C9829259419CCF579907C72241CD1346C98292594139F2D24D13C7224123BD52C28092594181F7D24D13C722419DBD52C280925941A064D34D13C7224153A952C2809259414D834D021FC722410B2161BB7E925941F9864D021FC72241602161BB7E925941F0D24D021FC72241401361BB7E925941AC8D4F6B36C72241B9F17DAD7A9259415943506B36C72241C4CE7DAD7A9259412D93CB1F42C72241B4718AA6789259416F98CB1F42C722412D728AA6789259413005CC1F42C72241F55D8AA678925941807346D44DC72241E3C7989F76925941C854C18859C72241E71DA79874925941E696FEE270C72241F8CC4E8D70925941301291EE70C72241746ED28A709259417FE651DD70C72241F715EB8870925941B1B52ABA60C72241D6DF32146F92594190102ABA60C722416ED232146F925941C0FA8F8250C722417E7CA39D6D925941480C024B40C72241061214276C925941C15AE8DB1FC72241326FF53969925941A0B5E7DB1FC72241CA61F539699259416C37B2B80FC72241160D3DC5679259416C41CFA40FC722412DBECAC367925941BF8094950FC7224103A4F2C5679259412778573BF8C62241FCEA4AD16B925941605DDC86ECC62241ED9E3CD86D925941187C61D2E0C62241E9482EDF6F925941F446E71DD5C6224117D51FE67192594199DBE61DD5C62241BFE91FE67192594166267B69C9C622416C9410ED73925941FFFB1FB1BDC62241E4933EF4759259411FB003B1BDC622413B0544F475925941DA26A5FCB1C62241183A30FB77925941AED288FCB1C6224134AE35FB779259411B140A4CA6C62241F4A4E5017A925941703480979AC62241AC8BDB087C925941212F80979AC62241318BDB087C92594178C17F979AC62241959FDB087C925941285305E38EC62241A835CD0F7E925941B616C88877C6224135E3231B82925941C396357D77C62241B141A01D8292594101030000000100000004000000C854C18859C72241E71DA79874925941BBE8BF8A59C72241982ED5987492594103E9BF8A59C722418C2ED59874925941C854C18859C72241E71DA7987492594101030000000100000004000000D04D6230F0C622419E5E29D786925941E2D63731F0C62241E5EE3DD7869259410BDE3C31F0C62241B9163DD786925941D04D6230F0C622419E5E29D786925941"
4
  • Post the sample of geom please. – Jakub Kania Mar 25 '14 at 13:40
  • Ok, I edit, is there... It works in your PostgreSQL? – Peter Krauss Mar 25 '14 at 13:59
  • None of these contains a hole. – Jakub Kania Mar 26 '14 at 17:11
  • Thanks by the checking. Well, the problem is about the "NULL return", because guide say "Return NULL if there is no polygon in the geometry", and there are a polygon as you see. – Peter Krauss Mar 26 '14 at 17:25
1

MultiPolygons are not Polygons. You need to extract a polygon from the collection before passing it to ST_NumInteriorRings. ST_NRings should be more forgiving, but it will give you the count of both exterior and interior rings. So I guess if you look for cases where ST_NRings > ST_NumGeometries that should turn up instances of MultiPolygons with holes.

5
  • You can extract a polygon using ST_GeometryN or you can dump the collection into a result set using ST_Dump – Paul Ramsey Mar 27 '14 at 8:39
  • Thanks! good tips... Spliting the question into two subquestions: 1) there are a bug? because ST_NumInteriorRings "will work with both POLYGON and MULTIPOLYGON types" and "return NULL if there is no polygon"; 2) There are an easy way to check when the MultiPolygon is not a topologically-disjoint set, and its layout behaviour is like a "polygon with holes"? – Peter Krauss Mar 27 '14 at 11:19
  • No, it's not a bug. It's behaviour I'm not entirely happy with, but the alternative could also be considered confusing: if you run ST_NumInteriorRings on a multi-polygon, what should the answer be? # of interior rings in first polygon? sum of interior rings on all? – Paul Ramsey Mar 27 '14 at 11:39
  • A multipolygon that answers st_numgeometries == 1 is a simple polygon without disjoint members. – Paul Ramsey Mar 27 '14 at 11:39
  • I am reviewing the problem, see answer below... – Peter Krauss Apr 3 '14 at 14:58
0

Hole detection in MULTIPOLYGONs

To see only geometries with hules, use the condiction

 SELECT * FROM t WHERE ST_NRings(geom)>ST_NumGeometries(geom)

to check if there are "important hules",

 ST_NRings(geom)>ST_NumGeometries(geom)
 AND
 round(ST_Area(geom)) < round(ST_Area(
       ST_MakePolygon(ST_ExteriorRing(ST_GeometryN(geom,1)))
    ))

this is the "real life" detection. The "non-important hules" (very tiny areas in the case of "ST_Union side effects") can be removed.

Clean ST_Union

After some analysis I discover that ST_Union produce very tiny areas in some geometries... they have all the area into ST_GeometryN(geom,1) and areas of ~10^-4...10^-6 in the other geometries (ST_GeometryN(geom,n) with n>1).


Answer

About *"ST_NumInteriorRings not returns zero, is a bug?"*

For multipoligon ST_NumInteriorRings() is not a "hule detector" and the function (or the guide) have a bug: the "NULL return" in the context where geometry is valid and not null.

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.