[postgis-devel] ST_Union, ST_Collect and ST_Difference strictness

Sandro Santilli strk at kbt.io
Mon Dec 18 06:25:22 PST 2017


On Mon, Dec 18, 2017 at 02:03:09PM +0000, Darafei "Komяpa" Praliaskouski wrote:
> Hi all,
> 
> I propose to change strictness for ST_Union, ST_Collect and ST_Difference
> to non-strict.
> 
> It's common in my workflows to have constructs like
> 
> ST_Difference(a.geom, (select ST_Union(geom) from clipping_table b where
> ST_Intersects(a.geom, b.geom)))
> 
> Right now it has not evident side effect of geometries disappearing if
> ST_Union had no rows to collect. It surely can be fixed with coalesce, but
> you have to first know of such behavior with NULLs. :)
> 
> So, I propose non-strict handling of nulls:
> ST_Union(A, NULL) => A,
> ST_Union(NULL, A) => A,
> ST_Union(NULL, NULL) => NULL,
> ST_Collect(A, NULL) => A,
> ST_Collect(NULL, A) => A,
> ST_Collect(NULL, NULL) => NULL,
> ST_Difference(A, NULL) = A,
> ST_Difference(NULL, *) = NULL.
> 
> any thoughts?

NULL is for "unknown", while what you're after is an EMPTY
How about seeing if ST_Union(geom) can return an EMPTY when passed
nothing ?

--strk;


More information about the postgis-devel mailing list