Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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 a customer's 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

https://some/unique/url

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

...

Waste 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

https://some/url/

Feature Group Name

character varying

HI-Grit/Salt Bins

Feature Group Code

character varying

GRIT

...

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.

Paste code macro
mkdir e:\isharedata\utilities\SFTPFileDownloader
cd e:\isharedata\utilities\SFTPFileDownloader
c:\python3\python.exe -m venv ./venv
venv\scrpts\activate.bat
python -m pip install --upgrade pip
pip install pysftp

Create the SFTP file downloader script

Copy the script below and save it as E:\iShareData\Utilities\SFTPFileDownloader\sftp_file_downloader.py

Paste code macro
languagepython
import sys
import configparser
import pysftp
import warnings

if __name__ == "__main__": 
  warnings.filterwarnings("ignore")

  config = configparser.ConfigParser()
  config.read(sys.argv[1])
  host = config['SFTP']['host']
  user = config['SFTP']['user']
  password = config['SFTP']['password']
  sftp_file_location = config['SFTP']['file_location']
  local_file_location = config['LOCAL']['file_location']

  cnopts = pysftp.CnOpts()
  cnopts.hostkeys = None

  with pysftp.Connection(host, username=user, password=password, cnopts=cnopts) as sftp:
    try:
      sftp.get(sftp_file_location, local_file_location)
    except:
      print(f"Download failure: {sftp_file_location}")

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

Paste code macro
[SFTP]
host = sftp.some.server
user = some_user
password = some_password
file_location = path\file.csv
[LOCAL]
file_location = E:\iShareData\Data\path\file.csv

Create a Program Task in Studio

Create a program task with the following details. Adjust the location of the configuration file.

Paste code macro
Program: E:\iShareData\Utilities\SFTPFileDownloader\venv\Scripts\python.exe
Additional Parameters: E:\iShareData\Utilities\SFTPFileDownloader\sftp_file_downloader.py E:\iShareData\Data\<project>\sftp_<project>.config

Run the task and confirm the file has downloaded

Transfer the data to a temporary location in the Spatial Data Warehouse

Ensure that Studio supports VRT files for Spatial Data Transformations.

Inspect E:\iShareData\LIVE\config\studio\config\ConTypes.xml and check the following FileConType is defined. If not add it.

Paste code macro
<FileConType>
  <Name>VRT File</Name>
  <FormatName>VRT</FormatName>
  <Direction>Both</Direction>
  <FileFilter>VRT files (*.vrt)|*.vrt</FileFilter>
</FileConType>

Create a VRT for the CSV file.

VRTs are a virtual format supported by OGR/GDAL. More information can be found here https://gdal.org/en/latest/drivers/vector/vrt.html.

Ensure that the OGRVRTLayer name matches the csv file name. It is best to use lower case and underscores and definitely no spaces for both.

The following is an example of the waste assets. There's a SrcSQL definition where is a SQL statement is present to adjust the datetime fields into a suitable format for Postgresql. The source 'table' must also match the csv file name. Note that each field name has a type e.g. string or numerical.

The geometries are defined by the GeometryType (wkbPoint), LayerSRS (EPSG:27700) and the GeometryField elements.

