Pan and zoom all data frames to match the data driven data frame in ArcMap

A colleague had a map document containing 9 data frames showing various climate scenarios. He asked me for a script to pan and zoom each data frame to the extent and scale of the data driven data frame. The data driven pages were set up beforehand and must be enabled manually.

In Line 21, the data frame containing the data driven pages index layer is stored, so that it can be excluded from the list of all the data frames in the document in Line 24. When looping over the data driven pages for export, the scale and extent of each data frame are set to match those of the main data frame before writing to jpeg.

Create feature classes from a pandas data frame

I had a large CAD drawing which I had brought into ArcGIS, converted to a feature class and classified groups of features using a 3 letter prefix. I also had an spreadsheet containing a long list of those prefixes, along with additional columns of information for that prefix, including feature class name and shape type.

I wanted to create the feature classes for dozens of these prefixes, based on the values in my converted feature class, and a template feature class for the field structure. The Select geoprocessing tool could have easily split out all the different features per prefix for me, but that would have kept the CAD feature class structure, and not the structure that I wanted.

I figured this would be a good time to get into pandas (and eventually geopandas, maybe).

In Line 28, I load the first 7 columns on the first sheet in the workbook into a pandas data frame. I set the index column to the column called “Prefix”, so those values will be used for the lookup instead of the default int index pandas assigns.

In Line 37, the prefix value from the feature class is used to look up the corresponding feature class name in the pandas data frame. Once the feature class has been successfully created, a selection layer of the matching features is appended into the new feature class. I could use a SearchCursor and store the matching features in a python list to be copied into the new feature class, but that’s something I will test at another time.

Developing an asset management GIS data maintenance methodology: Part 5 – My preparation for the way forward

(This is Part 5 of a week long series of posts on the current project I am working on, to develop a reasonable GIS data maintenance strategy for Asset Management data. Read Part 1, 2, 3, 4.)

The work I’ve done over the last few weeks (and years) is all leading to one point. A single system, with all the data topologically correct, standardised and easily accessible.

An enterprise geodatabase, with versioning enabled for the Desktop team to maintain the data without fear of conflicts. Using SDE in this manner will automatically allow for checks to be in place, for example, where I could first check the reconciled versions before moving it to default.

Archiving would be set up, and the database would be regularly backed up. Map services would be published and be made available to the non-GIS users such as the Asset team. I’d prepare the services for consumption in their app of choice: ESRI Maps for SharePoint, ArcGIS for AutoCAD, a JavaScript viewer, an Excel document with the attribute tables embedded…

The services would have the sync capability enabled, so that when they go out in the field, a Collector map could be easily configured for data capture in an offline environment. Since they visit areas which are routinely out of cell coverage, this would be ideal (and better than carrying around a printed mapbook).

While I am busy with this year’s updates, I am keeping all of this in mind. Every little bit I can do now is a little bit less that I have to do later. Once this foundation is in place, we can start looking at more advanced aspects, such as turning all this data into geometric networks, and creating custom tools which can automatically calculate the remaining useful life, asset depreciation and answer all the questions that could possibly be asked.

Developing an asset management GIS data maintenance methodology: Part 2 – Designing the workflow

(This is Part 2 of a week long series of posts on the current project I am working on, to develop a reasonable GIS data maintenance strategy for Asset Management data. Read Part 1 here.)

Yesterday, I gave a very brief overview of what it maintaining asset management data in GIS involves. Of course, the reality is much more complicated. Over the years that I have been involved with this process, my role has grown from simple data capture, to having full control of the data and the workflow needed to maintain it.

As the amount of work this year increased dramatically, I realised that I would need to document the process that I’ve had in my head. The workload would need to be shared with the Desktop GIS team. The first thing I did was to create a coherent folder structure for data storage. While we are transitioning the system, all of our data is still file-based. This already poses a challenge, as multiple people would need to access the data, and would most likely keep copies on their own devices to avoid data loss/slow access times on the network.

This is what I came up with:


and this is the script which creates that basic structure:

It is still my intention to optimise that script and integrate it into my Asset Management Python Toolbox, but I haven’t had the time do it yet. That structure may look like overkill, but it’s the way I keep my sanity, and allows me to build checks into this process. If at any point the data needs to be reverted, I can retrieve the previous version. For example, if we receive a CAD drawing, the workflow is as follows:

  1. Save the CAD file under data\recd\date_assetperson
  2. Make a copy of the CAD file under conversion\cad_gis\date
  3. Georeference the CAD file using the projection of the local municipality/study area
  4. Convert the CAD layers (normally only the polylines, sometimes the points) to feature classes in a file gdb in the same folder
  5. Project the converted feature classes to a file gdbworkspace\date
  6. Process the data further in that working gdb
  7. Final working data is pulled from all the working gdbs and sorted in current.gdb
  8. Final data for handover is pushed into Municipality.gdb

This process has already saved me time over the last few weeks, where I had to fall back on a previous version of a feature class taken from a CAD drawing due to changing requirements. This is also why I am designing this workflow in an agile way – the requirements are constantly changing, and the data is different for each municipality. I’ve had to add more folders/feature datasets since I drew this up a month ago, and I’m still ironing out the kinks in the communication with the rest of the team.

