...
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 a customer's Brightly Confirm installation.
...
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 |
...
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 | |
Feature Group Name | character varying | HI-Grit/Salt Bins |
Feature Group Code | character varying | GRIT |
...
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.