On Wed, May 11, 2011 at 9:17 PM, Aren Cambre
<aren@arencambre.com> wrote:
2. Not TxDPS reference markers correspond to TxDOT reference markers.
Now, if I've matched a route, I have to find the reference marker.
The TxDOT database is pretty good but not 100% complete, so some TxDPS tickets' reference markers may not exist in the TxDOT table. Plus, it's possible that some TxDPS tickets have the wrong marker.
To compensate, I am looking for the closest reference marker along the route that is not more than 50 marker units away, either direction. I've again implemented that with multiple queries, where I don't stop until I find a match. Suppose I am searching for reference marker 256 on TX 71. The queries will be like this:
- rte_nm = 'SH71' AND rm = '256' (base marker)
- rte_nm = 'SH71' AND rm = '257' (+1)
- rte_nm = 'SH71' AND rm = '255' (-1)
- rte_nm = 'SH71' AND rm = '258' (+2)
- rte_nm = 'SH71' AND rm = '254' (-2)
- ...
- rte_nm = 'SH71' AND rm = '306' (+50)
- rte_nm = 'SH71' AND rm = '206' (-50)
Assuming a matching route name was found in the prior step, the app will have 1 to 101 of these queries for each ticket.
This is a perfect example of a place where you could push some work out of the application and into the database. You can consolidate your 1 to 101 queries into a single query. If you use:
WHERE rte_nm='SH71' AND rm >= 206 AND rm <= 306 ORDER BY abs(rm - 256), rm - 256 DESC LIMIT 1
it will always return the same value as the first matching query from your list, and will never have to make more than one trip to the database. Your one trip might be slightly slower than any one of the single trips above, but it will certainly be much faster in the case where you have to hit any significant % of your 101 potential queries.
-Eric