         CISutil - Collision Information System Utilities
         ------------------------------------------------

CISutil is a VB6 Windows application for processing data of the 
Collision Information System (CIS), and the 
Landmark Kilometre Inventory (LKI).

This is not an IMB-supported application.  It includes utilities that are
used internally at Cypher Consulting, and utilities written for special-
case requirements of MoTI and other CIS and LKI users.

CISutil currently has:
- functions for working with CIS Collision Data Extract (CDE) csv files:
   - Create a Collision Diagram in MS-Word
   - Open CDE in Excel, with convenient formatting
   - Create KML file for use with Google Earth (if CDE file has Lat & Lon)
   - add fields from a Section Definition File.
   - add Latitude and Longitude fields, calculated from SEGNUM and KMMARK
     using LKI centreline geometry.
   - add BC Albers X and Y collision location values, calculated from
     the Latitude and Longitude.

- functions for preparing (fatal) Detail reports for the fatal
  collision standard reports,

- a function for generating a Rate Table in Published Format
  from a CIS (Rate Table) Class-Rate csv file.
  
- a function for reformatting an SSA (Specified Sections Analysis)
  csv file into a format used by Paul DeLeur.
  
- a function for combining SSA location records  into 
  Chuck Hutchinson's CPL report format.

- a function for creating a KML file from the combined SSA location records
  or from a CPL output csv file.

- a facility for assisting with CIS collision validation,
  which automates the look-up of LatLon information, and plotting
  on Google Maps and Google Earth.
  
- a utility for transforming an LKI location backwards and forwards
  through all the versions of the LKI.  (This relies on getting  
  data updates after each LKI release.)  
  See LKIhelp.txt for details.  (Use the Help button on the LKI tab.)

- a utility for transforming 1 or 2 LKI locations per row in an Excel
  worksheet to a specified LKI version.  
  
- a utility for looking up the Latitude and Longitude, and BC Albers
  coordinates, of a provided LKI location, from LKI segment geometry.
  
- a utility for finding the closest LKI location(s) from a specified
  Latitude and Longitude.

- a utility to perform the preceding 2 LatLon conversions in bulk in an 
  Excel worksheet, for one or two locations per row.
  
- a utility to generate collision counts by section, month and severity 
  given a section list and a CDE of an SSA output subset.
  
- a utility for checking for section overlaps in a Section Definition 
  file (SDF).  
  
- a utility for creating a KML file from an SDF, showing the 
  section line, and optionally marking section endpoints 
  and LKI kms at intervals along the line.

- a utility for transforming an SDF to another LKI version.

- a utility for taking an SDF coded with kms to 2 decimal places,
  and producing an SDF with the kms rounded to 1 decimal place,
  and with sections separated by 0.1 km.  
  (The separation logic only works between sections which are
   adjacent in the SDF.)
   
- a utility to generate an SDF of Short Sections by breaking 
  down a specified subset of the LKI according to specified
  criteria.  (See Short_Section_Documentation.pdf)

- a utility to calculate traffic volumes for a specified section
  along a search path.   (See CISutil_VolumeTab_Help.pdf)

Windows 10 Sub-Window Problem
-----------------------------
Occasionally, in Windows 10, a CISutil sub-window 
(e.g. SDF / Short Sections) does not appear, and appears as blank 
in the taskbar pop-up image if you hover the mouse pointer
on the CISutil icon in the taskbar.  If this happens, right-click on
the blank sub-window in the taskbar pop-up image, then select
"Maximize".  This will show the sub-window maximized.  Then drag the
title bar down a little, and this should restore the window to its
proper size.

Getting Help Information
------------------------

This README.TXT file is available from the [ReadMe] button
on the CISutil main form.  The following help files are
available from their related tabs or forms:

 - CDE_for_CD.txt 
     - instructions for creating a Collision Data Extract
       file suitable for creating a collision diagram.  
 - LKIhelp.txt 
     - LKI tab help
 - Short_Section_Documentation.pdf 
     - help for the Short Sections form, reached from the SDF tab.
 - SDF_KML_Help.txt 
 - Process_for_creating_Combined_CPL_Stats.pdf


If you have questions or suggestions, contact:

   Alexander Nicoll                  Matthew Nicoll
   afnicoll@CypherConsulting.com     menicoll@CypherConsulting.com
   778-867-1352                      250-337-8441               


Installation
------------

