Straight Table Chart Expression from two table data

[sbiradar]

Hi ,

I have two tables both are not related to each other , both tables have geo coordinate data (Easting & Northing). I want to find distance between Source point(each record from TABLE1) to all destination points(each record from TABLE2).

I am trying to achieve this via Straight table chart in which am using expression column to populate the distance. I want to display the chart only when User selects ID from TABLE1 in selection box and populate the records only for ID selected and the distance calculated from selected ID to all rows in TABLE2.

Sample data :

TABLE1:

ID     Easting      Northing

1     683599          365839

2     834570          238409

TABLE2

ID     Easting      Northing

1     683599          365839

2     834570          238409

3    683599          365839

4     834570          238409

5     683599          365839

6     834570          238409

7     683599          365839

8    834570          238409

I have tried my expression which calculates the distance in Chart but data is not getting populated. Can anyone help here.

[felipedl]

I'd say you'd have to do the combination in script side, something like:

// Simple euclidian distance for two points
set distance = sqrt(pow($1-$3,2)+pow($2-$4,2));

TABLE1:
load * Inline
[
ID,Easting, Northing
1,68.3599,36.5839
2,83.4570,23.8409
];

// Joining all the values of table 2 to table 1 for distance calculation for all possibilities
join (TABLE1)
Load Easting2, Northing2 Inline
[
ID,Easting2, Northing2
1,68.3591,36.5834
2,85.4570,23.8412
3,62.3599,36.5839
4,87.4570,23.8409
5,58.3599,36.5839
6,33.4570,23.8409
7,48.3599,36.5839
8,13.4570,23.8409
];

// creates the distance, using the distance function defined earlier.
_tmp:
Load
*,
$(distance(Easting,Northing,Easting2,Northing2)) as Distance
Resident TABLE1;


drop table TABLE1;