This question was originally posted here https://stackoverflow.com/questions/8492149/return-records-in-chronological-order-given-a-separation-factor and re-posted on this forum given a user's advice.
I have a massive table in SQL Server 2008, it contains the position reported by technicians every minute. I need to report on this table but in order to control the amount of records that are displayed in the report both a time and distance separation factors need to be taken into account.
So, a query may look like : "Return all records with no less than 5 minutes and/or 300 feet between them".
The time part is done, but I'm having a hard time with the distance factor. I have the latitude and longitude for each point, and I have no problem if I need to include a SQL Server 2008 spatial UDT in order to resolve the problem.
Things I have considered:
Bring the records by the time factor, and apply the separation constrain in the client by calculating the distance between adjacent points and discard those which falls inside the the factor. (the easiest, but it must be the one consuming more resources).
Keep the last record per technician in a cache, pre-calculate the distance between the record and its predecessor, and resolve the constrain in the client. (should consume less resources than 1) since the distance is pre-calculated, however and since the table is BIG It will increase the size of the dataset, not sure if the space is worth the processing savings).
Use the spatial functions in SQL Server 2008, but honestly I had been reading and I couldn't find anything that helps me resolve this type of requirement. Any GIS expert??
I would like to go with the best option possible (maybe not listed above?) and IMO should be the one using the SQL Server features most efficiently.