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

[Chris Hermansen]

Chris Date says:

"... the aggregate functions (SUM, AVG, etc) ... simply egnore any nulls
in their argument (except for COUNT(*)) ... if the argument to such a
function happens to evaluate to an empty set, the functions all return
null (except for COUNT, which returns zero."

This from page 222-3,_A Guide to the SQL Standard_, Third Edition, Date
C.J. and Hugh Darwen, 1993 Addison Wesley.

Paragon Corporation wrote:
> 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
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>   


-- 
Regards,

Chris Hermansen         mailto:chris.hermansen at timberline.ca
tel+1.604.714.2878 · fax+1.604.733.0631 · mob+1.778.232.0644
Timberline Natural Resource Group · http://www.timberline.ca
401 · 958 West 8th Avenue  · Vancouver BC · Canada · V5Z 1E5