Here’s a problem: You have 20,000 records each with latitude and longitude coordinates. You want a search function for these records to show the results closest to the user’s current position on a map. What do you do?
Thanks to Postgres’ full text search and Earthdistance extension we can implement this logic entirely in the database and it’s lightning fast to boot.
This is exactly the problem I had when I was working on my recent project Pirep. The core functionality of the website is based around a map which displays ~20,000 airports in the US provided by the FAA’s database. The map has a search feature on it and I wanted it to display results based on the area the user was looking at on the map. For example, searching for Portland
when looking at New England would return the Portland, Maine airport rather than the Portland, Oregon airport even though Oregon would likely be the more common result given it’s a larger city.
As far as searching goes, given a database with all of the airports in it, indexing by airport name and location isn’t difficult. The difficulty comes in when trying to rank search results based on the proximity to the user’s current location. It’s not exactly feasible to read every record, calculate its distance to the user, sort by distance, and then take your search results in realtime. You could conceivably index airports by their state and only return results from the same state as the user then calculate distance on those results and sort by that. That may be a “good enough” solution, but in my case I wanted, for example, a search for port
when looking at the south Puget Sound to return Port Orchard, WA at the top of the list instead of the further away Port Angeles, WA. Thanks to the Earthdistance extension it’s possible to do this type of calculation entirely in the database and in realtime.