Pythagoras please? Shortest Distance between multiple coordinates

Discussion created by anthonymellor on Mar 9, 2017
Latest reply on Mar 20, 2017 by anthonymellor

Hello World, a debutant writes. Using FM Pro Adv 15.03.305 for one day.


I have two files/tables each with a pair of coordinates (OSGB) in each record. Two fields per record.


There is no common field/relation.


I need to apply Pythagoras (distance calc) to each pair of coordinates in table1 to every pair of coordinates in table2, selecting the minimum found in table 2, subject to a constraint of a further table1/field3 value being between two fields in table 2. (In short date in table 1 is between two dates in table 2( but these are not technically dates as such, just numbers)


The constraint is:


table1/field3 (e) is between table2/field3 (f) and table2/field4 (g)


Where if the above is true then:


table1/fleld1 = a

table1/field2 = b

table2/field1 = c

table2field2 = d


The Pythagoras I need is (a-c)*(a-c)+(b-d)*(b-d)      (which = c^2)


Can anyone give me the code please?

I have spent hours reading many pages and arrive here in desperation. Thus far I have been able to proceed by following the online docs and discussions in here step by step, but this matter has defeated me.


Basically it's an SQL Sub query, or an Exel array, or an AWK short script, but now I have the data in FM after weeks of preparation, I am being stymied by this. I gather it may be achieved with Execute SQL, but I also read that this may be slow (I have six million records in table 1 and 3,300 in table 2 = 20 billion calculations ). This is why I have excluded taking the square root in the above as this can be done afterwards. I also gather FM's SQL is it's own variant and having tried it I get nowhere.


I really do not want to have to take the data out of FM, process it and put it back as this seems to defeat the idea of having FM and I'd like to learn how to do this for myself, which so far has been a miserable, time consuming and frustrating failure as an objective.


So, is there a kind soul out there who can fix this for me?


I can upload samples if that helps; even the AWK script or the Excel arrays that work.




p.s. I have also gleaned this below from the web, though for my purposes I think Let([a2=(a-c) * (a-c) + (b-d) * (b-d)];)  is more like it, but hey..

I seem to think this is to create some sort of user defined function

// returns the length of the hypotenuse based on the Pythagorean Theorem

// input two numbers output number



a2=(a-c) * (a-c);

b2=(b-d) * (b-d);






and here the possible PostgreSQL:


UPDATE ACC SET ACC.ACC_EC = SITESmin.ACC_EC,   ACC.ACC_NC = SITESmin.ACC_NC FROM ACC   INNER JOIN LATERAL ( SELECT TOP 1   *   FROM SITES   ORDER BY ( acc_ec - site_etg ) * ( acc_ec - site_etg ) + (acc_ncb - site_ntg ) * ( acc_ncb - site_ntg )   ) SITESmin;