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.

Using a query table to represent a 1:M relationship spatially

I first discovered (and used) the Make Query Table tool during my second week at Aurecon (March 13 2012, according to my OneNote). This was about a month before I started using ModelBuilder (to combat the frustrations of ArcMap), and about 6 months before I started Python (to combat the frustrations of ModelBuilder).

I’m just giving a bit of context because this was before everything clicked into place for me. Before this point, I treated everything I learnt in my studies and during my internship as separate silos of information: GIS, Databases, Programming.

I did not even realise until after I was working at Aurecon that the query expressions used in ArcMap are SQL, despite me studying all those things. It just shows how one’s mindset can block progress, and how I allowed the awful experience of learning Computer Science in Afrikaans to stop me from letting things “click” for so long.

Back to the tool. Joins in ArcGIS are notoriously slow, and 1:M joins are not allowed (technically they are, but in the sense that an arbitrary matching feature will be joined). Naturally, the relationship between the GIS and the asset register is 1:M.

For example, a single point is used to represent the physical, spatial location of an asset, reservoir WRV-00001. In the asset register, this reservoir is unbundled into various components – storage tank, building, fence etc. Each of these assets have their own unique asset ID, but all have the same GIS ID.

I now need to represent all these assets spatially. The points/lines will all be on top of each other, but that’s fine. The Make Query Table tool does exactly this, but it is…quirky. I’ve compiled a list of things to remember when using this tool (supplemented by this question on GIS.SE):

  1. Tables/feature classes in the relationship should be stored within the same database: I tend to remember this step only after I add the inputs and the tool shouts at me.
  2. Add the feature class first in the multivalue parameter: The format of the input relies on the format of the first argument in the multivalue parameter control. The feature class should be added first to ensure that the output is a layer, otherwise it will be a table view.
  3. Enclose table names and field names in quotes: For example, I wish to join the asset register table ar to the point layer points using their common field GIS_ID. By default, the tool encloses my whole expression in quotes "points.GIS_ID" = "ar.GIS_ID". This will cause the tool to fail. Add extra quotes around the field names "points"."GIS_ID" = "ar"."GIS_ID".
  4. Choose the NO_KEY_FIELD option: Trying to add key fields causes some erratic behaviour (???). Just don’t do it. By selecting this option, the existing ObjectID field from the input will be used.
  5. The output layer will appear to have no symbology: Go into the Layer properties, click the Symbology tab, click the existing symbol, then OK, OK. It’s a bug./li>
  6. Persist to disk!: Remember to export the layer to a feature class, otherwise the layer will only exist in the map document.

Survey123: I have not been this excited in a while

Last week, I was scrolling through my Feedly and came across this post on the ESRI Australia Technical blog, one of my favourite ESRI blogs. While I am already worked up about ArcGIS Earth, it was the other app in the post that changed the course of my day (and my life).

Survey123 is an app from ESRI Labs which solves all of the issues I’ve had with mobile data capture solutions over the years. For me, it’s basically a combination of Mobenzi, GeoForms, Collector and ArcGIS Mobile.

I immediately downloaded it and did a test survey using XLSForms – another revelation to me. I can’t really say more at this stage, without testing it further, but I’ve joined the beta group on GeoNet and have already devoured the blog posts. Hopefully within the next few weeks, I will have some time to replicate one of our Mobenzi surveys in Survey123 and do a proper comparison.

CLD202x SharePoint Basics for IT Professionals: Yes please

I was planning on posting this a few weeks ago when I signed up for this course, but I’ve only gotten around to it now. I am very pleased with the course offering by Microsoft on edX, and this course is no exception.

Over 2 years ago now, when our GIS team split into Enterprise and Desktop, I was introduced to SharePoint. I had left the City just before they made switch to using SharePoint libraries as storage space instead of My Documents, so I had only dabbled with it briefly (and from an end user perspective).

During the last 2 years, I have had to interact with it in multiple ways – setting up subsites, managing permissions, creating content (such as embedding YouTube playlists, storing geometry in lists, configuring spatial viewers), and generally whatever else came up.

I never received any formal training in it, and simply clicking around in SharePoint while trying to figure it out, as I am inclined to do with other technologies, does not really work. There’s a reason this site exists. This is why I am pleased that Microsoft thought to offer this MOOC – it’s clearly needed.

The notes are very detailed, and explained very nicely in text form. This makes it very easy for me to copy into OneNote, and highlight as needed. The concepts are explained in a way that I would never have gotten from our seasoned SharePoint dev. Not that he doesn’t want to explain – he has shown me so many things on SharePoint – but it’s often the little things that one does automatically that trips up newbies.

