I have a static table with location name, latitude, longitude, tolerance. for example:
NY, 40.7128, 74.0060, 100 x 50
There are 600 records at this time, the table will grow slowly.
Also there is a dynamic table with MAC address, and coordinates that change every few minutes:
AC233F271FE4, 40.7228, 74.0110
With 4000 records
I want to count how many MAC addresses are within tolerance for each location. Accuracy for earth being a sphere/ellipsoid is not important.
At first I was going to calculate the distance between two points in SQL query when I want to display count, but now I am thinking if it is better to calculate this in php when I update coordinates for the MAC address. I could add a location column, calculate closest point, update MACs lat/long/location every few minutes. Then SQL query for displaying count would be a simple SELECT COUNT GROUP.
The main question is – at what stage is it better to determine location within tolerance?
Second question would be – do I use geography function of SQL (I have read that it is slow) or Haversine formula and how to implement tolerance into distance between two points?
Source: Ask PHP