Categorising spatially enabled dashboards

Lately I’ve been working with dashboards quite a bit. For clients who don’t really understand spatial data, it’s easier for them to digest the information on the map when it’s presented along with the graphs and indicators they are familiar with from Excel or Power BI.

Over the last few weeks, Julian has spent quite a bit of time setting up a number of dashboards using Operations Dashboard, each with a different purpose. On one project, we have a dashboard showing the client the real-time progress of fieldworkers on a map, along with some graphs showing the breakdown of assignments which are in progress and completed per district. It enables the client to answer questions such as which worker is causing a bottleneck. This dashboard consumes the workers and assignments layer from the Workforce for ArcGIS project, along with the various Survey123 feature services.

On another project, we have a dashboard showing the results of an asset life cycle cost analysis model. This dashboard includes graphs depicting when the client can expect to incur the greatest cost to replace key assets, as well as helping to answer questions such as: Is it cheaper to replace an asset in 5 years, or to spend an additional amount on maintenance in 3 years in order to extend the remaining useful life of the asset by 7 years?

We also have a number of ideas in dev at the moment, including the actual software we use to display the dashboard (that will have to be a post by itself). I’ve been mulling over how to package these different dashboard types as solutions to offer to a client. I decided to adapt the traditional categories to our purpose.

• Operational: This is the basic dashboard, as detailed in my first example. This type will normally display two maps – one showing real-time progress of fieldworkers and their assignments, and another showing the surveys they submit along with actual data. Graphs may include the amount of assignments completed per worker, per area or along whichever dimension is most logical (or whatever the client prefers). Filters are included to drill down through the live data.
• Analytical: This dashboard shows the results of analysing the data displayed on an operational dashboard (my second example). A single map can be used to display the analysis results per survey or per area. Graphs will vary according to client needs, but will be based on the survey points in the map. The user can interact with the dashboard by drawing various reports that they need, creating pivot tables, filtering etc.

My current dev efforts are focussed on a third type of dashboard. For a large project last year, I designed and implemented a mobile data capture solution which incorporated a QA process (to be carried out by professional engineers) as well as an invoicing process (to reduce turnaround time between carrying out the work and getting paid by the client). I’ll have to use another post to brainstorm that idea.

Editing assignment types in Workforce for ArcGIS

A Workforce for ArcGIS project is based on a predefined schema. This schema states that assignment types are populated into an associated coded value domain on the Assignments feature layer when created.

Sometimes assignment types need to be modified, perhaps because of a typo. On the Workforce front end, it is impossible to edit an assignment type. It can only be deleted (if the type is not being used by an assignment).

One can work around this by opening the Workforce project in ArcGIS Online, open the Assignment layer properties and directly edit the coded value domain.

How to convince someone to move from paper-based forms to electronic surveys

I spent March of 2018 working closely with a bridge engineer. For years, they had been using four paper questionnaires to capture inventory and inspection data of structures such as bridges, culverts, gantries etc. They would arrive at a structure, select the appropriate form, measure and write everything down, take a load of photos, draw a sketch or two and move on to the next one.

When they got back to the office, they would get a few students to manually input the captured data into an electronic form, which would store it in a database on their local machine. The students would also need to manually link photos to the correct structure. As with most projects, time would always run out, so the engineers would also need to help out with this electronic transfer process.

Once the screaming in my head subsided, I asked him, “Why? Why is it being done this way? It’s 2018. The process you are describing should have been phasing out at least 5 years ago already.” He didn’t really have a straight answer for me, beyond “this is the way it’s always been done”.

Over the next few weeks, I showed him that there is a better way. I used the first week to convert the largest of the four forms using Survey123. The form was fairly complex – with no access to a table layout or even a grid theme, I had to make a number of design decisions which wouldn’t impact the user experience too much while still retaining (and even enhancing) the functionality available in the paper form.

