Finding the nearest location

[DataPro]

I need to find the closest address for an airport. I have an airport database (Apt) which has the airport manager's address, but half of the time it's not at the airport. I've geocoded the address and know how far it is from the center of the airport.

I also have FBO addresses (businesses on the field) in a separate table (FBO) and have geocoded them as well. They relate to the Apt records via an ID field.

What I need to do is find the nearest address to the center of each airport between the one Apt address and the one or more FBO addresses.

[Vaughan]

Why not use one of the web based services that are available now?

"I've geocoded the address and know how far it is from the center of the airport."

That will be "as the crow flies" distance. This can be significantly different to the distance by road. The web service will have already worked all of these details out.

Some time ago I put together a custom function that works out distance between two lat/lon points on earths surface. You may find this useful.

[DataPro]

It's not calculating the distance that I need, I was looking to see if there is a quick way to search through the records, but evidently not so I'll just loop through the records.

I have the distance calc which I found on BrianDunning.com (could be yours), but I could use the logic to calculate the bearing from one point to another. I didn't take trigonometry, so it doesn't come easy to me. Do you have such a formula?

[comment]

Why don't you simply sort the relationship by distance, and take data from the first related record?

[Vaughan]

I cannot see how calculating the bearing would help your original problem of finding the closest airport.

I'd put the current location into a global field (or variable). The Airport table has a calc field that uses this global field (or variable) to calculate the distance for each record in the table: the calc field will need to be unstored because it will change. The shortest distance could then be gained by having a cartesian (X) relationship to the Airports table and calculating the Min( distance) of the related records.

If there are a lot of airport records this could be slow, or at least noticable. It may well be more than acceptable.

An optimisation might be to "save" the results of each calculation in another table, so that it only needs to be done once. It will add a bit of time to the process the first time the calc is done for a prticular location, but after that it may be faster. In fact the calcs could be pre-processed for expected locations beforehand.

An interesting problem with lots of opportunities to play and have some fun. }:(

[DataPro]

Thanks, I've been working on it today and have the logic figured out.

The bearing has nothing to do with this issue, but I thought it would be handy for another purpose.

Appreciate the feedback!

[comment]

You can find the formula for calculating bearing here:

http://www.movable-type.co.uk/scripts/latlong.html

http://williams.best.vwh.net/avform.htm

Translated to Filemaker, I believe it would be:

Let ( [

la1 = Radians ( lat1 ) ;

lo1 = Radians ( lon1 ) ;

la2 = Radians ( lat2 ) ; 

lo2 = Radians ( lon2 ) ;



x = Cos ( la1 ) * Sin ( la2 ) - Sin ( la1 ) * Cos ( la2 ) * Cos ( lo2 - lo1 ) ; 

y = Sin ( lo2 - lo1 ) * Cos ( la2 ) ; 



atan2 = Case ( 

x ; Atan ( y / x ) + Case ( x < 0 ; Pi ; Atan ( y / x ) > Pi ; -2 * Pi )  ;

Sign ( y ) * Pi / 2 

)

] ;

Mod ( Degrees ( atan2 ) ; 360 )

)