Average Turnover of Competitors within a proximity Radius

[Anonymous]

Hi,

I have a data set containing Longitude, latitude and store turnover, i would like to have a variable or calculation which calculates the average turnover of stores within a 2km radius.

I tried to load distances between all stores by:

T1:

NOCONCATENATE

LOAD

Ref,

Latitude as Lat1,

Longitude as Lon1

RESIDENT StoreDetails;

OUTER JOIN (T1)

LOAD

Ref as Ref2,

Lat1 as Lat2,

Lon1 as Lon2

RESIDENT Temp;

// Haversine

Distances:

LOAD

Ref,

Ref2,

atan2(sqrt(sqr(sin((Lat2-Lat1)/2))+cos(Lat1)*cos(Lat2)*sqr(sin((Lon2-Lon1)/2))) ,sqrt(1-(sqr(sin((Lat2-Lat1)/2))

+cos(Lat1)*cos(Lat2)*sqr(sin((Lon2-Lon1)/2)))))*2*6371 as "Distance"

RESIDENT T1

WHERE Ref <> Ref2;

DROP TABLE T1;

however because i have over 100 000 stores it bombs out. the equation i would like to do is:

=AVG({$<Distance={'<2'}>}Revenue)

any idea on a better way to do it due to large data set size?

Thanks

Gareth