After my initial stab at it, we spent 2 more weeks going back and forth, fine-tuning choice lists, removing unnecessary questions, changing section groups, enforcing relevant fields, choosing repeats and optimising calculations. We ended up with an xls of 250 rows of questions and 35 choice lists. I was fortunate to have access to one of the databases they’d used on a previous project, so I was able to extract the choices from the lookup tables using SQL and Python.

I spent the last week of March replicating the other three forms. They were similar enough that I could copy and paste much of what I had implemented on the first form, but different enough that I couldn’t keep everything in one form. Once that was completed, I published all the surveys and gave it to him to thoroughly test (I believe my exact words were “Try to break it”).

By the first week of April, I had fixed most of the bugs and we were ready to train the students on it in the field. I set up a web app allowing him to view the surveys as they were submitted. He could immediately send a message to the students in the WhatsApp group I set up on their tablets if they were measuring components incorrectly, or not describing items properly. He was fully converted.

In my next post, I’ll detail how I took everything down and rebuilt it from its ashes by adding Workforce for ArcGIS and Operations Dashboard to create a more efficient system.

The grid theme is finally available in Survey123

I got a lovely surprise when I opened my emails on Friday – ESRI has finally gotten around to implementing a beta version of the grid theme in Survey123.

I know I’ve been quiet on the blog for a while, but that’s because I’ve been keeping my rants about GIS offline (mostly). In particular, my utter frustration at not being able to replicate a very critical paper-based form digitally because of the lack of a grid theme.

I even went as far as chucking Survey123 completely, setting up ODK Collect from scratch and trying out the grid theme there. I was not happy with the results at all, so I resigned myself to a compromise: replicating the forms in Excel and extracting the data automatically from the spreadsheets as the team uploads them to OneDrive in the field. Terrible, I know, but still better than paper.

Since ESRI is making this tentatively worded claim in the beta forum:

It’s now possible to design a digital smart form that closely resembles its paper form predecessor!

I will be putting it to the test today. It’s still not the holy grail though, which would be a proper table theme, but it at least takes us a step closer.

Survey123 support added to the ArcGIS API for Python

I don’t get excited for much these days, which is why I found my reaction to point 3 of the release notes for the 1.5.1 update so odd:

• Added support for Survey123 to the apps module

Maybe it’s because I’ve spent the last 8 months in a constant state of frustration, integrating and automating processes using Survey123, Workforce and AGOL through the API. It could be relief that I’m feeling as well. Perhaps there is light at the end of this tunnel.

Changing the worker basemap in Workforce

I’ve recently implemented Workforce for ArcGIS on a big project. It’s great being able to automatically assign sites to fieldworkers, and update their to-do lists on the fly.

However, the lack of reference data for the fieldworkers was bothering me. In the Workforce mobile app, the map displays the default topographic basemap and the location and status of their assignments. We were sending fieldworkers into areas where they would have to be a bit more vigilant of their surroundings, as well as ensure that fieldworkers did not drive through areas which were deemed as “High risk” due to crime or environmental conditions.

I modified the worker basemap of the project to include a polygon layer containing these “High risk” areas, so workers could always be aware when they were near one of these areas. I also tried changing the basemap to OpenStreetMap, but the Workforce app did not like that at all. The app crashed multiple times before I figured out it only wanted to render the default map. The dispatcher map had no issue with changing the basemap.

I also managed to overlay our own reference road network over the basemap, so the fieldworkers were aware of which direction to face when capturing a survey. This was included so that required photos were captured consistently.

Coded values display in AGOL instead of their descriptions

I’ve been quite busy over the last few months. I’ve fully switched over to ArcGIS Pro, and I’ve been spending most of my time developing a fieldwork workflow using Workforce, Survey123, Collector and ArcGIS Online.  I also (finally) forced myself to dabble with the ArcGIS Python API, which has fallen neatly into place alongside my normal fiddling with ArcPy.

