1

I have an SQL table where I can store a user location (Longitude and Latitude) and the time his location was captured, I'd then like to SELECT any user in the table and retrieve all other users that were captured near his location at a specific time skew.

For example, let's say I have Alice and Bob. Alice visited the city center at 5 PM on day 12/12/2020. Bob also visited the city center on the same day at 5:03 PM (have gotten near Alice within a radius of 5 meters), they both were in the same place within 5 meters and 4 minutes.

Assuming that these numbers pass my threshold, I'd like to get Bob as a result of querying Alice.

My (not so efficient) attempt was to cross join the table with itself as [x.id | x.location | x.time | y.id | y.location | y.time] and then selecting all rows where x.location is near y.location AND x.time is close to y.time.

Note that x.location is used just for a simple example, in reality, I'd use x.longitude and x.latitude instead.

This solution would pass in case of just Alice and Bob, but in a real-world scenario, as my user base grow and the number of logs also grows, it'd be an awful experience for my server to handle this query.

I'm using SQL Server with LINQ and .Net Core 3.1 if this would be of an important notice.

bfahm
  • 188
  • 5
  • 14
  • Show us your actual attempt, don't describe it to us. – Thom A Jun 22 '20 at 12:05
  • You could use simple mathematic to sort you users by distance between 'em – Roman Kalinchuk Jun 22 '20 at 12:09
  • does this answer your question? https://stackoverflow.com/questions/16465779/sorting-mysql-query-by-latitude-longitude – Roman Kalinchuk Jun 22 '20 at 12:10
  • The best way is to create a hash (boundary conditions in the link above). With Longitude and Latitude the hash sizes may not always be the same size. Suppose you want the hash to contain less than 1 million people. The area (square miles) of the hash would be different depending on location. A city like NYC would have four hashes while the North and South Pole may contain 1,000 square miles. – jdweng Jun 22 '20 at 12:24
  • 1
    Are you storing the latitude & longitude as discrete numeric fields, or as a `geography` type? – alroc Jun 22 '20 at 12:44

0 Answers0