1

Resolved

using GeographyCollectionAggregate on set of points that exceed hemispehere limit raise Exception

description

When using GeographyCollectionAggregate or GeographyUnionAggregate on set of points that exceed the hemisphere limit an Exception is raised. What would be better is if NULL is returned as this would match the behaviour of STUnion and would make the aggregate classes more usable.
 
For example: -
 
declare @x geography = 'MULTIPOINT ((0 1), (90 1), (180 1), (270 1))';
declare @y geography = 'POINT (45 1)';
 
select @x.STUnion(@y)   This returns NULL because by unioning @y to @x the new geography exceeds the hemisphere limit.
 
However if using, say GeographyCollectionAggregate, like so: -
 
declare @t table (
name varchar(max),
position geography
);
 
insert into @t (name, position) values ('object 1', 'POINT (0 1)' );
insert into @t (name, position) values ('object 1', 'POINT (90 1)' );
insert into @t (name, position) values ('object 1', 'POINT (180 1)' );
insert into @t (name, position) values ('object 1', 'POINT (270 1)' );
/ including this point causes hemisphere limit Exception /
insert into @t (name, position) values ('object 1', 'POINT (45 1)' );
 
select name, dbo.GeographyCollectionAggregate(position) 'locus'
from @t
group by name
 
The inclusion of the POINT(45 1) forces the 'locus' value to exceed the hemisphere limit but instead of returning NULL a GLArgumentException is raised.

file attachments

comments

bsdz wrote Jun 14, 2009 at 10:35 PM

Here is a patch that resolves the issue by wrapping the ConstructedGeography property in a try/catch block. This patch might be a bit naive in terms of performance and should be accompanied by documentation stating clearly that an aggregate of points exceeding the hemisphere limit will return as an empty geography collection.

wrote Jun 14, 2009 at 10:35 PM

bsdz wrote Jun 14, 2009 at 11:25 PM

I've noticed a problem with my patch. It simply resets the collection of points whenever an exception occurs. This has the side effect of still accumulating points after an exception. The following query exhibits this: -

declare @t table (
name varchar(max),
position geography
);

insert into @t (name, position) values ('object 1', 'POINT (0 1)' );
insert into @t (name, position) values ('object 1', 'POINT (90 1)' );
insert into @t (name, position) values ('object 1', 'POINT (180 1)' );
insert into @t (name, position) values ('object 1', 'POINT (270 1)' );
/ including this point causes hemisphere limit Exception /
insert into @t (name, position) values ('object 1', 'POINT (45 1)' );
/ then this point is included after being reset above /
insert into @t (name, position) values ('object 1', 'POINT (60 1)' );


insert into @t (name, position) values ('object 2', 'POINT (0 1)' );
insert into @t (name, position) values ('object 2', 'POINT (90 1)' );
insert into @t (name, position) values ('object 2', 'POINT (180 1)' );
insert into @t (name, position) values ('object 2', 'POINT (270 1)' );


select name,
dbo.GeographyCollectionAggregate(position) 'locus',
dbo.GeographyCollectionAggregate(position).STNumPoints(),
COUNT (*)
from @t
group by name

I've still to get more familiar with writing aggregate classes using the CLR so I couldn't work out how to place a holding flag on the accumulation stream instructing it to NULLify the aggregate at Terminate.

I can work around this by comparing the values STNumPoints and COUNT then filtering where needed.

wrote Jun 19, 2009 at 11:33 AM

bsdz wrote Jun 26, 2009 at 8:30 AM

I can confirm the latest fixes in SVN correct this issue. Thanks :)

wrote Feb 21, 2013 at 10:39 PM

wrote May 16, 2013 at 10:24 AM

wrote May 16, 2013 at 10:24 AM

wrote Jun 14, 2013 at 7:21 AM