That brings me to the next aspect of this workflow: the OneNote Notebook. The Asset GIS Notebook contains an Overview section at the top level, which has the contact details of the GIS team members and Asset Management team members. It also contains a breakdown of the folder structure with detailed explanations, as well as links to relevant external documentation, such as the Prefixes spreadsheet (more about that in Part 3).

For each municipal/study area folder in the Assets folder, there is a corresponding section group in the Notebook. This section group contains a General section (technical information such as projection, major towns in the region, project costing details) as well as sections per year. The year section contains all the tasks for the current year, such as

  1. Convert data received
  2. Create mapbooks for site
  3. Generate report

etc. Some of the tasks will be quite specific, depending on the state of the data and the client requirements. There is also a Paper Trail subsection, for all email/Lync/phone correspondence with the asset team. Any answers to questions we have about the data are recorded in this section, not only to cover the GIS Team for audit purposes, but also in case a team member must pick up a task where I have left off.

Of course, it would be terrible to lose all of this hard work. In lieu of a better system, I have MacGyvered a backup system of sorts, where each day before I leave, I sync the network folder to my local OneDrive for Business folder using SyncToy, which then syncs to the cloud. It’s not ideal, but it’s better than what I had before (which was nothing. If that network drive failed…)

Although there are other team members helping with the data capture portion of the work, and who have contributed to the development of the workflow, I still retain responsibility for the process. After they have finalised their data capture, I check the data in the feature classes, assign new GIS IDs (another tool for the toolbox) and load the data into its final structure (also another tool for the toolbox).

Tomorrow in Part 3, I will talk about the kind of data we work with. It will probably be even longer than this post, and will hopefully shed some light on why designing this workflow has been challenging.

Developing an asset management GIS data maintenance methodology: Part 1 – An Overview

(This is Part 1 of a week long series of posts on the current project I am working on, to develop a reasonable GIS data maintenance strategy for Asset Management data).

I’ve been meaning to write this series of posts for a while, and now that we are in the middle of “asset management” season at work, I figured that now was as good a time as any. For the last few years, I have assisted the asset management team in my unit with GIS support. Each year, they update the civil (and sometimes electrical) infrastructure asset registers of several local municipalities. This involves unbundling upgrade projects completed in the last financial year, amongst other things.

I’m not involved in that aspect of the work, so I’m not going to try to explain what they do. On my side, this is what would normally happen:

  1. I receive data (shapefiles, spreadsheets, pdfs, CAD drawings, hard copy maps that have been drawn on)
  2. I process/convert/work some magic on the data to get it into a usable GIS format (more on that in the next post)
  3. I give back a spreadsheet containing the new shape data from the upgrades, including the GIS IDs
  4. Sometimes I’ll give a mapbook of the area, if requested.

The shape data (lengths of lines) and the unique GIS IDs per feature are integrated into the asset register, and serve as a link between the GIS and the completed register. It’s very important that this link be maintained – I’ve had to find GIS data for records on an asset register where a stakeholder at the municipality had decided to remove the GIS IDs, hence why I put “work some magic” in point number 2 in the list above.

This year is different though. We are trying to transition to an integrated system, where instead of all the data lying in random spreadsheets on the project server, and random gdbs on my server, all the data should be stored in a central location for easy access (and auditing purposes). This may seem to be an obvious approach, but there’s about 7 years worth of data which needs to be transitioned (excluding the GIS data).

It’s an enormous task, and I’ve been given control over the GIS side. That means that I get to take the data I’ve gathered over the last 3 years and push it through some sort of transformation process, while also overseeing the current tasks and carrying out some of the work which can’t be done by the Desktop team (yet). I’m also writing a Python toolbox with a set of tools to automate some of the functions, such as generating GIS IDs based on a specific convention, automating the mapbook production, and creating the correct folder structure and feature class structure per municipality.

The slideshow below is what first triggered me to write some posts on this topic:

I also saw this article, and had a subsequent conversation with the author on Twitter about it. GIS can add a great deal of value to many asset management processes (and many other fields as well, but let’s stay focussed here). I’m breaking away from my normal schedule and will be posting every day this week as follows:

  • Part 2 – Designing the workflow
  • Part 3 – Data: Processing, updating, working the magic
  • Part 4 – Data handover and the audit process
  • Part 5 – My preparation for the way forward

I’m very excited to write these posts, because I will cover everything that makes up an excellent Enterprise GIS solution: some programming stuff (more Python in my life is always better), some database stuff (SQL and file gdb), some GIS stuff (ensuring all those shapes are in the right place) and some interconnected stuff (eventually mobile apps and cloud services).

Spatially enable a column in a SQL Server table

Lately I’ve had to dig up my knowledge of SQL from university for some tasks at work, as well as the module I recently completed through UNISA.

The coordinates are in two columns, and I had to present the data back to the client as a spatially enabled view. Thanks to my colleague Grobbelaar for the code, and for showing me some cool stuff in SQL Server Management Studio (in which I am a total n00b).

The where clause will fail if the coordinates are not in the Eastern Hemisphere. I could have gone for Latitude < 0 as well, but that still leaves me in the same situation. As all of the data for this client is in South Africa, it was safe to use this expression. It's not robust though.