match location between coordinates

  php, sql-server

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

LEAVE A COMMENT