Paste code macro
<OGRVRTDataSource>
  <OGRVRTLayer name="waste_assets">
    <SrcDataSource>E:\iShareData\Data\<project>\waste_assets.csv</SrcDataSource>
    <SrcSQL dialect="sqlite">SELECT *, (substr("Survey Date", 7, 4) || '-' || substr("Survey Date", 4, 2) || '-' || substr("Survey Date", 1, 2) || substr("Survey Date", 11, 9)) as formatted_survey_date,    (substr("Feature Start Date", 7, 4) || '-' || substr("Feature Start Date", 4, 2) || '-' || substr("Feature Start Date", 1, 2) || substr("Feature Start Date", 11, 9)) as formatted_feature_start_date, replace("Feat Cent East", ",", "") as easting_decimal, replace("Feat Cent North", ",", "") as northing_decimal FROM waste_assets</SrcSQL>
    <GeometryType>wkbPoint</GeometryType>
    <LayerSRS>EPSG:27700</LayerSRS>
    <GeometryField encoding="PointFromColumns" x="easting_decimal" y="northing_decimal"/>
    <Field name="feature_type_name" src="Feature Type Name" type="String" />
    <Field name="site_code" src="Site Code" type="String" />
    <Field name="site_name" src="Site Name" type="String" />
    <Field name="locality_name" src="Locality Name" type="String" />
    <Field name="ward_name" src="Ward Name" type="String" />
    <Field name="asset_number" src="Asset Number" type="String" />
    <Field name="feature_location" src="Feature Location" type="String" />
    <Field name="central_asset_id" src="Central Asset Id" type="String" />
    <Field name="material" src="Material" type="String" />
    <Field name="manufacturer" src="Manufacturer" type="String" />
    <Field name="mounting_configuration" src="Mounting Configuration" type="String" />
    <Field name="asset_position_on_site" src="Asset Position On Site" type="String" />
    <Field name="surface_base_type" src="Surface Base Type" type="String" />
    <Field name="owner" src="Owner" type="String" />
    <Field name="asset_id" src="Asset Id" type="String" />
    <Field name="survey_date" src="formatted_survey_date" type="DateTime" />
    <Field name="feat_cent_east" src="easting_decimal" type="Real" />
    <Field name="feat_cent_north" src="northing_decimal" type="Real" />
    <Field name="latitude" src="Latitude" type="Real" />
    <Field name="longitude" src="Longitude" type="Real" />
    <Field name="feature_start_date" src="formatted_feature_start_date" type="DateTime" />
    <Field name="feature_type_code" src="Feature Type Code" type="String" />
    <Field name="url" src="url" type="String" />
    <Field name="feature_group_name" src="Feature Group Name" type="String" />
    <Field name="feature_group_code" src="Feature Group Code" type="String" />
  </OGRVRTLayer>
</OGRVRTDataSource>

It is worth testing that the VRT file has been defined correctly. You can use OGR to do this. Identify where the location of OGR by inspecting the OGR Path parameter in Studio's settings. Open a command window and navigate to that folder. Enter the following to test the VRT file. If correct OGR will report back the features found in the VRT file.

Paste code macro
ogrinfo.exe path\to\file.VRT

Create the Spatial Data Transformation task

For the Source Data select 'VRT File' and enter the path to the VRT file. For output select a schema and a temporary table location for the data. Uncheck 'Projection as British National Grid' if the VRT file is not using EPSG:27700 as the SRS. If you do uncheck this option specify the destination projection by selecting 'Show expert mode' and entering the following in the Additional Parameters box.

Paste code macro
-t_srs "EPSG:27700"

Run the task and confirm the data has been loaded to the destination specified by using QGIS or pgAdmin.

Check that the data has been transferred correctly

Create the Python 3 environment

Paste code macro
install .net 4.8
install Microsoft C++ Build Tools via https://visualstudio.microsoft.com/visual-cpp-build-tools/ --> Run exe --> Check C++ build tools --> check MSVC v142 and Windows 10 SDK --> wait for installation to finish

mkdir e:\isharedata\utilities\DataSourceChecker
cd e:\isharedata\utilities\DataSourceChecker
c:\python3\python.exe -m venv ./venv
set - this displays the environment variables
edit venv\Scripts\activate.bat
  redefine PATH in activate.bat so it doesn't mention c:\python27 or ogr paths
  set GDAL_DATA in activate.bat to blank if it exists as an environment variable
venv\Scripts\activate.bat
download https://github.com/cgohlke/geospatial-wheels/releases/download/v2024.9.22/GDAL-3.9.2-cp39-cp39-win_amd64.whl and copy to e:\isharedata\utilities\DataSourceChecker
pip install GDAL-3.9.2-cp39-cp39-win_amd64.whl
pip install numpy