A note on ZIP files:
  CISutil files are delivered in a "zip" file, which contains
  many individual compressed files.
  Zip files have a file name extension of ".zip".
  In Windows File Explorer these files are displayed as a zip folder,
  with the ".zip" extension hidden, but with a folder icon 
  which has a zip up the side.
  In the following instructions, the phrase 
     "unzip file x.zip into..."
  can be interpreted as 
     "copy files from zip folder x into...".

To install the CISutil application on your workstation:

 - create a folder called CISutil somewhere on your file system.

 - find the latest file CISutil_xxx.zip  (e.g. CISutil_542.zip)
   in MoTI LAN folder: 
       P:\HQ\ENG\Safety - Highway Engineering\CIS\CISutil\
   or Cypher download web page:
       www.cypherconsulting.com/downloads.html
   and unzip it into your CISutil folder.
   
 - see if there is a file named CISutil_Update_vvv.zip
   in the MoTI LAN folder, where vvv is greater than xxx, 
   and if so, unzip that file into your CISutil folder
   (replacing CISutil.exe and README.txt).
   
 - to start CISutil, open CISutil.exe
 
 - to put a CISutil icon on your desktop:
    - right-drag CISutil.exe to your desktop
    - in the pop-up menu, select "Create Shortcuts Here"

 - Note: this is a registry-free installation.  
   It does not register DLL or OCX files in the Windows registry.
   It does not appear in your Windows list of programs.
   (However it DOES use the registry to store your form parameters
    between sessions.)
   
 - to uninstall, simply delete your CISutil folder, and delete your
   desktop icon (if you made one).
   If you wanted to, you could also delete the following node from
   the Windows registry:
   HKEY_CURRENT_USER\Software\VB and VBA Program Settings\CISutil

Version Information
-------------------

0.1.0     2010-09-20 - initial test release
0.1.1     2010-09-21 - 2nd test release
1.0.0     2011-06-11 - added functions for creating web reports.
2.0.1     2011-10-26 - added MapCase tab for Google Map automation
2.1.0     2011-11-22 - added Google Earth option for mapping.
2.1.1     2012-02-02 - improved saving of folder and file names between sessions.
2.1.2     2012-05-02 - requires confirmation if input and output folders differ.
2.2.0     2012-05-24 - added Class Rate tab for generating a Rate Table from Class-Rate output
                     - added KML output from CDE file.
2.3.0     2012-05-30 - added SSA tab for reordering SSA output for Paul DeLeur
2.3.3     2012-12-07 - fixed bug in detail rtf split.
2.4.0     2013-03-26 - enhanced the ClassRate functionality, to use CIS Class-Rate csv output.
                     - added form frmClassVol
                     - modified MapCase tab to get lat/lon from new screen CIS860.
2.4.1     2013-04-09 - put option buttons in frames so that they behave properly.
3.0.0     2014-08-31 - add the LKI (transformation) tab and functionality
3.0.1     2014-12-04 - mods to WebRep code to handle changes in Oracle Reports RTF format.
3.0.2     2015-02-02 - added code in sub LoadTable to get/save LKI version to the Windows register.
3.1.0     2015-04-20 - added SSA_Combine_Locations routine
3.1.1     2015-06-18 - collision diagram fixes for 3-char segment, and null values in Animal test

3.2.0     2015-08-21 - added Excel warning to main form CDE Files tab
                     - changed collision diagram from .doc to .docx
                     - added severity-based colour coding to collision diagram.
          2015-08-21 - Collision Diagram:            
                        - changed from .doc to .docx
                        - added colours for fat, inj & pdo symbols and counts
                        - assume collision symbols are all visible in cd_base.docx
          2016-02-04 - added module CDEbyGroup to generate collision counts by section, 
                       month and severity given a section list and a CDE of an SSA output subset.
                       This is activated by a new button and fields on the SSA tab.
3.3.0     2016-08-21 - got MapCase to Google Earth working again, by creating a KML file
                       in the temp folder for each case. 
3.3.1     2016-08-23 - put ACCase in field on MapCase tab after getting from CIS860
3.4.0     2017-01-09 - add functionality to CDE tab to add fields from SDF file to CDE file.
3.4.1     2017-01-12 - added code to check for 3-digit segment numbers to SDFtoCDE function
                     - added file browse button for SDF file on CDE tab.
3.4.2     2017-01-12 - changed to write a message to status/notes field instead of msg box.
3.5.0     2017-02-03 - added "SDF Files" tab, button "Check for Overlaps".
3.6.0     2017-07-31 - replaced VBA module IOSlib with module Cypherlib and deleted duplicate
                       routines from module CISutil.
          2017-08-01 - added linestring KML routines to the SDF tab and module SDF_Utils.
          2017-08-09 - added the "Get Lat Lon" button to the LKI tab.
                     - added the GeoDBmod module, which references the SegGeo
