Re: Best ways around memory Error on large st_union for overlay processing
Paul Ramsey via postgis-users <[email protected]> Thu, 15 Jan 2026 10:16:47 -0800
| Newsgroups | gmane.comp.gis.postgis |
|---|---|
| Message-ID | <[email protected]> |
--Apple-Mail=_9CAB0C61-59CB-48BE-9729-47D17E55E188 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Jan 15, 2026, at 9:46=E2=80=AFAM, Mike Treglia <[email protected]> = wrote: >=20 > Is there an obvious or more optimal way to do that = st_union(st_exteriorring(geom)) step for large datasets?=20 >=20 Just spitballing=E2=80=A6 Starting with an ST_Subdivide on all the rings, writing out the ring = segments and a unique key into a staging table. Then do the st_union on a gridded basis. I think that should be safe? The part I worry about is doing the polygon building, unless you do the = build with overlapping grid cells to select potential input ring = segments, and then post-filter the polygon collection you get to remove = any overlapping/duplicated polygons. My concern is that a very large = input polygon relative to the grid size might fail to be built, if all = its component pieces do not happen to fall into a single grid cell. At some point you end up building something of the scale and complexity = of the topology module, and maybe you would be able to get some good = results starting there instead. P.= --Apple-Mail=_9CAB0C61-59CB-48BE-9729-47D17E55E188 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 <html><head><meta http-equiv=3D"content-type" content=3D"text/html; = charset=3Dutf-8"></head><body style=3D"overflow-wrap: break-word; = -webkit-nbsp-mode: space; line-break: after-white-space;"><br = id=3D"lineBreakAtBeginningOfMessage"><div><br><blockquote = type=3D"cite"><div>On Jan 15, 2026, at 9:46=E2=80=AFAM, Mike Treglia = <[email protected]> wrote:</div><br = class=3D"Apple-interchange-newline"><div><meta charset=3D"UTF-8"><div = style=3D"caret-color: rgb(0, 0, 0); font-family: HelveticaNeue; = font-size: 13px; font-style: normal; font-variant-caps: normal; = font-weight: 400; letter-spacing: normal; text-align: start; = text-indent: 0px; text-transform: none; white-space: normal; = word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: = none;">Is there an obvious or more optimal way to do that = st_union(st_exteriorring(geom)) step for large datasets? </div><br = class=3D"Apple-interchange-newline"></div></blockquote></div><br><div>Just= spitballing=E2=80=A6</div><div><br></div><div>Starting with an = ST_Subdivide on all the rings, writing out the ring segments and a = unique key into a staging table.</div><div>Then do the st_union on a = gridded basis. I think that should be safe?</div><div>The part I worry = about is doing the polygon building, unless you do the build with = overlapping grid cells to select potential input ring segments, and then = post-filter the polygon collection you get to remove any = overlapping/duplicated polygons. My concern is that a very large input = polygon relative to the grid size might fail to be built, if all its = component pieces do not happen to fall into a single grid = cell.</div><div>At some point you end up building something of the scale = and complexity of the topology module, and maybe you would be able to = get some good results starting there = instead.</div><div>P.</div></body></html>= --Apple-Mail=_9CAB0C61-59CB-48BE-9729-47D17E55E188--