Create the feature_count.py script

Store the following as E:\iShareData\Utilities\DataSourceChecker\feature_count.py.

Paste code macro
languagepython
import sys
import configparser
from osgeo import ogr

def count_features_in_ogr(ogr_driver, ogr_connection, ogr_layer):
  # Register the VRT driver
  ogr.RegisterAll()
  ogr.DontUseExceptions()

  # Open the datasource
  if ogr_driver == 'OGR_VRT':
    datasource = ogr.Open(ogr_connection)
  elif ogr_driver == 'PostgreSQL':
    datasource = ogr.Open(ogr_connection)
  else:
    driver = ogr.GetDriverByName(ogr_driver)
    datasource = driver.Open(ogr_connection, 0)

  if datasource is None:
    print(f"Failed to open {ogr_connection}")
    return 0

  layerList = []
  for i in datasource:
    daLayer = i.GetName()
  if daLayer == ogr_layer:
    feature_count = i.GetFeatureCount()

  return feature_count

def list_ogr_drivers():
  cnt = ogr.GetDriverCount()
  formatsList = [] # Empty List

  for i in range(cnt):
    driver = ogr.GetDriver(i)
    driverName = driver.GetName()
    if not driverName in formatsList:
    formatsList.append(driverName)

  formatsList.sort() # Sorting the messy list of ogr drivers

  for i in formatsList:
    print(i)
    return 0

if __name__ == "__main__": 
  config = configparser.ConfigParser()
  config.read(sys.argv[1])
  source_driver = config['SOURCE']['source_driver']
  source_connection = config['SOURCE']['source_connection']
  source_layer = config['SOURCE']['source_layer']
  source_feature_count = count_features_in_ogr(source_driver,source_connection,source_layer)
  destination_driver = config['DESTINATION']['destination_driver']
  destination_connection = config['DESTINATION']['destination_connection']
  destination_layer = config['DESTINATION']['destination_layer']
  destination_feature_count = count_features_in_ogr(destination_driver,destination_connection,destination_layer)
  if source_feature_count != destination_feature_count:
    print(f"feature counts do not match")

Each dataset that is being imported will need a configuration file. Edit accordinly to suit the source & destination dataset. Here we wish to check that the VRT file contains the same number of features as the version held by the Spatial Data Warehouse. Store the file as E:\iShareData\Data\<project>\feature_count_<project>.config.

Paste code macro
[SOURCE]
source_driver = OGR_VRT
source_connection = E:\iShareData\Data\path\file.vrt
source_layer = the_layer

[DESTINATION]
destination_driver = PostgreSQL
destination_connection = PG: host=<SDW host> user=isharedata_gis dbname=sdw password=<password> port=5432
destination_layer = the_schema.the_layer_temp

Create a Program Task in Studio to run the script

Create a program task with the following details. Adjust the location of the configuration file.

Paste code macro
Program: E:\iShareData\Utilities\DataSourceChecker\venv\Scripts\python.exe
Additional Parameters: E:\iShareData\Utilities\DataSourceChecker\feature_count.py E:\iShareData\Data\<project>\feature_count_<project>.config

Run the task and confirm the two datasets have the same number of features

Transfer the data to the 'live' location

Now we are confident that the data has been downloaded and transferred to the Spatial Data Warehouse correctly we can copy the earlier Spatial Data Transformation task and adjust the destination table so that it in the correct location for any mapsources wanting to use the data.

Create a mapsource to use the data

Now that the data is loaded into the Spatial Data Warehouse we need to create a mapsource and layer that uses the data. If any classification styling is required it is recommended to load the data into QGIS. Choose categorised styling and export the style SLD file. This can be imported into iShare. Ensure the field that is used for categorised styling is exposed as an attribute field.