I was looking for SO and did not really find an answer to this question, but this seems like a general problem.
I have several hundred thousand places in the database, each of which has a geocode (lat / long). If that matters, they are distributed across the USA. Now I have a client application in which I want users to give me their lat / long and radius (say, 5mi, 10mi, 25mi, etc.), and I want to return all that match. I can only take into account the value of the distance, which can be obtained, for example, by the Haversin formula, and not by the shortest distance. However, given this, I want it to be as accurate as possible.
This database is mostly read-only. On a good day, there may be 10 inserts. Now I will have hundreds of customers, maybe tens of thousands of customers who will use the software. I want users to get results in a few seconds, but if one request takes 10-20 seconds, it will be scanned when hit with a load of clients.
How can I best serve the results? I know that I can just store them in MySQL or PostgreSQL (Oracle and MS SQL Server exist for this, but some other open source repositories can be great) and just put the Haversin formula in the WHERE clause, but I don’t think it will effective results.
source
share