Welcome to the Iamdata Solutions Asset Management Monthly Newsletter - August 2022
The GIS Officer and Asset Management
GIS and Spatial Intelligence have become commonplace in many organisations, especially local government and those with a corporate objective to maintain infrastructure assets. The GIS Officer / Spatial Analyst are undoubtedly an integral part of the organisation.
Geospatial technology is constantly growing and improving and the role of the GIS Officer has greatly changed over the last decade. It’s more about spatial analysis than making a map nowadays – which is great! Conducting spatial analysis is much more interesting than producing a map (for me, anyway!) I can admire a good map when I see one, but producing an aesthetically pleasing map was never one of my strongest skills. However, using the spatial data for spatial analysis is definitely my thing!
Spatial Analysis and location intelligence help us make better informed decisions. Using spatial analysis can help us examine many of the issues that we face in society and help provide us with some valuable insights on how we may be able to remedy some of these issues. Local Government face these types of questions all the time. We are fortunate that we have this type of spatial information, relatively speaking, at our fingertips and we can use it to help us with our planning and forecasting. For example, spatial analysis can help a Council decide where best to build a skate park or a playground that will service their residents best.
I know a Council that built an amazing playground suitable for the children and the aged residents living in and around that location. The park, although quite small, is a huge success with the residents and visitors to the area. The Council used the results of spatial analysis in their decision making, looking at their infrastructure assets such as footpaths, roads, and existing playgrounds, along with demographic information from the latest census data, bus routes, and customer surveys, etc. This analysis informed where the park needed to be built and gave them invaluable information about the people who the park will service. The information gained from this spatial analysis also informed the decisions around what infrastructure went into the park, what features were chosen for the play area, how many seats were installed and where. A good outcome, the residents are happy and Council knows it has spent the money well.
How can today's technology help the Councils and other organisations better manage their spatial data?
The GIS Officer at the Council has a big responsibility of ensuring that the spatial information relating to Council’s infrastructure assets is accurate and up to date. After all, how can we hope to manage our infrastructure assets efficiently if we don't even know where they are! We need to have an accurate representation of where our assets are located on the earth.
Build and design a Microsoft SQL Database to manage and maintain the spatial data
The design for a Microsoft SQL Server database for spatial data is very similar to a data warehouse model. I've built many SQL Server Spatial databases and I can tell you it isn't that difficult. The database does not have to be complex, and the benefits of storing your spatial data within a database opens up a whole new world of possibilities of how you can use your data for analysis and to provide your organisation with enriched, meaningful, and informative data .
Once you have your spatial data in a SQL Server database, you can easily link, merge and transform the data and easily serve it out to your organisation for consumption - and all of this hard work will be done by the SQL Server and data integration tools. Once built, the solution runs seamlessly in the background. As long as the initial setup has been built correctly, it pretty much runs along by itself. I have built data integration solutions like this for many Councils and have seen first hand the benefits this model brings.
Having said all of that - let's not forget about the data! Data quality is fundamental. We all understand that any system is only as good as the data that is going into it.
Microsoft SQL Server Business Intelligence Integration Services (SSIS)
Many organisations are already utilising Microsoft SQL Server to manage and maintain their corporate systems and data. It makes sense to leverage the in-built functionality available.
SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), and Visual Studio provides us with a suite of tools to clean, standardise and normalise data into a ‘Single Source of Truth’ within a secure SQL database, as well as providing data integration between systems and databases, while SSRS provides us with the means to easily view and make sense of the data in many different formats and using different visualisation techniques.
Benefits of this approach
Note: Microsoft are continually updating Visual Studio. There is usually a fairly substantial update every 2 years, e.g. 2017, 2019, 2022 etc. Business Intelligence Integration Services may be part of the Visual Studio download if you are using an older version or an additional download, if you are using 2019, 2022.
‘Microsoft Integration Services is a platform for building enterprise-level data integration and data transformations solutions. Use Integration Services to solve complex business problems by copying or downloading files, loading data warehouses, cleansing and mining data, and managing SQL Server objects and data.
Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.
Integration Services includes a rich set of built-in tasks and transformations, graphical tools for building packages, and the Integration Services Catalog database, where you store, run, and manage packages’.
Process
Organisations have been collecting, managing, sharing, and maintaining large amounts of data - since forever, and now, it’s relatively easy to incorporate spatial data into the mix to provide the additional benefit of location intelligence. Microsoft SQL Server handles spatial data just as easily as any other data type.
Below is an example of what a spatial data integration model may look like for your organisation:
The benefits of using SQL Server to manage your spatial data includes:
All Spatial Data are stored in the one point of truth, safe and secure within a SQL database
Dataflow Scripting provides the ability to perform complex data transformations very simply
Dataflow Tasks to easily identify the data-source and the data-destination, SQL scripts to replace current MapBasic scripts, set-up data viewer to view the data flowing through the SQL SSIS scripts – helps with checking and de-bugging.
Ability to set up Checkpoints in each step of the SSIS Workflow to help maintain consistency of the data, especially useful when managing large quantities of data.
Logging – Error Monitoring – able to record and gather information about the error to enable us to easily identify the problem and fix it.
Benefits to the Organisation
Other benefits that will be gained through implementing this model will include the on-going improvements to the attribute and spatial data. It will become easier to identify areas where attribute data and spatial data are not matching. A simple SQL script providing the relevant error information is the first step to being able to fix any issue. Automated scripts can be set-up to create email alerts that will inform us immediately if a package or transformation has failed.
It is also true, that when the organisation starts using the same ‘one-source of truth’, any errors identified can be rectified at the source, and the correction will automatically flow through to other applications. By definition, this model promotes more accurate data.
I've seen an improvement in data quality time and time again where I've implemented this.
What does Spatial Data look like in a Microsoft SQL Server Database?
Spatial data relates to the points, lines, and polygons that is a geographic representation of a feature on the Earth.
In my examples, I have chosen Line (roads, footpaths, etc), Polygon (property, buffers, etc) and Point data (defects, etc). Spatial data is stored as coordinates and topology which allows the data to be mapped. SQL Server supports geometry data and geography data. I usually use geometry. The difference being:
Geometry – Stores the X and Y coordinates that represent lines, points, or polygons.
Geography – Stores the latitude and longitude coordinates that represent lines, points or polygons.
Spatial Data
If your spatial data is already being managed and maintained within a database / geodatabase, then you are half way there. You only need to take a few more steps to link your spatial data with attribute data currently being managed within your other corporate systems, to create some very valuable spatially enabled datasets.
There are a few tools out there to help you transform and import your Spatial Data into MS SQL Server. If you are using MapInfo TAB files, then MapInfo Easy Loader is the best tool for you. http://www.pbinsight.com.au/support/product-downloads/for/easyloader
Here are some more tools:
FullConvert https://www.fullconvert.com/
Easy Loader transforms the MapInfo TAB files and imports the data into the MS SQL Server database. (Actually, it doesn’t have to be MS SQL Server, Easy Loader can transform MapInfo TAB files and import into any remote database). Easy Loader stores the spatial information present in the TAB files and when it’s been uploaded into SQL, makes it available for viewing and analysing in a GIS, like MapInfo or QGIS, etc.
Querying the data in Microsoft SQL Server database
Structured Query Language (SQL) is the most popular computer language used to create, modify and query databases. SQL is the standard language for relational database management systems and big data analytics and is used for updating, deleting, and requesting information from databases. Querying spatial data is not that much different to querying non-spatial data and is used by many organisations to manage big data solutions.
This is an example of a simple select query:
It is easy to perform powerful spatial SQL queries, such as buffer, containment, intersection, distance, nearest neighbour, routing, etc. A single spatial SQL query can achieve as much geo-processing as a couple of pages of MapBasic scripting, and queries that need to run regularly can be incorporated into Stored Procedures that are scheduled to run automatically at specific times.
Ideally, attribute data will be managed, maintained and updated in a bespoke corporate system. For example, road data will be managed in Council's Asset Management system. A unique Spatial ID will link the Road Asset attribute data within the Asset Management system to the corresponding spatial object in the Microsoft SQL Server database you have created for your spatial data. Property data will be managed within the organisation’s Property and Finance system and linked with the spatial data via the Assessment Number, or Parcel Number, or some other unique identifier that is present within both datasets.
The data need only be updated in one system. Any updated made in the Asset Management system will automatically be pulled through to populate the spatial tables.
For example, say you had collected road defects in the field. All you have is the defect type, the inspection date, and the geography. The Asset Manager wants to use this information to make some decisions about which roads to add to future capital works projects and maintenance planning.
In SQL Server Spatial database, we have tables relating to roads, property, etc. It is very easy to write a simple Stored Procedure using STBuffer, STContains, and there are dozens more spatial query types available in SQL Server, to build a more meaningful table in the database that contains all the information the Asset Manager needs. We can write scripts to calculate things like how many defects were recorded on each road segment, suburb, district, etc. And, as it is a stored procedures in the database we can set it to run as often as required. If the field staff are collecting defects every day then the stored procedure can be set to run every day - and we don't need to think about it again - it just runs along in the background updating the tables each day.
This is just one example, but if you have your data accessible in a SQL Server database, the sky's the limit!
Viewing / Editing MS SQL Server Spatial Tables
Viewing and editing your spatial data within a GIS is easy. Connect to the table via your GIS (MapInfo, QGIS, etc)
Editing Spatial Data in the GIS
You use the same process you would normally use to edit the geometry and / or the attribute data. Once you hit save, the edits are saved back to the SQL table. Easy!
SQL Server – Data Integration
Your data - including spatial data is now readily accessible to allow you to join datasets, build queries, create visualisations, and easily serve out important information across your organisation.
The datasets used for this article have been obtained through creative commons licensing
Other datasets such as the defect, hazard and attribute data was created solely for the purpose of demonstration for this article.
Look out for next months newsletter where I'll be demonstrating SQL Server database spatial queries to help you make the most of your data. September 2022 is also Iamdata Solutions 5th birthday! A big celebration!
I have worked on many different projects with my Local Government clients, from designing and developing Power BI Reports, to building SQL Server databases for spatial data, to managing and maintaining the GIS and the Asset Management systems. If you'd like to discuss how we might work together, then please email me at ➡️ jill.singleton@iamdata.solutions
If you would like to receive the latest Newsletter Blog straight to your inbox, please subscribe here: ➡️ https://www.iamdata.solutions/subscribe
You can read all our Newsletters and Blogs here:➡️ https://www.iamdata.solutions/blog
You may also be interested in our Projects Page:➡️ https://www.iamdata.solutions/past-projects
Check out what our clients say about us here:➡️ https://www.iamdata.solutions/reviews
If you would like to see a particular topic covered in these newsletters, then please let me know about it. The chances are other people will be interested and would like to hear about it too! Please email me at: ➡️ jill.singleton@iamdata.solutions with your suggestions.
Comments