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.

Thoughts on spatial data warehousing

I’ve been thinking quite a bit lately about how to store spatial data. It’s something I’ve covered here and my attitude towards this topic has evolved over the years.

The organisations I’ve worked in have mountains of spatial data accumulated over the years. The data is stored in shapefiles, geodatabases, normal databases, spreadsheets, documents, reports, photos…Why is it this way? It doesn’t have to be this way. It shouldn’t be this way!

In the course of my research for a topic for my project for next year, I’ve honed in on the methods for implementing an enterprise geoportal within an existing spatial data infrastructure. However, I feel like my focus is shifting to the data that the geoportal is trying to expose to a larger audience.

The concepts of a spatial data warehouse and a spatially enabled operational data store have been intriguing me. A regular GIS task involves comparing spatial data across a time period, analysing trends and presenting the results in a map or report. Why aren’t we storing this historical data in a SDW that’s optimised for reporting?

Non-spatial data can come from a variety of sources as well – spreadsheets, other databases etc. Another common GIS task is to spatially enable these datasets. Why are we not storing the outputs in a spatial enabled operational data store in an open format like GML?

I think it’s because to plan and implement a SDW/S-ODS takes time (and money). With a normal EDW, the organisation will not need much convincing to see the benefit of implementing one. “Spatial” is still seen as an “add-on”, or a “nice-to-have”.

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

Choosing a research topic

For the first time, the Honours class was not given a list of topics to choose from. Instead, we were instructed to choose any computing centric topic, and post a discussion about it where it could be ripped apart. Obviously, the whole point of me doing this course is to focus on the computery side of GIS, so I settled on spatial data infrastructure (SDI).

There is a lot of literature available on this topic, but most of it focusses on the implementation of SDIs within the public sector. Rightly so – implementing it correctly is expensive, and once the government has bought into the idea and made their data available, private companies should follow suit.

Even then, the whole SDI topic can be very broad, as it is made up of several components. In my current role, while I would have input into all of the components, I wouldn’t necessarily have the mandate for those components. I then narrowed it down to the component that I would be responsible for: the geoportal within the SDI. I’ll be focussing on methods and challenges involved in the implementation of a geoportal as a component of a SDI within the financial constraints of a large enterprise environment.

Enable Intellisense in pyt files in PTVS

Today I finalised my changeover to Visual Studio. I know I’ve been down this road before, but this time it’s different (probably).

This time around, I’m using Visual Studio 2015 Professional with Python Tools For Visual Studio. My shiny MSDN licence gives me access to it, along with Visual Studio Online. I mention that because after thoroughly messing up my Bitbucket account, I’ve now decided to play nice with the MS way of doing this, and using VSO for my source control.

That is not what this post is about though. Things were looking a bit down when I tried to import arcpy and VS could not resolve the import. After immediately googling the issue, I decided to look back at the editor and saw that the squiggly line was gone. Turns out VS needed a few minutes to decide that it liked ArcPy after all.

Like Joel McCune’s PyScripter workaround, I figured VS would need something as well to recognise Python toolboxes. After some clicking around, the solution was trivial: Tools > Options > Text Editor > File Extension. Type pyt into the text box and choose Python Editor from the dropdown list.

The settings take a few minutes to take effect, but it’s evident it’s working once the pyt file changes to the Python colours. Now I just have to figure out how to structure all my files within a solution…

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.

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.