{"type":"doc","content":[{"type":"paragraph"},{"type":"bodiedExtension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"details","parameters":{"macroParams":{"id":{"value":"SC"}},"macroMetadata":{"macroId":{"value":"6cafe45a-3537-4da1-9c5e-99f7a29c69ff"},"schemaVersion":{"value":"1"},"title":"Page Properties"}},"localId":"a2c722ed-ee73-4a47-8cc4-492af5be67f4"},"content":[{"type":"paragraph"},{"type":"table","attrs":{"layout":"default","width":579.0,"localId":"a7647fe0-4769-4b24-912d-e661eddb8eb4"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[280.0]},"content":[{"type":"paragraph","content":[{"text":"System Category","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[895.0]},"content":[{"type":"paragraph","content":[{"text":"Asset Management","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[280.0]},"content":[{"type":"paragraph","content":[{"text":"Source","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[895.0]},"content":[{"type":"paragraph","content":[{"text":"Brightly Confirm","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[280.0]},"content":[{"type":"paragraph","content":[{"text":"Available since","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[895.0]},"content":[{"type":"paragraph","content":[{"type":"status","attrs":{"color":"green","style":"subtle","text":"2024","localId":"0af4ace2-7656-43b2-a0e8-49c56011fb6f"}}]}]}]},{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[280.0]},"content":[{"type":"paragraph","content":[{"text":"Organisation","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[895.0]},"content":[{"type":"paragraph","content":[{"type":"mediaInline","attrs":{"width":241,"alt":"image-20250226-173342.png","id":"210fb5f8-8c1e-431f-a89e-1355c9dde66c","collection":"contentId-4099080193","type":"image","height":84}},{"text":" ","type":"text"}]},{"type":"paragraph"}]}]}]},{"type":"paragraph"}]},{"type":"paragraph"},{"type":"bodiedExtension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"details","parameters":{"macroParams":{"hidden":{"value":"true"},"id":{"value":"KB"}},"macroMetadata":{"macroId":{"value":"7d2a19d1-2761-41e9-90a3-0840c1ced381"},"schemaVersion":{"value":"1"},"title":"Page Properties"}},"localId":"6ad0eb59-658e-40f9-91d3-1da0ed23f1c5"},"content":[{"type":"paragraph"},{"type":"table","attrs":{"layout":"default","localId":"df8008aa-7508-4077-bb11-b5df25e76a97"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Theme","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Asset Management","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Type","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Brightly Confirm","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Available from","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph"}]}]}]},{"type":"paragraph"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Overview","type":"text","marks":[{"type":"textColor","attrs":{"color":"#000000"}}]}]},{"type":"paragraph","content":[{"text":"Brightly Confirm provides asset management software. Local authorities use Confirm to manage such assets as street furniture and any fault reporting that occurs.","type":"text"}]},{"type":"panel","attrs":{"panelType":"note"},"content":[{"type":"paragraph","content":[{"text":"Read our blog post ","type":"text"}]},{"type":"blockCard","attrs":{"url":"https://www.astuntechnology.com/blog-and-news/mastering-local-assets-integrating-an-asset-management-system-with-your-gis/"}}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"How to Integrate with Brightly Confirm","type":"text","marks":[{"type":"textColor","attrs":{"color":"#000000"}}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Confirm exports are placed on a SFTP server.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"A Python script is used to download the data.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"A standard spatial data transformation task is used to import the data into a temporary location in the Spatial Data Warehouse.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"A check is placed upon the data to ensure all features have been imported successfully.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"If the checks are passed then the import is re-run to the 'live' location in the SDW.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Confirm exports are placed on a SFTP server.","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Street Light Faults Field Definitions","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":1800.0,"localId":"30a24816-b5c7-469e-904a-80754fa48024"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Field Name","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Data Type","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Example / Notes","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Asset Id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"AB/13/E","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Central Asset Id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Unique ID in Confirm","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Job Number","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"integer","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"173243","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Job Entry Date","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"datetime (DD/MM/YYYY HH24:MI:SS) GMT","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"19/06/2022 11:37:28","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Site Name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Albert Road","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Locality Name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"N4","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Feature Location","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"S/O No 86A Florence Road","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Job Notes","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"General Street Lighting Issue - Lamp on 24/7","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Priority Name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Street Lighting - 7 days","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Status Code","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"JS33","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Status Name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Job Received by Contractor","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Asset Number","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"decimal","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"200009.01","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Feature Type Code","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"LSL","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Cms Activation Date","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"datetime (DD/MM/YYYY HH24:MI:SS) GMT","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"19/06/2022 11:37:28","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Feat Cent East","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"decimal","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"530981.73","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Feat Cent North","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"decimal","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"187613.56","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Longitude","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"51.57218637","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Latitude","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"-0.12345678","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Url","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"https://some/unique/url","type":"text","marks":[{"type":"link","attrs":{"href":"https://some/unique/url"}}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Defect Type","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Column - Day Burner","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Defect Code","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"LCDB","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"wkb_geometry","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"well-known text geometry","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"POINT (-0.09102906 51.58654038)","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Feature Group Code","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"SLCO","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Feature Group Name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"SL-Street Lighting Unit","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Feature Type Name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Street Light Column","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Waste Assets Field Definitions","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":1800.0,"localId":"6e0f1d4e-e705-4595-ba13-ae6f17a95570"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Field Name","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Definition","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Example","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Feature Type Name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Dog Bin","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Site Code","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"P421054","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Site Name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Albany Close","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Locality Name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"N15","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Ward Name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"St Anns","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Asset Number","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"7,001.00","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Feature Location","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"by L/C AC805H","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Central Asset Id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"H5400002729","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Material","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Plastic","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Manufacturer","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Unknown","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Mounting Configuration","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Floor mounted","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Asset Position On Site","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Back of Path","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Surface Base Type","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Block Paving","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Owner","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Waste Management","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Asset Id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"S1","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Survey Date","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"datetime (DD/MM/YYYY HH24:MI:SS) GMT","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"11/24/23 0:00","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Feat Cent East","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"decimal","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"532047.79","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Feat Cent North","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"decimal","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"189119.58","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Latitude","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"51.58547145","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Longitude","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"-0.09542232","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Feature Start Date","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"datetime (DD/MM/YYYY HH24:MI:SS) GMT","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"6/13/24 17:34","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Feature Type Code","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"GRIT (for icon and/or colour coding)","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"url","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"https://some/url/","type":"text","marks":[{"type":"link","attrs":{"href":"https://some/url/"}}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Feature Group Name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"HI-Grit/Salt Bins","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Feature Group Code","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"character varying","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"GRIT","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Downloading the data","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Create the python 3 virtual environment","type":"text"}]},{"type":"paragraph","content":[{"text":"Ensure Python 3 is installed.","type":"text"}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"paste-code-macro","parameters":{"macroParams":{"__bodyContent":{"value":"mkdir e:\\isharedata\\utilities\\SFTPFileDownloader\ncd e:\\isharedata\\utilities\\SFTPFileDownloader\nc:\\python3\\python.exe -m venv ./venv\nvenv\\scrpts\\activate.bat\npython -m pip install --upgrade pip\npip install pysftp"}},"macroMetadata":{"macroId":{"value":"b89c1d35-2732-41e9-8205-34da3b043d55"},"schemaVersion":{"value":"1"},"placeholder":[{"type":"icon","data":{"url":"https://cloud-code-macro.services.atlassian.com/static/images/cloud-code-macro-paste-icon.png"}}],"title":"Better Code Block"}},"localId":"6cbb556b-1f42-42c8-bf4d-23fb0f23b28c"}},{"type":"heading","attrs":{"level":4},"content":[{"text":"Create the SFTP file downloader script","type":"text"}]},{"type":"paragraph","content":[{"text":"Copy the script below and save it as E:\\iShareData\\Utilities\\SFTPFileDownloader\\sftp_file_downloader.py","type":"text"}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"paste-code-macro","parameters":{"macroParams":{"language":{"value":"python"},"__bodyContent":{"value":"import sys\nimport configparser\nimport pysftp\nimport warnings\n\nif __name__ == \"__main__\": \n warnings.filterwarnings(\"ignore\")\n\n config = configparser.ConfigParser()\n config.read(sys.argv[1])\n host = config['SFTP']['host']\n user = config['SFTP']['user']\n password = config['SFTP']['password']\n sftp_file_location = config['SFTP']['file_location']\n local_file_location = config['LOCAL']['file_location']\n\n cnopts = pysftp.CnOpts()\n cnopts.hostkeys = None\n\n with pysftp.Connection(host, username=user, password=password, cnopts=cnopts) as sftp:\n try:\n sftp.get(sftp_file_location, local_file_location)\n except:\n print(f\"Download failure: {sftp_file_location}\")"}},"macroMetadata":{"macroId":{"value":"392eac99-4963-4431-83a7-7af92b3ca8c7"},"schemaVersion":{"value":"1"},"placeholder":[{"type":"icon","data":{"url":"https://cloud-code-macro.services.atlassian.com/static/images/cloud-code-macro-paste-icon.png"}}],"title":"Better Code Block"}},"localId":"41d0e30a-3b51-46c2-b0db-19988e8be3f9"}},{"type":"heading","attrs":{"level":4},"content":[{"text":"Create the SFTP file downloader config file","type":"text"}]},{"type":"paragraph","content":[{"text":"A config file will be required for each file on the SFTP server. Adjust the following example and save under E:\\iShareData\\Data\\\\sftp_.config","type":"text"}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"paste-code-macro","parameters":{"macroParams":{"__bodyContent":{"value":"[SFTP]\nhost = sftp.some.server\nuser = some_user\npassword = some_password\nfile_location = path\\file.csv\n[LOCAL]\nfile_location = E:\\iShareData\\Data\\path\\file.csv"}},"macroMetadata":{"macroId":{"value":"a9e679ab-19bc-483a-a186-7c49a61bc1f6"},"schemaVersion":{"value":"1"},"placeholder":[{"type":"icon","data":{"url":"https://cloud-code-macro.services.atlassian.com/static/images/cloud-code-macro-paste-icon.png"}}],"title":"Better Code Block"}},"localId":"54d6a414-e5ac-405b-ba69-51253bf51c91"}},{"type":"heading","attrs":{"level":4},"content":[{"text":"Create a Program Task in Studio","type":"text"}]},{"type":"paragraph","content":[{"text":"Create a program task with the following details. Adjust the location of the configuration file.","type":"text"}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"paste-code-macro","parameters":{"macroParams":{"__bodyContent":{"value":"Program: E:\\iShareData\\Utilities\\SFTPFileDownloader\\venv\\Scripts\\python.exe\nAdditional Parameters: E:\\iShareData\\Utilities\\SFTPFileDownloader\\sftp_file_downloader.py E:\\iShareData\\Data\\\\sftp_.config"}},"macroMetadata":{"macroId":{"value":"f2da355e-533b-4f00-9578-cbcbe25204e9"},"schemaVersion":{"value":"1"},"placeholder":[{"type":"icon","data":{"url":"https://cloud-code-macro.services.atlassian.com/static/images/cloud-code-macro-paste-icon.png"}}],"title":"Better Code Block"}},"localId":"32ff9196-4117-4ca2-b5b2-f27131eea53b"}},{"type":"heading","attrs":{"level":4},"content":[{"text":"Run the task and confirm the file has downloaded","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Transfer the data to a temporary location in the Spatial Data Warehouse","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Ensure that Studio supports VRT files for Spatial Data Transformations.","type":"text"}]},{"type":"paragraph","content":[{"text":"Inspect E:\\iShareData\\LIVE\\config\\studio\\config\\ConTypes.xml and check the following FileConType is defined. If not add it.","type":"text"}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"paste-code-macro","parameters":{"macroParams":{"__bodyContent":{"value":"\n VRT File\n VRT\n Both\n VRT files (*.vrt)|*.vrt\n"}},"macroMetadata":{"macroId":{"value":"8f79d5d4-ac9a-42e8-8429-8d40a6def5a0"},"schemaVersion":{"value":"1"},"placeholder":[{"type":"icon","data":{"url":"https://cloud-code-macro.services.atlassian.com/static/images/cloud-code-macro-paste-icon.png"}}],"title":"Better Code Block"}},"localId":"5d4ade9e-e0e4-4336-8b3a-5b667e0e851c"}},{"type":"heading","attrs":{"level":4},"content":[{"text":"Create a VRT for the CSV file.","type":"text"}]},{"type":"paragraph","content":[{"text":"VRTs are a virtual format supported by OGR/GDAL. More information can be found here ","type":"text"},{"text":"https://gdal.org/en/latest/drivers/vector/vrt.html","type":"text","marks":[{"type":"link","attrs":{"href":"https://gdal.org/en/latest/drivers/vector/vrt.html"}}]},{"text":".","type":"text"}]},{"type":"paragraph","content":[{"text":"Ensure that the OGRVRTLayer name ","type":"text"},{"text":"matches","type":"text","marks":[{"type":"strong"}]},{"text":" the csv file name. It is best to use lower case and underscores and definitely no spaces for both.","type":"text"}]},{"type":"paragraph","content":[{"text":"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 ","type":"text"},{"text":"match","type":"text","marks":[{"type":"strong"}]},{"text":" the csv file name. Note that each field name has a type e.g. string or numerical.","type":"text"}]},{"type":"paragraph","content":[{"text":"The geometries are defined by the GeometryType (wkbPoint), LayerSRS (EPSG:27700) and the GeometryField elements.","type":"text"}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"paste-code-macro","parameters":{"macroParams":{"__bodyContent":{"value":"\n \n E:\\iShareData\\Data\\\\waste_assets.csv\n 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\n wkbPoint\n EPSG:27700\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n"}},"macroMetadata":{"macroId":{"value":"d184bca0-5ae7-4434-be22-a7ad73d175d1"},"schemaVersion":{"value":"1"},"placeholder":[{"type":"icon","data":{"url":"https://cloud-code-macro.services.atlassian.com/static/images/cloud-code-macro-paste-icon.png"}}],"title":"Better Code Block"}},"localId":"9e36fc30-3774-4aeb-8e31-e8ceca7537fb"}},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"paste-code-macro","parameters":{"macroParams":{"__bodyContent":{"value":"ogrinfo.exe path\\to\\file.VRT"}},"macroMetadata":{"macroId":{"value":"11e91bb5-b482-4f78-ab91-2054c0f86514"},"schemaVersion":{"value":"1"},"placeholder":[{"type":"icon","data":{"url":"https://cloud-code-macro.services.atlassian.com/static/images/cloud-code-macro-paste-icon.png"}}],"title":"Better Code Block"}},"localId":"945417e6-9b90-42f2-bf56-9ce697a018b4"}},{"type":"heading","attrs":{"level":4},"content":[{"text":"Create the Spatial Data Transformation task","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"paste-code-macro","parameters":{"macroParams":{"__bodyContent":{"value":"-t_srs \"EPSG:27700\""}},"macroMetadata":{"macroId":{"value":"df82c5ec-a30b-4c7e-b480-2fb16a9a9cc2"},"schemaVersion":{"value":"1"},"placeholder":[{"type":"icon","data":{"url":"https://cloud-code-macro.services.atlassian.com/static/images/cloud-code-macro-paste-icon.png"}}],"title":"Better Code Block"}},"localId":"c06db602-388c-4afc-b608-b0c7650be063"}},{"type":"paragraph","content":[{"text":"Run the task and confirm the data has been loaded to the destination specified by using QGIS or pgAdmin.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Check that the data has been transferred correctly","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Ensure .net 4.8 has been installed","type":"text"}]},{"type":"paragraph","content":[{"text":"Install .net 4.8 from ","type":"text"},{"type":"inlineCard","attrs":{"url":"https://dotnet.microsoft.com/en-us/download/dotnet-framework/net48"}},{"text":" ","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Install Microsoft C++ Build Tools","type":"text"}]},{"type":"paragraph","content":[{"text":"Do this from ","type":"text"},{"type":"inlineCard","attrs":{"url":"https://visualstudio.microsoft.com/visual-cpp-build-tools/"}},{"text":". Run install and Check ‘Desktop development with C++’. Check that the latest MSVC and Windows 10 SDK components are selected.","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Create the Python 3 environment","type":"text"}]},{"type":"codeBlock","content":[{"text":"mkdir e:\\isharedata\\utilities\\DataSourceChecker\ncd e:\\isharedata\\utilities\\DataSourceChecker\nc:\\python3\\python.exe -m venv ./venv","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Check environment variables","type":"text"}]},{"type":"codeBlock","content":[{"text":"set","type":"text"}]},{"type":"paragraph","content":[{"text":"Check that PATH doesn’t contain c:\\python27 or OGR paths.","type":"text"}]},{"type":"paragraph","content":[{"text":"Check that GDAL_DATA is present. If not add it to the system environment variables. It should equal D:\\Astun\\Tools\\OGR\\gdal-data but confirm this using Windows Explorer.","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Copy GDAL whl file","type":"text"}]},{"type":"paragraph","content":[{"text":"Download ","type":"text"},{"text":"https://github.com/cgohlke/geospatial-wheels/releases/download/v2024.9.22/GDAL-3.9.2-cp39-cp39-win_amd64.whl","type":"text","marks":[{"type":"link","attrs":{"href":"https://github.com/cgohlke/geospatial-wheels/releases/download/v2024.9.22/GDAL-3.9.2-cp39-cp39-win_amd64.whl"}}]},{"text":" and copy to e:\\isharedata\\utilities\\DataSourceChecker.","type":"text"}]},{"type":"codeBlock","content":[{"text":"pip install GDAL-3.9.2-cp39-cp39-win_amd64.whl\npip install numpy","type":"text"}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Create the feature_count.py script","type":"text"}]},{"type":"paragraph","content":[{"text":"Store the following as E:\\iShareData\\Utilities\\DataSourceChecker\\feature_count.py.","type":"text"}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"paste-code-macro","parameters":{"macroParams":{"language":{"value":"python"},"__bodyContent":{"value":"import sys\nimport configparser\nfrom osgeo import ogr\n\ndef count_features_in_ogr(ogr_driver, ogr_connection, ogr_layer):\n # Register the VRT driver\n ogr.RegisterAll()\n ogr.DontUseExceptions()\n\n # Open the datasource\n if ogr_driver == 'OGR_VRT':\n datasource = ogr.Open(ogr_connection)\n elif ogr_driver == 'PostgreSQL':\n datasource = ogr.Open(ogr_connection)\n else:\n driver = ogr.GetDriverByName(ogr_driver)\n datasource = driver.Open(ogr_connection, 0)\n\n if datasource is None:\n print(f\"Failed to open {ogr_connection}\")\n return 0\n\n layerList = []\n for i in datasource:\n daLayer = i.GetName()\n if daLayer == ogr_layer:\n feature_count = i.GetFeatureCount()\n\n return feature_count\n\ndef list_ogr_drivers():\n cnt = ogr.GetDriverCount()\n formatsList = [] # Empty List\n\n for i in range(cnt):\n driver = ogr.GetDriver(i)\n driverName = driver.GetName()\n if not driverName in formatsList:\n formatsList.append(driverName)\n\n formatsList.sort() # Sorting the messy list of ogr drivers\n\n for i in formatsList:\n print(i)\n return 0\n\nif __name__ == \"__main__\": \n config = configparser.ConfigParser()\n config.read(sys.argv[1])\n source_driver = config['SOURCE']['source_driver']\n source_connection = config['SOURCE']['source_connection']\n source_layer = config['SOURCE']['source_layer']\n source_feature_count = count_features_in_ogr(source_driver,source_connection,source_layer)\n destination_driver = config['DESTINATION']['destination_driver']\n destination_connection = config['DESTINATION']['destination_connection']\n destination_layer = config['DESTINATION']['destination_layer']\n destination_feature_count = count_features_in_ogr(destination_driver,destination_connection,destination_layer)\n if source_feature_count != destination_feature_count:\n print(f\"feature counts do not match\")\n\n"}},"macroMetadata":{"macroId":{"value":"cd474964-0d3f-4207-9b7e-0a159d72545e"},"schemaVersion":{"value":"1"},"placeholder":[{"type":"icon","data":{"url":"https://cloud-code-macro.services.atlassian.com/static/images/cloud-code-macro-paste-icon.png"}}],"title":"Better Code Block"}},"localId":"2158a7f8-79a3-4e20-b2ae-4c29bb67cc73"}},{"type":"paragraph","content":[{"text":"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\\\\feature_count_.config.","type":"text"}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"paste-code-macro","parameters":{"macroParams":{"__bodyContent":{"value":"[SOURCE]\nsource_driver = OGR_VRT\nsource_connection = E:\\iShareData\\Data\\path\\file.vrt\nsource_layer = the_layer\n\n[DESTINATION]\ndestination_driver = PostgreSQL\ndestination_connection = PG: host= user=isharedata_gis dbname=sdw password= port=5432\ndestination_layer = the_schema.the_layer_temp"}},"macroMetadata":{"macroId":{"value":"a0ea0bed-df03-42a2-860b-f8de74de20a7"},"schemaVersion":{"value":"1"},"placeholder":[{"type":"icon","data":{"url":"https://cloud-code-macro.services.atlassian.com/static/images/cloud-code-macro-paste-icon.png"}}],"title":"Better Code Block"}},"localId":"c19007e0-7805-46cb-b2a7-8d7b2d9e6b40"}},{"type":"heading","attrs":{"level":4},"content":[{"text":"Create a Program Task in Studio to run the script","type":"text"}]},{"type":"paragraph","content":[{"text":"Create a program task with the following details. Adjust the location of the configuration file.","type":"text"}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"paste-code-macro","parameters":{"macroParams":{"__bodyContent":{"value":"Program: E:\\iShareData\\Utilities\\DataSourceChecker\\venv\\Scripts\\python.exe\nAdditional Parameters: E:\\iShareData\\Utilities\\DataSourceChecker\\feature_count.py E:\\iShareData\\Data\\\\feature_count_.config"}},"macroMetadata":{"macroId":{"value":"dbdc92c1-6d8e-44df-aa73-5527f2557ba9"},"schemaVersion":{"value":"1"},"placeholder":[{"type":"icon","data":{"url":"https://cloud-code-macro.services.atlassian.com/static/images/cloud-code-macro-paste-icon.png"}}],"title":"Better Code Block"}},"localId":"239b281c-01db-4d32-ab44-2779e39a597c"}},{"type":"heading","attrs":{"level":4},"content":[{"text":"Run the task and confirm the two datasets have the same number of features","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Transfer the data to the 'live' location","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Create a mapsource to use the data","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"paragraph"},{"type":"paragraph"}],"version":1}

Browser not supported