For example, one of our clients had recently started using ArcGIS full time after only using it for very basic data capture before. He asked me why ArcMap was complaining about the projection of two feature classes being different when they were both in the same projection. I pointed out that while they were both in LO19, the one projection’s name was “LO19”, while the other one was “Transverse_Mercator_19”. It shouldn’t make a difference, but it does. The central meridian might differ by 0.00001 or something.

Another example is when one runs the Append tool in an ArcMap session, with the target table opened in the map. The tool will complete successfully, but the newly added rows won’t appear in the table even if you click away to a different table. You have to close the table, then open it again to force a refresh. Most of my ArcMap woes are solved this way – closing off whatever I’m doing and trying it again, or forcibly ending the programme. This is something I know after dealing with ArcMap for over 8 years now. It’s the GIS equivalent of

ArcGIS has a seemingly endless amount of this type of issue, and if I were teaching a total n00b how to use it, I would never even think to mention these things, because dealing with them is a reflex now. From what I can tell, SharePoint is similar, except that it probably has even more of it. Nonetheless, I’m just happy that someone has taken the time to lay out the basics in this format.

Testing out the new Collector app on Windows 10

I was very happy to see a post from ESRI appear in my feed this week about the new beta for Collector on Windows 10. It has irritated me that the only app available for Windows Phone was the ArcGIS app, which was pretty much a Collector app designed according to Modern principles anyway.

I am very pleased with this development, and am even more pleased that the app did not crash after I loaded it up on Astro. It only crashed while my colleague was attempting to navigate out of the Measure screen, but that could also just be my tablet acting up.

I will continue testing the app, hoping that related tables will appear soon, and that ESRI will turn into a universal app.

List the duplicate values in a field in an attribute table

I received some asset management data, and the state of it led me to write this script to determine if there were any values duplicated in the GIS ID field. There’s a number of ways that one can do this, but I do like to know as many methods as possible before settling on the optimised one.

In Line 18, the attribute value is stored as a key in the dictionary, along with the number of occurrences of that value. In Line 21, only the keys for which there is more than one occurrence is displayed. I wrote it as a function so that I could generate a report for all the layers in the map document.

Writing this report out to a table may be more useful, especially if there are lots of duplicates, but then one may as well run Summary Statistics.

How I roll: Georeferencing CAD datasets in ArcGIS

It’s the bane of a GIS Professional’s existence – or mine anyway. Here’s how a typical conversation would go:

CAD person: Hi, can you convert this dwg/dxf to “GIS” please?

Me: OK, should be fine. Is it coordinated?

CAD person: What?

Me: Does the drawing have a coordinate system assigned?

CAD person: I don’t know…I don’t think matters.

Me: What LO is it in?

CAD person: Oh! Why didn’t you say so? It’s LO19.

Me: OK. Send it to me, I’ll convert it to a shapefile and send you a map.

CAD person: Cool. So I can expect that in 15 minutes then, because that’s easy GIS right?

Me: …

So most of the people who send me CAD drawings simply do not know what a coordinate system is, or if they do know, why it matters. Here are the steps I follow:

  1. In Catalog, assign the alleged CS to the drawing.
  2. Drag it into ArcMap. If I get the “Inconsistent extent” message, I’ll email the CAD person to ask one of the few senior CAD people to coordinate it for them (somehow, the knowledge of how to do this is never passed on).
  3. Zoom to the extent of the drawing to see where it falls. Invariably, the x and y axes will have been flipped, so the drawing will be in the opposite hemisphere.
  4. Georeference the drawing using control points if I have erven for the study area, or approximate the position using shift, scale and rotate. Confirm with the CAD person that the drawing is now in the correct place.
  5. Look at the layers within the drawing. About 75% of the time, the CAD person would have placed all types of features (roads, pipelines, erven) on the same layer. I then have to select individual colours to create new layers. And yes, sometimes they give different features the same colour.
  6. Check that the data is not nonsensical e.g. labels have not been converted to polylines, legends and neatlines are not included in the drawing etc.
  7. 25% of the time, there will be different layers for different features. I can then export these to feature classes, populate the correct attributes, make copies as shapefiles and generate maps.

There was one time, when someone gave me a drawing that was coordinated, had the right coordinate system assigned, fell exactly in the right place, and had separate layers for each feature type. It was beautiful. The drawing had been set up correctly from the beginning, and the person had worked neatly, knowing that double work could be avoided.

Unfortunately, that happened one time. As it turns out, they tend to have the perception that GIS is quick and easy – anyone can do it! Imagine if I were to tell them that creating a drawing is easy, and could be even easier if they used ArcGIS for AutoCAD, thereby creating everything in a spatial format, readily available to all who need to work with the data in a native format without the need for conversions therefore increasing productivity? That would be the day.

What do I know after all, I’m just a GIS person.

Dilbert CAD Monkey

Yet we’re all fighting the same fight