Use a stored procedure to determine whether a person has stayed longer than the specified time in the travel track of a day.
The stored procedure is used to determine whether a person has stayed for more than the specified time in the travel track of a day. problem description in a project, the patrol staff's hand-held GPS will report the location of the person at a fixed time to the total database. Now, we need to know whether a specified person has stayed at a certain point for more than 20 minutes within the specified time. 2. First of all, the analysis does not mean that the two points are completely merged to stay. GPS coordinates have offsets and errors, and meaningless movements within a certain range cannot be considered as valid movements. Second, we should try to improve the general degree in the code. We should set the distance tolerance range, time tolerance range, query personnel, and query time period to controllable parameters. Finally, as a statistical requirement, writing stored procedures is more efficient when real-time judgment is not performed for each reporting location. 3. Specific implementation
4. optimization of the above scheme is post-hair statistics. Imagine if I want to count the situation for a week, a person can report hundreds of thousands of track points in a week. In the worst case, the number of cycle traversal times of a person is counted to exceed 100,000. If optimization is selected, the optimization direction can be to distribute the pressure to each report for determination. We assume that we need to count the number of people who stay for more than the specified period of time for A week. The specific idea is as follows: A. Create A table with A weekly scheduled condition. The table name is tchumanweekstaycheck. This table can contain two fields: humanID and checked. It is used to record which persons are not on demand in the week. During creation, all personnel numbers are entered into this table, and all checked fields are true. B. When reporting a trajectory, first go to the tchumanweekstaycheck table to check whether the checked corresponding to the humanID is false. If yes, it indicates that the employee has stopped because the employee does not comply with the regulations. Then, set the parameter to false in the handheld device, and the employee will not stop reporting the case next time, improve efficiency (judgment is meaningless ). If no, it indicates that the person has been in compliance with the requirements, and then judge the coordinates with the previous coordinates of the person. If there is a stay, record the stay time. If you do not stop, do not record the stay time. The entire logic is similar to the logic described in the preceding stored procedure. C. When a person makes a stop judgment when reporting the position, and determines that the position has exceeded the preset time, the value of humanID checked in the tchumanweekstaycheck table is changed to false. D. Check tchumanweekstaycheck to know who has exceeded the stay time.