12 Replies Latest reply on May 27, 2017 8:40 AM by rouelf

# Calculating distance between current location and multiple locations.

CurLoc=current location

I need to calculate distance (formula known) between current location and 2000 locations records stored in a table and then pick the shortest distance. I'm learning Filemaker as we speak but am not a professional.

e.g. CurLoc to CYBW, CurLoc to CYEG, CurLoc to CYQF and then pick the shortest distance. Basically I'm comparing current location to multiple locations in an airport database and then picking the airfield thats closest to my location.

• ###### 1. Re: Calculating distance between current location and multiple locations.

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 ) ]

Loop

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 ) ]

End If

End Loop

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 ...

• ###### 2. Re: Calculating distance between current location and multiple locations.

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.

1 of 1 people found this helpful
• ###### 3. Re: Calculating distance between current location and multiple locations.

jbante wrote:

Ha, I just *knew* you would chime in! Thanks for the interesting link.

• ###### 4. Re: Calculating distance between current location and multiple locations.

Thanks for the answers; well done!

• ###### 5. Re: Calculating distance between current location and multiple locations.

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

• ###### 6. Re: Calculating distance between current location and multiple locations.

If you use MBS FileMaker Plugin on a Mac, you can use the CLGeocoder functions.

They allow to map addresses to coordinates via CLGeocoder.GeocodeAddressString function or back using CLGeocoder.ReverseGeocodeLocation.

Once you got two coordinates, you can use CLGeocoder.Distance to calculate distance.

• ###### 7. Re: Calculating distance between current location and multiple locations.

The parameters are formatted as Let Notation. You can make it directly, or you can use these custom functions. If you're running FileMaker 16, you might want to modify the script to use JSON.

• ###### 8. Re: Calculating distance between current location and multiple locations.

All these answers are good. However implementing them is beyond my skillset...right now.

• ###### 9. Re: Calculating distance between current location and multiple locations.

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.

• ###### 10. Re: Calculating distance between current location and multiple locations.

I think that

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

• ###### 11. Re: Calculating distance between current location and multiple locations.

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.

• ###### 12. Re: Calculating distance between current location and multiple locations.

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.

1 of 1 people found this helpful