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

[Charlie Savage]

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/x-pkcs7-signature
Size: 3237 bytes
Desc: S/MIME Cryptographic Signature
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080926/2cf44798/attachment.bin>