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

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).

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.

INF3707 Advanced Database Development :: A strategy.

I’ve already mentioned previously that I am taking two modules this semester as part of my prep for Hons. I decided to tackle the assignments for this module first because while I am familiar with SQL Server and T-SQL, I have never delved into the Oracle side of things. Unless one counts Java, and since I’ve long since repressed the the three years I spent with it, I don’t.

Right off the bat, this module appears quirky. We’re using Oracle Express 10g, which a quick Google search and multiple forum posts on myUnisa confirm is a long outdated version of Oracle’s free database version. Also, it’s only available in 32-bit, so it absolutely refused to install on my 64-bit Windows 8.1 VM.

I tried the workarounds Google suggested, such as running in compatibility mode for XP SP3 as admin, and editing some registry key after installation. Nothing happened when I tried the first option, thereby rendering the second option moot.

After following some frustrated ramblings on myUnisa, I just installed the 11g version with no problems. Once I had it installed, and unlocked the default user and tables, I was ready to do the assignment.

The structure of SQL in Oracle is a little bit different to T-SQL, but thankfully not too much. We’re using the actual Oracle University manual as a textbook, so luckily the notes are quite detailed and specific to Oracle. The first assignment did give a taste of what the exam would be like though, and since that counts 80% of the module mark, I’m aiming to complete the written assignment next week so that I can start studying for the exam. Which is 3 months away, yes, but my paranoia is at an all-time high.

It’s also not good enough to just pass the module (or in my case, to get that 60%). I have to go all in, otherwise what is the point? To end this post, I will leave a quote from a thread on the forum:

Written by a guy, if that wasn’t obvious from the dripping sarcasm.

 

DAT201x Querying with Transact-SQL: A timely MOOC

I mentioned a while ago that while I was chuffed that Microsoft has started offering MOOCs on edX, I was not at all pleased about the peer assessment requirement in DEV203x.

Fortunately, another MOOC caught my eye: DAT201x Querying with Transact-SQL. The timing could not be better – I’m currently preparing for my first university exam in 5 years, so I’m trying to get back into that frame of mind. The exam is focussing more on the theory of database administration, best practice etc (so pure study work – my favourite!), but one of the assignments included a question where the answer was to write a query in T-SQL.

While I did that in undergrad, things have gotten understandably hazy over the years. Coincidentally, I had to modify some queries at work on one our SQL Server databases recently, so after not doing anything pure SQL related in 5 years (besides the SQL stuff in ArcGIS), in the same month I get hands-on experience at work, plus this MOOC to refresh my memory. Just in time for the exam in 3 weeks. Yay!

Also, no peer assessments. Just pure learning.