Is this more about calculating the distance or an algorithm / script to get the smallest (shortest) result?
As for the latter, I'd do this:
Set Variable [ $startCoordinates ; Calgary ]
Set Variable [ $listOfDestinationCoordinates ; get a list of coordinates, excluding the start coordinates ]
Set Variable [ $listOfDestinationIDs ; get a list of corresponding primary keys ]
Set Variable [ $listCount ; ValueCount ( $listOfDestinationIDs ) ]
Exit Loop if [ Let ( $i = $i + 1 ; $i > $listCount ) ]
Set Variable [ $theseCoordinates ; GetValue ( $listOfDestinationCoordinates ; $i ) ]
Set Variable [ $thisDistance ; calculate distance using $startCoordinates and $theseCoordinates ]
If [ $thisDistance < $shortestDistance or IsEmpty ( $shortestDistance ) ]
Set Variable [ $shortestDistance ; $thisDistance ]
Set Variable [ $shortestID ; GetValue ( listOfDestinationIDs ; $i ) ]
At this point, you know the shortest distance and have the id of the corresponding destination.
You'd need a user interface to pick a start, and either get a list of possible destinations, or simply read in a list of all destinations stored in the table, which can easily be done using ExecuteSQL().
Don't forget to exclude the start coordinates, or the smallest result will be distance 0 ...
1 of 1 people found this helpful
You probably don't want to be calculating the distance to every record in the table, but perform a find for a small bounding box. Once you get a bounding box with at least one record in it, then you can calculate distances for the much smaller set of found records to find the one closest record.
Ha, I just *knew* you would chime in! Thanks for the interesting link.
Thanks for the answers; well done!
How do I pass the script parameters to your script Find Nearest Unique Location ( latitude ; longitude ; latitudeField ; longitudeField )? Here's hoping I'm asking the right question
All these answers are good. However implementing them is beyond my skillset...right now.
Finally got it to work. Its pretty fast on 1024 records. I think the other ways may be faster but the skill set needed to make them work is beyond me. This to all who helped.
Update: its not calculating the shortest record. For some reason its not picking up the shortestID. When run its showing zero.
I think that
radians ( Airport PNR::Lat2 )
probably will not work as you expect. The function Radians doesn't work on a list of numbers. you can still use the list, but you have to convert each value to radians separately in your loop.
The other thing is that you maybe should start with a search, looking for coordinates that differ only a calculated max from your current ones. you could do that in a loop as well, widening or narrowing the ranges until you have a result of for example 10 airports. After that you can run your loop which will be ready in only half a second
There was a lengthy discussion and solution of something like this using Pythagorean distances.
I implemented this in Java and FMP to help the OP. This solution ran extremely fast (less than 30 minutes). The basic idea was that for each record in the parent table, every child record's coordinates would be checked to find the shortest distance using the Pythagorean formula. My solution ran quickly since I had the child table in memory using a Map (aka "Dictionary") data structure.
There were over 19,000,000,000 (billion) total calculations!
The OP was trying to disprove the idea, as I recall, that speeding tickets reduce fatalities (or something along those lines). He posted data and detailed examples of what he wanted to do.
I'll try to help if I can.
denns, you have posted a FM file for this activity: https://community.filemaker.com/docs/DOC-8243
Using gps locations for the airfields you provided in the file link above, it is very simple to find the mininum or shortest distance from a current location. See Attached file. It uses the Minimum function on a table occurrence. The scripts that were in the original file are not needed. Only one script with two lines.
BTW, if you were to use this with an iPhone or iPad, a script can be written to capture the current gps location directly to the fields for initial location, then simple click on the Find By Sort button.
BTW, the attached file, uses a custom function, to calculate the distances using the gps coordinates. Note that the custom function is also not needed. See the calculated fields in the table definition.
nearestAirport_RF2.fmp12.zip 178.9 K