How add calculated distance to data table block

General
1

I have a view which has a point column. I get a location (also a point ) from the user

then I have a data table pointed to a view and the where clause returns all rows where the distance between the location column and the point from the user is < some number.

I’d like to add to my data table a new column that shows distance to XX (where XX was the location set by the user)

to be a little more clear

the user selects a coal sample from a table
image

in this case W194318

then selects a distance and runs a search for all mines less than distance from the sample

image
image808×152 2.74 KB

this shows a table with all mines < 180 away from W194318

image
image941×203 1.98 KB

I would like to add to this table a column “distance_from_sample” and fill it with the distance between sample and mine location. since this is a search I don’t want to alter the tables. I am not sure if this is possible and if it is how should I attack this? If there is a better way using some other structure than a data table I am all ears.

notes:

  1. distance between sample and mine are searched using a where clause like this:
distanceOnSphere(location, 'POINT(-87.4189 37.1222)') < 289681.92

which in codeless looks like this

image
image811×244 10.7 KB

  1. I convert miles (from the user) to meters for the distanceOnSphere
  2. the distanceOnSphere is working as expected.
  3. the sample location is in a different unrelated table from the mine location

Thanks,
H

2

Suppose there is a way to create such a column. Questions that come to mind are:

Is there only one mine that would be applicable to a sample? If not, which mine should the distance be calculated for?

3

There will be one sample and the table returns a list of mines withing XX radius of the sample.
I’d want to show the mine info (name, location, etc ) and the distance from the sample to that mine.

Ideally the new column would be added to the table

so this

image
image917×221 2.36 KB

would look like this

image
image1232×228 3 KB

4

Are you talking about adding a column in the database or in the UI?

5

in the ui I do not want to alter the DB at all

6

The Data Table component cannot inject dynamic properties on the fly. This means that if you were to use that component for data rendering the column must be a part of the response fed into the UI component. To make it happen, you could add an event handler (in Backend’s Cloud Code) to inject the additional column into the response. Alternatively, it might be possible to accomplish it with a view.

7

sorry for the later response.

I’m not sure how to do that with a view since the distance can’t be calculated until a sample is selected by the user.
same with the event handler I still need to get the selected sample from the user first. ( I am 100% sure this can be done, but I have never done this and am not sure how to attack the issue )

I solved this by placing the returned items in a variable, then cycled through the list with a for loop calculating the distance and adding it as a property to the j(th) item(in the list) . when done I set a property in page data with the final list and pulled that into a data grid. Probably not the most elegant solution but I plan to limit the Max rows returned too 100 (at least right now ) so the performance penalty should be low.

let me know if this is a terrible idea.

H