[postgis-users] GeometryCollection(Empty) is SRID dependent (or supporting nulls in st_union)

[Paragon Corporation]

I'm wondering if it might be better to just change the st_geom_accum
function  that all the aggregate functions rely on to collect the geometries
to just ignore nulls.

I can't think of a reason why any of the spatial aggregates should even be
bothered with nulls.  

Thanks,
Regina

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Charlie
Savage
Sent: Saturday, September 27, 2008 12:24 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] GeometryCollection(Empty) is SRID dependent (or
supporting nulls in st_union)

In postgis sending null to functions such as st_union causes the result to
be null.  To get around that, postgis defines the collection of an empty
geometry as GEOMETRYCOLLECTION(EMPTY).  As a result, in our application we
check for null geometry bounds, and if they are null, substitute an empty
collection.

The problem with this is doesn't work if you have geometries in different
SRIDs.  For example:

select asewkt(st_union('srid=4326;point(1 1)'::geometry,
                        'GEOMETRYCOLLECTION(EMPTY)'::geometry))

ERROR:  Operation on mixed SRID geometries

Since we're substituting out for null geometries, we can't know what the
correct SRID is for the empty geometry collection.  Thus when these empty
collections are unioned with other geometries they will blow up if they
don't share the same srid.

So a proposal - checking the SRID values for Empty Geometry collection
should be dropped.  Since the geometry is empty, it has no effect on the
final calculations anyway, I don't think this change would break any code.

The other way I around this is to fix st_union and other functions to ignore
nulls.  This would be more inline with what postgresql does with other
aggregates:

select sum(numbers.value)
from
(select 1 as value
union
select null as value) as numbers

The result is 1, not null.

The 2nd options seems nicer to me (the empty geometry collection idea has
always seemed a big kludge to me), but either would work for us.

Thoughts?

Charlie