Location, Part 6: What’s Close?

This is Part 6 in a multi-part series about using the location functions in FileMaker Go.

1. Where Am I?
2. What’s Accuracy?
3. Where’s My Stuff?
4. How Far?
5. Which Way?
6. What’s Close?

Now that we can use the location functions in FileMaker Go to get our current geographic coordinates and figure our location relative to particular geocoded points of interest, it’s natural to want to perform a find for any locations in a database close to your current position. “I just finished a job in the field; what other jobs are nearby that I could do next?”

Find within radius

So how do we do that? If you just get your current position with the Location function and perform a find for the matching latitude and longitude, you’ll only be able to find records for other locations within about an 11 cm radius — too cozy for my taste. We want to be able to find records within a given, much larger, radius. You might try using the haversine formula from part 4 to check how far every record in a table is from your current position, and omit all the records that are farther than your search radius. I’ll call this the record scan strategy, and it is molasses slow.

The fast finds we’re accustomed to in FileMaker are fast when they work on fields that are indexed. This means that all the data values in an indexed field are stored separately so that when we perform a find for records with particular values in that field, FileMaker doesn’t have to do a record scan checking the records one-by-one for matches; FileMaker instead finds those values in the index, which tells us much more quickly exactly which records match the values and where to find those records. It’s the difference between finding a book in a library by checking books from a giant disorganized pile one at a time vs. looking it up in the card catalog and finding it on the shelf. (Do libraries still have card catalogs?)

The reason the record scan strategy is slow is that it’s checking records one at a time, rather than quickly finding a whole set at once using a find on indexed fields. So how are you supposed to perform a find for all the records in a circle? Well, you’re not. We can index latitude fields, we can index longitude fields, and we can perform fast finds on either one. But when we try to specify find criteria for both fields falling in a circular region, we run into the problem that the longitude range will be different for every latitude value, and vice versa. Tough luck. This is a fine time to invoke one of my favorite problem-solving strategies: pick an easier problem to solve instead.

If you’re willing to bend the parameters of the problem, there’s a much faster solution. Since latitude and longitude fields can each be indexed, we can perform a find for one range on each field to match a square region around our current location instead of a circular region. (The region will actually only be approximately square due to the geometry on the surface of a sphere.) Since practical distances by transit and neighborhood boundaries rarely correspond to exact circular regions anyway, this is usually just as good. And since it’s typically an order of magnitude faster, I’d say it’s worth it.

More…Location, Part 6: What’s Close? – The Scarpetta Group, Inc..