Select AVLS data location

Choose whether to read the AVLS data from text data files, (e.g. csv or SQL Server).

Files: If you select Files, the Next button will open a file explorer dialog in which you can select multiple files to be processed.

The reading of AVLS data files is controlled by a config file.

SQL Server: First connect to SQL Server with an account with access to the SQL Server database containing the AVLS data:

Click Next.

Now confirm how the data should be read from SQL Server. There are two options:

You can either

Use the AVLS Add-In’s built-in wizard to select the table or view to read and which columns contain the relevant AVLS data; or
Supply your own custom SQL to read the data.

1. Using the built-in wizard

The user has access to specific tables and views in all the schemas in the database. This is displayed in a grid sorted by schema and table/view name.

Select one and click Next to progress to the column matching, i.e. selecting which columns from the chosen table or view the add-in is to read.

Notes

There must be a text field with the Callsign (id) of the vehicle;

There must be two numeric fields for location. These can be either:

An easting and northing in OS National Grid or
A latitude and longitude in WGS 84

There must be either:

A numeric field containing the speed in MPH and a date-time column of when the speed was recorded, or
A numeric field containing a distance travelled in metres and two date-time columns recording the time that travel started and ended.

Optionally there can be a numeric column containing the bearing at the time of travel. For example this can be used to determine which road link to use at cross roads.

There can also be a text field with the status value for the record. For example this could record if the speed was recorded en route to an incident (e.g. blue light speed or returning to base.)

Below are two examples of SQL configuration:

After selecting the relevant columns, click Next.

If a Status column is selected then the next page will show status values read from the selected table:

You can choose whether or not to read records with specific status values.

The next page allows you to specify a date range for the data to read:

Click Next to open the ‘Confirm SQL’ page.

2. Using you own custom SQL

Some services have access to AVLS data in SQL Server but have strict instructions to use a specific SQL statement, e.g. Manchester fire use:

SELECT
h.callsignid,c.callsign,h.lon,h.lat,h.statusid,s.statustext,h.speed,h.heading,h.datastamp
FROM
tblAVLHistory h,tblAVLCallsignHistory c,tblAVLStatusHistory s
WHERE
-- Insert your required conditions here
-- (h.callsignid=<CALLSIGNID>) AND
--(s.statustext='MI') AND
((c.callsign = 'G16P1')) AND
-- Specify your date range here - You must use 'yyyy-mm-dd hh:mm:ss' as the format
((h.datastamp>='2018-01-31 00:30:00') AND (h.datastamp<='2018-01-31 00:45:00')) AND
-- Do not change this section
(c.callsignid=h.callsignid) AND
(s.statusid=h.statusid) AND
(
((s.startstamp<=h.datastamp) AND (s.endstamp>h.datastamp)) OR
((s.startstamp<=h.datastamp) AND (s.endstamp IS NULL))
) AND
(
((c.startstamp<=h.datastamp) AND (c.endstamp>h.datastamp)) OR
((c.startstamp<=h.datastamp) AND (c.endstamp IS NULL))
)
-- End of section

This SQL can be copied and pasted into the Edit SQL box in the Custom SQL page:

The text at the top of this Custom SQL page shows what column names the add-in requires

Click Next. The custom SQL is checked to see it contains the necessary columns.

The column names in the real AVLS database do not match the names the add-in expects. Here the SQL contains:

SELECT … h.lon,h.lat, … ,h.datastamp

The SELECT has to be modified to include aliases, i.e.

SELECT … h.lon AS X, h.lat AS Y, … ,h.datastamp AS DATETIME_1

If the necessary column names or aliases are present then clicking Next will move to the ‘Confirm SQL’ page.

3. Confirm SQL

Both the table configuration wizard and the custom SQL page lead to the ‘Confirm SQL’ page. This shows how many AVLS records will be retrieved by the SQL.