GeographyUnionAggregate and hemisphere limitation

Jun 11, 2009 at 9:23 PM

 Hi

Thanks for creating such great extensions.

I am currently trying to use GeographyUnionAggregate and notice if my aggregate points create a geography which exceeds the hemisphere limit then an exception occurs.

Is it possible to test if a union of a two geography instances exceeds the hemisphere limit inside the aggregate function and if it does then nullify that aggregate?

Or perhaps there is a filter I can add to my SQL-where clause that can remove problematic records?

Blair

Developer
Jun 15, 2009 at 6:30 PM

I'll change UnionAggregate to return NULL, the same way as STUnion method does when result can't fit in a hemisphere. That way there would be no need for filtering.

Marko

 

Jun 15, 2009 at 6:35 PM

Thanks Marko

Just to also let you know CollectionAggregate has the same problem. You will see a naive attempt at fixing this in a submitted patch.

Mar 20, 2010 at 12:22 AM

Hello,

I tried using the GeographyUnionAggregate to aggregate county objects from the northwestern US, including Alaska.  The result was null.  I excluded the counties that were in Alaska, and the aggregation worked fine.

Does anyone know if this is the "hemisphere limitation" causing the null shape to be returned when Alaska is included in the query?

Thanks,

Shane

Developer
Mar 20, 2010 at 2:41 AM

Shane -

I'm not sure what the issue is with your data but it is not a "hemisphere limitation".  I just used GeographyUnionAggregate to to aggregate all of the US County objects (3146 rows) into a single geography (1 row).  The original dataset was from the US Census.

- Ed

Mar 20, 2010 at 2:10 PM

Ed,

Can you point to where you got your dataset?

Thanks,

Shane

Developer
Mar 20, 2010 at 2:18 PM

Shane -

 You can get this data in SQL Server backup form (along with a lot of other data from) from here , or you can get the original dataset (shapefile) here

- Ed.

 

Mar 21, 2010 at 8:39 PM
Edited Mar 21, 2010 at 9:06 PM

Ed,

First let me say thanks for the links.

I ran the query shown below, using a database restored from the backup you pointed me to.  Along with a spatial result consisting of the lower 48 and hawaii, I got the response shown below in red.  When I ran the same query for just Alaska, I got the same response and no image of Alaska.  Any thoughts as to why that might be happening?

SQL:
SELECT NAME_1, dbo.GeographyUnionAggregate(GEOG) state
FROM [Sample_USA].[dbo].[Counties]
group by NAME_1

Response: One or more spatial objects were too large to display. Please refine your query.

Shane


UPDATE: I finished running a query (see below) to aggregate the counties into one shape (US), and after 40+ minutes I got the following exception: 
SQL: SELECT iso, dbo.GeographyUnionAggregate(GEOG) usa FROM Counties group by ISO
Exception: Msg 6532, Level 16, State 49, Line 1
.NET Framework execution was aborted by escalation policy because of out of memory.
System.Threading.ThreadAbortException: Thread was being aborted.
System.Threading.ThreadAbortException:
   at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo)
   at Microsoft.SqlServer.Types.GLNativeMethods.ThrowExceptionForHr(GL_HResult errorCode)
   at Microsoft.SqlServer.Types.GLNativeMethods.GeodeticIsValid(GeoData g)
   at Microsoft.SqlServer.Types.SqlGeography.IsValidExpensive()
   at Microsoft.SqlServer.Types.SqlGeography.ConstructGeographyFromUserInput(GeoData g, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeographyBuilder.get_ConstructedGeography()
   at SQLSpatialTools.GeographyCollectionAggregate.ConstructedGeography()
   at SQLSpatialTools.GeographyCollectionAggregate.Write(BinaryWriter w)
   at SQLSpatialTools.GeographyUnionAggregate.Write(BinaryWriter w)

Developer
Mar 21, 2010 at 9:45 PM

Shane -

You have two issues going on here:

1. "One or more spatial objects ..."  The final aggregated instance (a mulitpolygon) contains more than ~5,000 vertices.  We throttled Management Studio map visualization in this version to not display very large objects.  We will probably up the size limit for the next release but for now the object that you have created is simply too big to display in Management Studio.

2. "...aborted ... out of memory"  You have run out of memory, pure and simple.  What you are trying to do is very compute and memory intensive.  On my 4 processor 64-bit box with 4GB RAM and nothing else running, it took over 50 minutes to create the aggregate for all counties, including Alaksa.  Solution, get a 64-bit box and 8GB of RAM or simplify your problem (you can get the same result by aggregating the States table).

-Ed

Mar 21, 2010 at 9:53 PM

Ed-

Thanks for the quick response.

1. Good to know that there's not really anything wrong.

2.  Simple enough solution.

On an off topic, one last thing, I have a topic going about debugging the SpatialTools project & UDAs in general.  Would you mind waying in.

Thanks again for your help, you've save me a lot of head aches.

Shane