3.6.1     2017-08-14 - fixed Browse button functionality on CDE tab
                     - added function sav_Valid_LKIversion and calls to same
                       on CDE and SDF tabs to catch invalid LKI versions.
                     - added Help button on SDF tab, to open file SDF_KML_Help.
3.6.2     2017-08-31 - made SDF overlap checking work for sections spanning
                       any number of nodes.  (IncOpp SDF feature not supported.)
3.6.3     2017-08-31 - enhanced KML output so XML special characters are replaced
                       e.g. with "&amp;" for "&".
                     - fixed UNDELIM routine to clear output array.
3.6.4     2017-12-13 - (not released outside Cypher Consulting)
                     - added Geo Source option (CHRIS or GeoBC) to LKI and SDF Files tabs.
                                          
3.7.0     2018-02-22 - (beta)
                     - re-organized VB libraries: eliminated util.bas, moving routines
                       out to Cypherlib.bas, VB6_util.bas, LKI_util_noDB.bas and winlib.bas.
                     - input SDF files may now have quoted string fields.
                     - added option of using input SDF record number as ID, if no SDF
                       ID (or documented alias) field is found.
                     - added Short Sections button to SDF Files tab, and the ShortSections
                       form and functionality.
                     * (No traffic volume output yet for short section output.)
                     - added capability of reading the new LKI Landmark-Intersection table
                       so that multiple Intersection Short Sections can be linked to 
                       the same intersection via an Intersection_ID.
                       (For LKI versions prior to 201807, the table is read from a CSV file.)
3.7.1     2018-02-27 - (beta)
                     - added Highway Class, Reg, Dist, Area and Volume output.
3.7.2     2018-02-28 - (beta)
                     - changed to use Volume_for_CISutil.mdb instead of utvs_volume.accdb
                       and thus got rid of msado15.dll dependency.
3.7.3     2018-03-07 - moved SDF KML parameters to a separate form, and enhanced.   
                     - added code for LKI node KML for multiple-segment sections.
                     - added substitution of segment length for km = 999.9 when reading an SDF.
                     - added Help button to frmShortSections, to open Short_Section_Documentation.pdf
3.7.4     2018-03-09 - added NintLoc1dp and NintLoc2dp to Short Section output 
                       for Intersection sections
                     - delivered with the addition of 2017 traffic volumes, 
                       for use with the 201707 version of the LKI.
3.7.5     2018-03-14 - fixed bug in SDF_Read to get blank field past last comma.
                     - added option in SDF_Read to default to trailing blank fields.
                     - added option for table borders in SDF-KML balloons.
3.7.7     2018-03-16 - added "Sum by Group and Quarter" button to CDE tab, which runs
                       Sub CDE_Sum_by_Group_and_Quarter in module CDEbyGroup.
3.8.0     2018-04-08 - added SDF transform and "2dp to 1dp separated" functions 
                       to the SDF tab.
                     - added capture of ENTER keypress in the LKI tag Segment field
                       to perform the Show function.
3.8.1     2018-04-18 - fixed a bug on the "Reduce to 1DP..." Option Number prompt Cancel code.
                     - enhanced LKI database file search to find the 201707b mdb file, and
                       prompt for a confirmation on the LKI database file name.
                     - includes the latest Landmark-Intersection.csv file.
3.8.2     2018-04-20 - fixed bug finding "Km Mark" heading in sub SDfields2CDE.
3.8.3     2018-04-26 - fixed logic bug in Short Sections selecting segments on Numbered and
                       UnNumbered highways but not Lettered.
3.8.4     2018-05-19 - added Legs to Intersection Short Section output.
                     - for pre-201807 LKI uses Landmark-Intersection_Legs.csv instead of
                       Landmark-Intersection.csv
3.8.5     2018-05-29 - added validation of Short Sections Class-Category list
                     - skip BreakLandmarkType validation if BreakAtLandmarkTypes not checked
3.8.6     2018-05-30 - added separation at nodes, and log file output, to SDF_1DP_Separate routine.
                     - increased MATCHFOLDER length from 80 to 200 in LKI lkitran8_dll.dll
3.8.7     2018-05-31 - fixed bug setting the temp folder for file SDF_1DP_Separate_Log.txt
4.0.0     2018-06-01 - replaced sub SDfields2CDE/SD_Fields_to_CDE with SDF_util/SDF_Fields_to_CDE,
                       enhancing it to a) use the SDF_read routine, b) allow sections to span nodes,
                       and c) handle node names missing a leading zero on the CDE file.
