ST_Union performance problem

[akrherz]

I use ST_Union to join together National Weather Service forecast zones and counties. This has worked fine for me for many years now. The most recent update of these zones by the NWS must include a few higher resolution geometries that now cause ST_Union to peg CPU for a few minutes before returning a result. My environment is:

PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit POSTGIS="1.5.8" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel.4.7.1, 23 September 2009" LIBXML="2.7.6" USE_STATS (procs from 1.5 r7360 need u

pgrade)

I am attaching my reproducer table dump, which I dumped via the command:

pg_dump -Fc -t postgis_union_issue postgis > st_union_problem.dump

the simple command to reproduce

select st_union(geom) from postgis_union_issue;

ST_IsValid indicates all the geometries are valid.

Thanks

[akrherz]

so I had to cull some geometries to get under the 5 MB limit, with the culled geometries removed from the query I get:

NOTICE: TopologyException: found non-noded intersection between LINESTRING (-77.0096 35.0631, -77.0089 35.0628) and LINESTRING (-77.0089 35.0628, -77.0089 35.0628) at -77.008930136999936 35.062848054000028

Here's the larger file with the other 3 geometries included:

​http://mesonet.agron.iastate.edu/pickup/postgis/st_union_problem.dump

I don't get the topologyexception when they are included, just very slow.

[pramsey]

I think it's unlikely this will ever get deliberately fixed, though core changes to the overlay code over time might make it better incidentally. If such changes do happen.