Can I make SQL Queries to Filter Data From SpeedLane?

[Vipin Malik]

#271

I need to download specific data from the SpeedLane (Pro) and do not wish to download all the raw data.

For example, I need to know the fraction (or percentage) of vehicles in the 1st lane as compared to all the vehicles over the last interval (say 5 minutes). Rather than download all the data, can I simply make an SQL query to the SpeedLane and get this result?

[Vipin Malik]

#272

Yes! The SpeedLane (Pro) saves all per vehicle as well as interval data (which is per lane per defined interval of time) data in an on-board SQLite DB. You may run SQL queries that correspond to the SQLite feature set from our Windows program (Houston Radar Advanced Stats Analyzer) “Custom SQL Query Window”. Alternatively, you may request a “C” or “C#” SDK to integrate this capability into your host/system/computer to automate this process.

Here is an example of a query that will return you the fraction of vehicles in Lane #1 over the latest interval. Interval times are user configurable via the setup GUI in 1 minute increments starting from a minimum value of 1 minute.

WITH

        total AS ( SELECT sum(Volume) as laneTotals FROM intervals WHERE IntervalNo=(SELECT MAX(IntervalNo) FROM intervals)),

        laneVolume AS (SELECT Volume FROM intervals WHERE IntervalNo = (SELECT MAX(IntervalNo) IntervalNo FROM intervals) AND Lane=1)

SELECT

        1.0*Volume/laneTotals as fixed1

FROM total, laneVolume

Please contact us to request our SpeedLane SDK Users guide for a complete database schema and further examples if you would like to implement this functionality.

[Author]

Posts