I thought I’d write this post quickly just to document something I’ve noticed when publishing a feature layer symbolised using unique values from a coded value domain to ArcGIS Online. Despite the descriptions displaying correctly in Pro, if one edits the symbols and then does not delete all the feature templates and recreate them, editing them in an online web map will result in the codes being shown on the edit dialog, and not the descriptions.

The way around this is to, well, delete the feature templates and recreate them before publishing. I’ve had this happen a few times, where everything looks great online, and then someone tries to edit it in Collector using the codes.

Overcoming the Make Query Table bug in ArcGIS

According to my notes, I first used the Make Query Table tool in my first week at Aurecon, back in March 2012. It was the first of many, many times, because often when receiving spatial data in a non-spatial format from a non-GIS user, the first thing that gets thrown out is any trace of the original spatial component.

At some point, I realised the tool’s expression parameter was a bit wonky. As I have come up against this problem every few months since (forgetting it happens each time because I only thought to write down a note about it now), I have decided to immortalise it in a gist below.

http://desktop.arcgis.com/en/arcmap/10.3/tools/data-management-toolbox/make-query-table.htm

When inputting the optional SQL clause, ArcGIS automatically adds quotation marks “” to the field names in the dialog box. This will
pass the tool’s error checking successfully but will cause the tool to fail with an error.

If you verify the SQL clause in the dialog box, it will give a SQL error with no specifics. When adding the clause, remember to remove
the quotation marks.

e.g. If you want to join Layer1 to Layer 2 on common field ID and where Layer 1 contains “Cape Town”, ArcGIS will format your expression
in the following way:

"Layer1.ID" = "Layer2.ID" AND "Layer1.TOWN" = 'Cape Town'

You need to change it to

Layer1.ID = Layer2.ID AND Layer1.TOWN = 'Cape Town'

view raw

makequerytablebug.md

hosted with ❤ by GitHub

Thoughts on spatial data warehousing

I’ve been thinking quite a bit lately about how to store spatial data. It’s something I’ve covered here and my attitude towards this topic has evolved over the years.

The organisations I’ve worked in have mountains of spatial data accumulated over the years. The data is stored in shapefiles, geodatabases, normal databases, spreadsheets, documents, reports, photos…Why is it this way? It doesn’t have to be this way. It shouldn’t be this way!

In the course of my research for a topic for my project for next year, I’ve honed in on the methods for implementing an enterprise geoportal within an existing spatial data infrastructure. However, I feel like my focus is shifting to the data that the geoportal is trying to expose to a larger audience.

The concepts of a spatial data warehouse and a spatially enabled operational data store have been intriguing me. A regular GIS task involves comparing spatial data across a time period, analysing trends and presenting the results in a map or report. Why aren’t we storing this historical data in a SDW that’s optimised for reporting?

Non-spatial data can come from a variety of sources as well – spreadsheets, other databases etc. Another common GIS task is to spatially enable these datasets. Why are we not storing the outputs in a spatial enabled operational data store in an open format like GML?

I think it’s because to plan and implement a SDW/S-ODS takes time (and money). With a normal EDW, the organisation will not need much convincing to see the benefit of implementing one. “Spatial” is still seen as an “add-on”, or a “nice-to-have”.

Database access via Python

In my ongoing quest to do absolutely everything through Python, I’ve been looking a lot lately at manipulating databases. I’ve been using arcpy to access GIS databases for years, and last year I finally got around to using pyodbc (and pypyodbc) for accessing SQL Server databases.

Now that I’m in an Oracle environment, Oracle has provided the cx_Oracle library to directly connect to databases. I have yet to test that though. What I’m interested in at the moment is creating and accessing databases for personal use.

I considered MongoDB for a while, but I don’t think I want to go NoSQL yet. This is why I have been experimenting with SQLite (through the sqlite3 library), as it is included in the Python install, and has the delightful SpatiaLite extension. The slogan goes against my one of my mottos (Spatial is Special) while supporting my other motto (Everything is Spatial).