4.0.1     2018-07-10 - 201807 LKI version added to LKI support files.
                       (No software changes.)
4.0.2     2018-08-03 - added geosource to msg: no lat lon available.
                     - packaged with correct GeoBC SegGeometry.
4.1.0     2018-09-17 - added segment number validity check so ShortSections/MakeSegList
                     - added ShortSections Extend50m option
                     - changed to allow non-integer Min & Max short section lengths
                     - fixed bug joining to Intersection tables with 201807 LKI.
                     - changed Integer to Long on variables to allow for > 32000 short sections
4.1.1     2018-09-17 - two more Integer to Long changes to prevent overflow.
4.1.2     2018-09-19 - more Integer to Long changes to prevent overflow.
                     - added check for mainform.status.text overflow
4.2.0     2018-10-08 - enhanced "Combine Intersection Locations" function to handle a variable
                       number of input CPL records per GroupID.
4.2.1     2018-10-29 - fixes in "Combine Intersection Locations" function:
                       - fixed lmktype - was getting it from wrong input column
                       - fixed hwyclass - was getting it from next group
                       - added warnings for inconsistent hwy class in a group
4.3.0     2018-11-06 - added SSA tab "Create KML from CPL or Combined Int. Locns" function.
4.4.0     2018-11-07 - added SDF tab "CPL to SDF, grouped..." function
                       (requires LKI version 201807 or later)
4.5.0     2019-02-27 - added "Find Closest Locations" function to LKI tab.
                     - removed geometry source options, delivered with 
                       "CHgeo" geometry = best of CHRIS & GeoBC.
                     - removed requirement for Date for LKI tab Show Transformations
                       (it will automatically use the date the "Lookup a valid Date"
                       function would obtain).
4.5.1     2019-02-28 - added Short Sections log file output to Function lki_Open_Database
4.5.2     2019-03-01 - improved LKI Get LatLon message when provided LKI location is
                       not on the LKI, or does not transform onto the geo LKI version.
                     - updated module Locn_LatLon to use just CHgeo source.
452a      2019-05-23 - package CISutil_452a.zip contains Volume_for_CISutil.mdb
                       with 2018 traffic volumes added.
4.5.3     2019-07-24 - removed arg from CypherLib function MaxFile, added MaxDir.
                       NOT published: it only affects RTF Detail report split used at Cypher.
4.5.4     2019-08-10 - look for GoogleEarth.exe under "Program Files" AND "Program Files (x86).
                       NOT published: it only affects work at Cypher.
4.5.5     2019-09-27 - fixed bug in Cypherlib function MaxFile          
4.6.0     2020-01-16 - added Direction to LKI Segment Show information
                     - LatLon <--> SegKm in Excel button added to LKI tab,
                       which opens frmLKILatLon, to allow 1 or 2 conversions per row
                       back and forth between LKI Locations and a Lat-Lon.
4.7.0     2020-05-12 - added "LKI Transforms in Excel..." button to the LKI tab
                       which opens frmLKItranExcel, to allow LKI transforms of
                       1 or 2 LKI locations on each row of an Excel worksheet.
                     - added capability of working on the currently active Excel
                       worksheet to frmLKILatLon and frmLKItranExcel
4.7.1     2020-07-12 - no software changes, just repackaged with LKI 202007 data.
4.7.2     2020-10-28 - increased max number of CDE fields from 100 to 200 in 
                       collision diagram code (Sub Read_CDE).
                     - changed to open SegAllVersions.mdb in shared mode
                       in sub sav_Set_db.
4.8.0     2020-11-16 - to the CDE tab, added "Add Latitude & Longitude" function.    
4.8.1     2020-11-18 - small revision to AddLatLon to prevent failure if last
                       field on a data record is blank.
                     - added checks for long headings to "Open CDE in Excel"
4.8.2     2020-12-29 - added Region, District and Area output to the
                       LKI tab Segment Show function.
4.8.3     2021-01-05 - added "Map LatLon" button to MapCase tab.
4.8.4     2021-01-19 - added "_pre2018" suffix to Landmark-Intersection_Legs
                       csv file name.
4.9.0     2021-03-12 - added BC Albers capability to CDE and LKI tabs.     
4.9.1     2021-06-14 - added [?] button beside "LKI Version" field on the
                       "LKI to/from LatLon in Excel" form. 
