I have sql server table capturing gps data from vehicles every 10 seconds.
Table structure is
|VehicleID|GpsDateTime|Latitute|Longititude|
161 2024-05-23 20:19:59.000 24.80392 85.001716
91 2024-05-23 21:19:34.000 24.803922 85.001573
161 2024-05-23 23:59:59.000 24.790462 84.989324
185 2024-05-23 23:59:49.000 24.790907 84.990018
185 2024-05-23 23:59:59.000 24.790691 84.990284
322 2024-05-23 23:59:59.000 24.790796 84.989244
104 2024-05-23 23:59:59.000 24.803811 85.001493
91 2024-05-23 23:39:59.000 24.790767 84.990036
159 2024-05-23 23:59:58.000 24.771136 85.010258
91 2024-05-23 23:59:18.000 24.803876 85.001511
I Want SQL server query to count how many times each vehicle touch the source point (24.790691 84.990284).
For example If vehicle 185 within range of 30 meters from source point the count is 1, again after some time if it reached within range of 30 meters from source point count is 2 and so on…
means vehicle 185 makes 2 trip around point (24.790691,84.990284)
Desired query Out put
Vehicle ID| VisitCount
185 2
91 3
…..
……..
user25216918 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.