Quering and Aggregating Geography Point Data

hi all, have table structure this:

create table [locationstats](
[locationstats] [int] identity(1,1) not null,
[point] [geography] not null,
[crossing] [bigint] not null,
[entrances] [bigint] not null,
[exits] [bigint] not null)

table loaded data point derived lat long coordinates.

query table , aggregate data in it, i'd able indcate scale of aggregation. let me try explain way of example:

if collect 200000 records of data 100sq km area, , i'm showing points on map scaled out show of - don't want 200000 rows of data plot map in client. want data aggregated @ say, every 10sq km - assuming there data each of 10sq km areas 100 results crossing, entrances, exists summed up.

however, if zoom show specific 10sq km area, want firstly filter down displayed area , aggregate @ 1sq km level , more granular aggregation.

possible straight out of sql? i'm running 2012 enterprise.

---

"look, we've had drink"

if i'm understanding you're asking...sure can.

you need make geography corresponds 10sq km zoom area, , use stintersects against table. , return 1 of built-in spatial aggregates (say collectionaggregate or unionaggregate) http://msdn.microsoft.com/en-us/library/hh403400.aspx 1 of columns.   

cheers, bob

SQL Server  >  SQL Server Spatial