4.9.2     2021-07-08 - no software changes, just repackaged with LKI 202107 data.
5.0.0     2021-08-16 - added break at Area/District/Region boundaries to 
                       Short Sections form and module.
5.1.0     2021-11-27 - added LKI date options to LKI transforms in Excel.
                     - LKI/Show: changed to skip reg/dest/area lookup if the
                       segment was last in an LKI version prior to 200806.
                       (Because old database file names are less predictable.)
5.1.1     2021-12-16 - added error check on oExcelApp.Interactive = False
                       statement when preparing excel for LKI transform
                     - added error check when looking for an LKI file to open.
                     - added validity checks for km1 and km2 in SDF_Read.
5.2.0     2022-01-24 - module RateTable, sub ClassRate_to_RateTable: fixed
                       code which extracts Class Sets and Volume Ranges from
                       the input csv file.
                     - added message boxes to get user confirmation on that
                       data.
                     *** Warning: CIS still has bug (Vivid CIS-818): 
                         Volume Ranges are not always all included in the SSA csv file.                         
5.2.1     2022-05-30 - added messages to module frmLKItranExcel
                       sub cmdTransform_Click when XL_Get_App_and_Worksheet
                       was not able to find or open an instance of excel 
5.2.2     2022-06-06 - modified Sub vol_AADT_by_year_at_1_Locn to allow for
                       actual zero AADTs, and -1 instead of zero indicating
                       a missing AADT.
                     - includes traffic volumes with corrected interpolations
5.3.0     2023-02-11 - repackaged to include geometry for LKI 202207
		     - added code to Add Geo Fields to better identify lat/lon fields
		     - fixed bugs caused by rounding errors
		     - fixed bug in LKI tab where # Decimal Places was not getting used
		       correctly
5.3.1	  2023-02-24 - added some showstatus statements
		     - corrected packup error
5.3.2     2023-04-04 - added LKIver6 to module vol_Database, so if given an LKIver
                       with a trailing letter, the letter is ignored.
                     - added module ACR_read_and_Lookup for Average Collision Rate
                       lookup for sections.  (Not used yet.)
                     - in sub CPL_to_SDF_Grouped, added a check that the CPL
                       seg-km is valid on the specified version of the LKI.
                       (This will make sure that the correct LKI version is 
                        specified to match the CPL input file.)
                     - in module LKI_Database, added function lki_Segment_Length
                       which can be used to check that the provided segment is on 
                       the LKI, and thus that there is not an LKI version mis-match.
5.3.4     2023-04-08 - added function Km2Dm to module LKI_util_noDB.
                     - added use of ndp setting when looking up Lat-Lon on 
                       the LKI landmark table, in module Locn_LatLon.
                     - packaged with 2022 traffic volumes.
5.4.0     2024-02-22 - added traffic volume calculation utility
		     - packaged with 2023 traffic volumes
5.4.1     2024-03-05 - fixed typo Pubished -> Published on button on Class Rate tab
	  2024-04-05 - edited 'About' msgbox to add Alexander Nicoll as primary contact
	  2024-06-13 - improved Sub sav_Set_db() to prompt for segallversion.mdb locn if not found
	  2024-06-28 - minor formatting improvements
	  2024-07-15 - packaged with LKI_202407 data files
	  
5.4.2     2024-12-10 - new improved algorithm for finding closest Seg-Kms for a Lat-Lon.
                       (Sub Closest_LKI_Locns2 in module LatLon_to_SegKm)
                     - added Lat-Lon to the output list of closest Seg-Kms.
                     - added txtNclosest field on LKI tab to limit the number of 
                       closest Seg-Kms listed.
	             - improved LKITRAN dll initialization code in 
	               Sub cmdFindClosestLKILocations_Click, to avoid crash in LKITRAN DLL.
5.4.3     2025-01-13 - added check in Sub Closest_LKI_Locns2 for when the number
                       of close points is reduced to zero when cutting square down
                       to range circle.
5.4.4     2025-04-23 - packaged with 2024 traffic volume data
		     - packaged with CHgeo_lki202407_SegGeometry.mdb
5.4.5     2025-07-19 - fixed bug in Volume calc utility where output format 'By UTVS' was restricting
			all UTVS elements by the start/end kms.
		     - minor Volume output improvements and code comment.
5.4.6	  2026-02-11 - made LKI geometry file case insensitive
		     - packaged with CHgeo_lki202507_SegGeometry.mdb
5.4.7     2026-03-14 - packaged with 2025 traffic volume data