| System Category | Asset Management |
|---|---|
| Source | Brightly Confirm |
| Available since | 2024 |
| Organisation |
|
Overview
Brightly Confirm provides asset management software. Local authorities use Confirm to manage such assets as street furniture and any fault reporting that occurs.
Integration with Brightly Confirm TLDR
Confirm exports are placed on a SFTP server.
A Python script is used to download the data.
A standard spatial data transformation task is used to import the data into a temporary location in the Spatial Data Warehouse.
- A check is placed upon the data to ensure all features have been imported successfully.
- If the checks are passed then the import is re-run to the 'live' location in the SDW.
Confirm exports are placed on a SFTP server.
Each export from Confirm should be a comma-delimited CSV file and contain all the fields necessary to be displayed & queried in iShare. Clearly some means of geographic referencing is required. In the following examples latitude / longitude or easting / northing coordinates are used.
It's worthy at this point to note any datetime fields. Postgresql prefers to import datetime strings as YYYY-MM-DD HH24:MI:SS so some manipulation will be required to adjust these fields into the correct format. The following are two examples from one Brightly Confirm installation.
Street Light Faults Field Definitions
Field Name | Data Type | Example / Notes |
Asset Id | character varying | AB/13/E |
Central Asset Id | character varying | Unique ID in Confirm |
Job Number | integer | 173243 |
Job Entry Date | datetime (DD/MM/YYYY HH24:MI:SS) GMT | 19/06/2022 11:37:28 |
Site Name | character varying | Albert Road |
Locality Name | character varying | N4 |
Feature Location | character varying | S/O No 86A Florence Road |
Job Notes | character varying | General Street Lighting Issue - Lamp on 24/7 |
Priority Name | character varying | Street Lighting - 7 days |
Status Code | character varying | JS33 |
Status Name | character varying | Job Received by Contractor |
Asset Number | decimal | 200009.01 |
Feature Type Code | character varying | LSL |
Cms Activation Date | datetime (DD/MM/YYYY HH24:MI:SS) GMT | 19/06/2022 11:37:28 |
Feat Cent East | decimal | 530981.73 |
Feat Cent North | decimal | 187613.56 |
Longitude | character varying | 51.57218637 |
Latitude | character varying | -0.12345678 |
Url | character varying | |
Defect Type | character varying | Column - Day Burner |
Defect Code | character varying | LCDB |
wkb_geometry | well-known text geometry | POINT (-0.09102906 51.58654038) |
Feature Group Code | character varying | SLCO |
Feature Group Name | character varying | SL-Street Lighting Unit |
Feature Type Name | character varying | Street Light Column |
Bin Assets Field Definitions
Field Name | Definition | Example |
Feature Type Name | character varying | Dog Bin |
Site Code | character varying | P421054 |
Site Name | character varying | Albany Close |
Locality Name | character varying | N15 |
Ward Name | character varying | St Anns |
Asset Number | character varying | 7,001.00 |
Feature Location | character varying | by L/C AC805H |
Central Asset Id | character varying | H5400002729 |
Material | character varying | Plastic |
Manufacturer | character varying | Unknown |
Mounting Configuration | character varying | Floor mounted |
Asset Position On Site | character varying | Back of Path |
Surface Base Type | character varying | Block Paving |
Owner | character varying | Waste Management |
Asset Id | character varying | S1 |
Survey Date | datetime (DD/MM/YYYY HH24:MI:SS) GMT | 11/24/23 0:00 |
Feat Cent East | decimal | 532047.79 |
Feat Cent North | decimal | 189119.58 |
Latitude | character varying | 51.58547145 |
Longitude | character varying | -0.09542232 |
Feature Start Date | datetime (DD/MM/YYYY HH24:MI:SS) GMT | 6/13/24 17:34 |
Feature Type Code | character varying | GRIT (for icon and/or colour coding) |
url | character varying | |
Feature Group Name | character varying | HI-Grit/Salt Bins |
Feature Group Code | character varying | GRIT |
Downloading the data
The following is a Python 3 script that is used to download the data from the SFTP server. A configuration file is defined for the server details and the local location of the downloaded file.
Create the python 3 virtual environment
Ensure Python 3 is installed.
Create the SFTP file downloader script
Copy the script below and save it as E:\iShareData\Utilities\SFTPFileDownloader\sftp_file_downloader.py
Create the SFTP file downloader config file
A config file will be required for each file on the SFTP server. Adjust the following example and save under E:\iShareData\Data\<project>\sftp_<project>.config
Create a Program Task in Studio
Create a program task with the following details. Adjust the location of the configuration file.