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

[Paragon Corporation]

Brent

> That gets back to how nulls should be handled generally. 

> If you take the sum() (or avg(), max(), etc) of a numeric column with
nulls, what should the result be?

> IMO it should be a null, as we really don't know. If the nulls in the set
comprise unknown values, then any result predicated on them is also unknown.


> If the user wants the sum(), etc, of the not null values then they can ask
for that easily enough via a where clause.

All RDBMS I know when you sum up things with nulls you get the result
without the null (there are rare aggregates where it is not advantageous to
do that).  Whether that is right or wrong is another question, but it seems
pretty baked into the standard
of how RDBMS work and its convenient because in general that's the behavior
you want and its easy to generate the reverse behavior of what you describe
by slapping a coalesce/case when everywhere, but its not quite so easy to go
the other way around.  Think about it how would you say count only things
that aren't null.


> I'm also unclear on the way Postgis treats empty geometries vs null
geometries, or if they are functionally the same thing. 

> Is an empty geometry value different from a null in a geometry column?  
> Can an empty geometry have a SRID (& a null can't)?
> In what cases would it make sense to replace null geometries with empty
ones?

No.  They are not functionally the same.  For example the intersection of 2
disjoint geometries is obviously an empty collection - it is known therefore
it is not null
And  that empty collection should have the same srid of the 2 geometries
being intersected.

Thanks,
Regina