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.

Convert a list of field names and aliases from Excel to table using ArcPy

I went digging through my old workspace and started looking at some of my old scripts. My style of coding back then is almost embarrassing now ­čÖé but that’s just the process of learning. I decided to post this script I wrote just before ArcGIS released their Excel toolset in 10.2.

From what I can recall, I needed to create a file geodatabase table to store records of microbial sample data. Many of the field names were the chemical compound themselves, such as phosporus or nitrogen, or bacterial names. For brevity’s sake, I had to use the shortest field names possible while still retaining the full meaning.

I set up a spreadsheet containing the full list of field names in column FIELD_NAMES and their aliases in ALIAS. I created an empty table in a file gdb, and used a SearchCursor on the spreadsheet to create the fields and fill in their aliases.

This solution worked for me at the time, but of course there are now better ways to do this.

Reverse geocode spreadsheet coordinates using geocoder and pandas

I had a spreadsheet of coordinates, along with their addresses. The addresses were either inaccurate or missing. Without access to an ArcGIS licence, and knowing the addresses were not available on our enterprise geocoding service, I sought to find a quicker (and open-source) way.

I used the geocoder library to do this. I used it previously when I still had an ArcGIS Online account and a Bing key to check geocoding accuracy amongst the three providers.

Since I don’t have those luxuries anymore, I used pandas to read in the spreadsheet and reverse geocode the coordinates found in the the third and fourth columns. I then added a new column to the data frame to contain the returned address, and copied the data frame to a new spreadsheet.

Filter a pandas data frame using a mask

After using pandas for quite some time now, I started to question if I was really using it effectively. After two MOOCs in R about 2 or 3 years ago, I realised that because my GIS work wasn’t in analysis, I would not be able to use it properly.

Similarly, because pandas is essentially the R of Python, I thought I wouldn’t be able to use all the features it had to offer. As it stands, I’m still hovering around in the data munging side of pandas.

I used a pandas mask to filter a spreadsheet (or csv) based on some value. I originally used this to filter out which feature classes need to be created from a list of dozens of templates, but I’ve also used it to filter transactions in the money tracking app I made for my household.

Add a new field to a feature class using NumPy

I needed to add a field to dozens of layers. Some of the layers contained the field already, some of them contained a similar field, and some of them did not have the field. I did not want to batch Add Field, because not only would it fail on the layers which already had the field, but it is super slow and I would then still have to transfer the existing values from the old field to the new field.

I wrote the tool in the ArcMap Python window, because I found it easier to load my layers into an mxd first as they were lying all over the place. The new field to be added is set up as a NumPy array, with the relevant dtype.

The script loops over all the layers in the document, adding the field via the Extend Table tool, and then transferring the values from the old field to the new field. Deleting the old field at the end would be an appropriate step to include, but I didn’t, purely because I’ve lost data that way before.

Split line interactively using ArcPy FeatureSets

Back when I was doing a lot of GIS work for civil and electrical asset management, a common task I had was to ensure that all civil structures represented as a polyline (reticulation and bulk pipelines, eletrical feeder cables, road surfaces etc) were split at the physical road intersections as seen on the latest available aerial imagery, as well as where they crossed each other.

This task wasn’t too much trouble if the lines were already drawn across each other, as more often than not, the lines crossed at the road intersections. The result was usually achieved by using the Planarize option on the Advanced Editing toolbar. However, it became a problem for new areas which had to be digitised by hand (no CAD drawings or shapefiles received), or lines which had to be corrected.

I would have to visually check on the imagery where the road intersections were, and then manually split each polyline. I ran through some ideas, including digitising all road intersections as points (which would be good data to have anyway), and then splitting all the lines at the closest intersection by using the Split Line at Point tool.

I did however want to create a method of splitting the line multiple times on the fly, instead of just splitting it once (which is the option provided by the Split button on the Editor toolbar). I wanted to be able to select a line, click as many points along the line as I wanted, and then split the line into those points.

The tool iterates over all the points created by the user in the FeatureSet. For each point, a temporary diagonal is created to cut the original polyline. This new part is saved in a list and the tool runs again on the original polyline (minus the new part). Once all the points have been processed, the original polyline is removed from the feature class, and the new parts are inserted. All feature attributes are maintained.

My intention with this tool was to expand it with more features, fix the bugs (it would randomly not work) and upload it as a toolbox. However, the need to do that fell away. I also realised I was starting to get into a space where I was forcing as much code as I could into my work in order to get through the day.