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.


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.

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 random points in a polygon

I needed to create random points inside polygons for some testing I needed to do. Since I only had a Standard Licence available (and therefore no access to the Create Random Points tool that I used to POST random points as ESRI JSON to a REST endpoint), I decided it was as good a time as any to write a tool in Python.

After creating an InsertCursor on the point feature class, I iterate over the polygon feature class using a SearchCursor. In Line 25, a random number is chosen between 1 and 10 to determine the number of points that will be created for that feature. The extent of the current feature is stored in Line 26.

The actual points are created in Lines 30 – 34. For each point, a random floating point number within the polygon’s extent is chosen as the x and y coordinates. A point feature is created using the coordinates to build the geometry, as well as the name of the polygon feature. The point feature is then inserted into the point feature class.

Convert spreadsheets to GDB tables and display XY

I’m sick today, so I decided to choose one of the simpler scripts for today’s post. This one automates a common task of the GIS professional: converting a spreadsheet of coordinates to GIS. The script assumes you’ve formatted each sheet appropriately.

For each sheet in the workbook, the script creates a file geodatabase table, and displays the coordinates as a layer in ArcMap using the WGS 1984 spatial reference. The script is very basic, and therefore will be easy to change to suit your own data.

Export selected data driven pages only

A common task I perform with the asset management data is to create mapbooks per service (Water, Electricity etc) per main town/area/grid. I normally do this at the start of the project, to display the data we currently have. The asset guys then take the maps to the client, who will mark up amendments and additions.

While I cringe at having to create hard copies of anything, it’s the quickest way to get the information out of the client. It’s also relatively easy for me to create the mapbooks – I just have to open my service templates, save a new copy, set the data sources and CS to my current area, and export. I could automate this part of the process also, but often I need to visually confirm that the “current” data I am using is actually “current”.

When I set up the data driven pages for an area, I switch on all the asset data layers I have, and see which towns they are grouped into. I create best-fit rectangles around them, either by eyeballing it and drawing it in graphics, or using the Strip Map Index Features tool. Often, some areas won’t have Electricity data, or the Water Supply data only covers 3 out of the 5 grid blocks which covers the town.

Before, I would manually check this, and create separate index layers for each service, which would be used for that service’s data driven pages. This was a terrible way to do things, but doing it that way is what made me determined to find a better way. To Python!

This script takes a folder containing the various service MXDs, which have been set up so that the operational data is in a group layer called Data. Each of these layers is intersected with the data driven pages index layer, to determine which DDP polys contain the operational data. In Line 35, I’ve specified the selection_type as ADD_TO_SELECTION, so as not to lose the previously selected pages.

In Line 39, instead of making the traditional call to arcpy.mapping.ExportToPDF, I used dataDrivenPages.exportToPDF instead. The funny thing about the ArcGIS documentation is that I can look at the same pages so many times, and still discover new things.

What I like about this method is that it allows a SELECTED page range to be exported. This allows me to only have one index layer for an area 🙂 At the end of the script, I put some code in to convert the entire map to a KMZ file. That portion currently does not work, because the imagery basemap needs to be removed from the mxd before it can be converted. This should be as simple as calling RemoveLayer on the basemap, but I always have fantastic problems working with the layer movement methods (insert, add, move etc).

Edit: I just saw that I actually posted this script in July as well, when I wrote it initially. I was just so excited.