I'm trying to develop a column function that will count every row of my data table where
1) The :X and :Y coordinates are less than 2 Euclidean distance away
&
2) The :Value is = 5
In this 5 by 5 grid every :Value is set to 1, except for the central datapoint (3,3) which has :Value = 5.
The datapoints in blue are <= 2 unites away from the central datapoint and thus get correctly count 1 datapoint in their range.
The datapoints in red don't have any rows with :Value = 5 in range and thus count a value of zero.
I've been experimenting with different methods to do this. Mostly trying out the For Each Row() command, but that doesn't seem to work well in a column function. I'm probably doing it wrong. Any ideas?
If you need a formula it can get a bit slow but using As Constant can help. You could take a look into Distance(), KDTable() and maybe VPTree().
These are not optimized but are some simple examples on how you can use As Constant() and KDTable/Distance
Current Data Table() << New Column("R", Numeric, Ordinal, Formula(
As Constant(
tree = KDTable(Current Data Table()[0, {"X", "Y"}]);
{rows, dist} = tree << K nearest rows(25, 13);
v = rows[Loc(dist <= 2)];
);
If(Contains(v, Row()),
1,
0
);
));
One example using Distance
Current Data Table() << New Column("R", Numeric, Ordinal, Formula(
As Constant(
five_idx = Where(:Value == 5);
m = :X[five_idx] || :Y[five_idx];
);
Sqrt(Distance(Matrix({:X, :Y})`, m)) <= 2;
));
If you do not need formula, these can be made more efficient
