Geoprocessing history logging in ArcGIS: performance review

If you have used geoprocessing (further GP) tools in the ArcGIS framework, you are probably familiar with the concept of GP history logging. Essentially, all kinds of GP that you perform using ArcToolbox GP tools are written on the disk in a special folder. You probably already know that you can access the results of your GP tools execution (which are saved only within the map document); they are accessible in the Results window in ArcMap. However, this information is also written to the disk with all sorts of execution metadata including tool’s name, its parameters, the output information, and the environment settings. Please review this Help page – Viewing tool execution history – to learn more.

When working in ArcMap session, whether the GP history will be logged is determined by the GP settings (Geoprocessing menu > Geoprocessing Options). Yet after disabling this setting you may not see the performance boost as it doesn’t cost too much to write some data on the disk – running a GP tool 100 times in isolation produces a log file of about 1MB.

As to Esri docs, however, it might be worth noting that:

Logging can affect tool performance, especially in models that use iterators. For example, you may have a model that runs the Append tool thousands of times, appending features to the same feature class. If logging is enabled, each iteration in the model updates the metadata of the feature class, which slows down the performance of the model.

So, if your models or scripts might execute GP tools many thousands times, this will affect the performance as this information will have to be collected internally by the software and then written on the disk. In a word, it might be worth disabling this option if you don’t need to preserve any metadata of your operations. This might be particularly helpful when authoring large arcpy based scripts where GP tools are run a lot of times.

Keep in mind that according to the Esri docs,

for script tools and stand-alone scripts (scripts run outside of an ArcGIS application—from the operating system prompt, for example), history logging is enabled by default.

Another thing that is good to know is when exactly the writing occurs:

A session is defined by all the work performed from the time you open the application to the time you exit.

So when you run your Python script, the file is created named to the date and time when you’ve started running your script, but the actual data is written only when the script has finished running.

Luckily, there is an arcpy function that lets you disable geoprocessing history logging, arcpy.SetLogHistory(False).

Another thing is that the GP history is also written within the geodatabase itself regardless whether it’s a file based geodatabase or an enterprise geodatabase (ArcSDE). Fortunately, it’s the same setting that controls whether it’s being written – arcpy.SetLogHistory().

When the data you process is stored within a file based geodatabase, the geoprocessing history is being saved within the metadata stored for associated geodatabase object, such as a feature class. Right-clicking the feature class and choosing Item Description won’t let you see the GP history. This is because the standard ArcGIS Item Description style of the metadata view gives you only a simple outlook. In order to view the GP history, go to the Customize > ArcMap Options window > Metadata tab and choose for Metadata Style some other style such as FGDC. Then when right-clicking the feature class and choosing the Item Description, you will be able to see all the GP tools that were run on this feature class under the Geoprocessing History section.

As to practical numbers, I’ve created an empty file geodatabase, loaded into a polygon feature class with a dozen of polys and then run the Calculate Field GP tool 1,000 times calculating the same field over and over again. I’ve run this loop multiple times and have seen the stable increase of the file geodatabase in size with 300KB every 1,000 GP tool executions.

When processing data stored within an enterprise geodatabase (with the help of any DBMS supported) that is sometimes referred to as an ArcSDE geodatabase, keep in mind that when running any kind of GP tools on a geodatabase object such as a table or a feature class, the metadata of the tool execution is also being written as XML into the GDB_Items database table within the Documentation column (of XML type). This XML metadata can get fairly large as it contains the information about the tool name, input and output parameters, and some more. Single execution of a GP tool will add one line of XML; so again, if you run a lot of the GP tools on your feature class, the XML metadata stored can get very large and the queries to this database table will take longer to process because it will take more and more time to write/read the XML data. To give you a feeling of the size increase, running the Calculate Field GP tool 1,000 times made the GDB_Items table 500KB larger (I am on SQL Server and I’ve run the exec sp_spaceused 'sde.GDB_Items'). I’ve run this loop multiple times and have seen roughly the same increase in size.

This logging of the feature class metadata can also be easily disabled either in ArcMap by switching off this option in the Geoprocessing Options window or by using the arcpy.SetLogHistory(False) function when running your Python scripts. Esri has a KB HowTo: Automate the process of deleting geoprocessing history that can help you automate cleaning up the GP history metadata from your feature classes. The basic workflow is to export the metadata excluding the GP history and then import it back. This is the only workflow I can think of if you are using a file based geodatabase (apart from re-creating the feature class which will also drop the GP history metadata). With an ArcSDE geodatabase, you can use SQL to clean up the GDB_Items table deleting the content of the Documentation column for the chosen feature classes. You would need to parse the XML to clean up the metadata/Esri/DataProperties/lineage/Process resource as you might want to preserve other metadata information.

Remember as always to test your workflows in the sandbox environment before applying any changes in the production database.

Using Python in FME: WorkspaceRunner and fmeobjects

Are you an FME user and fond of Python? If you are, you might like learning more about use of Python in FME. FME is a great ETL software that can help streamline various GIS workflows. One of the neat features about FME that I like most is that it’s possible to call FME workbenches (think of workbenches as of ArcGIS ModelBuilder models) from Python supplying input arguments.

I have been using this approach for some of my workflows and it works really well. It’s fast and easy to customize. For some of the workflows, there might be a way to implement this purely in FME workbench, but I like calling things from Python because this lets me integrate FME workbenches into other non-GUI based operations.

This is the sample code for creating multiple buffers (basically Multiple Ring Buffer GP tool in ArcGIS) for the single input shapefile.

Calling an FME workbench from Python is super easy. You need to do just a couple of things:

* add fmeobjects to your path: this can be done in various ways; the one I found the most useful is to use the sys.path.append and specifying the path to the FME Python installation. The file we have to import from this folder is the fmeobjects.pyd which is essentially a Windows .dll folder. Check this question on the SO to learn more;
* initialize the FMEWorkspaceRunner class;
* run the workbench supplying the values for the published parameters as needed: the parameters are supplied in the dictionary format with the parameters names as dict keys and parameter values as dict values.

To take advantage of the intellisense and code completion in the IDE, I usually execute the code with the import of fmeobjects and then pause while staying in the debug mode. This makes it possible to access the properties and methods of the fmeobjects in the debugging window.

If you need to create a workbench that would be more flexible in terms of input parameters, you might consider learning about dynamic workspaces in FME if you aren’t familiar with them.

When you create a workbench with dynamic workspace, you are able to take any input data. This is what most authors of ArcGIS script tools would find useful. One would usually use FME for processing the same input on a regular basis, but I find it very helpful to be able to have a workspace that is not tied to any particular dataset or data schema and would let me process any kind of input dataset. Plus you would like to might want to use other transformers and not only those that have been added into the workbench.

This is the sample code that illustrates re-projecting multiple shapefiles in the input folder (think of this as of Batch mode for the Project GP tool in ArcGIS). The workbench that is being run has a Reprojector transformer added.

Unfortunately, the FME transformers are not exposed as Python functions, so this is where the FME differs from ArcGIS which has all of its GP tools exposed as arcpy package functions. This means you won’t be able to build all your workflows purely in Python because you won’t be able to use the transformers as they are. However, you can do a whole lot with fmeobjects purely in Python.

After importing fmeobjects, you will be able to read any supported data format (e.g. create a reader) and then write its features into any supported format (e.g. create a writer). The reader object is similar to the arcpy.da.SearchCursor(); it’s an iterator that you can go through just once reading features. Here is what is exposed to you among many other things:

* all the input dataset fields, its names and properties (similar to arcpy.ListFields() function);
* all the data rows (similar to arcpy.da.SearchCursor() cursor);
* geometry objects with lots of properties including coordinates of the vertices and the coordinate system (similar to arcpy.Geometry() class);

You will be able to manipulate the reader changing its schema and processing the features which you will write then into an output dataset. The feature object has also a bunch of methods for its processing. One of them is reproject() that re-projects the feature from its current coordinate system to that specified. Let’s build a script that can project an input shapefile without calling any FME workbenches.

Stress testing ArcGIS Server with Python

I’ve blogged earlier about using Apache JMeter for stress testing ArcGIS Server and for performance tests. It’s a terrific tool that can do so much more than that though.

In case you won’t be able to use the JMeter, you can perform a similar stress test on an ArcGIS Server service by using Python multiprocessing module as well as ArcREST Python package – an open source package developed by Esri and the user community and hosted on GitHub.

In this code, a pool of workers is created and each of those does the export of the map image which triggers creating an instance, if there aren’t any available, which use certain amount of RAM and CPU. To learn more about the ArcREST, please visit the Wiki page of the project. To learn more about the Python multiprocessing, check the Help page and some SO pages here and here.

For live monitoring of the ArcGIS Server service busy instances, a sample from Esri ArcGIS Server development team could be used. Another application built on top of that sample can be found here on GitHub.

Data analysis for ArcGIS Pro 1.2 users

If you will need to do any advanced data analysis, consider using Python libraries numpy and pandas for that. Just in case you are a user of ArcGIS Pro 1.2, the Python 3.4 compilation that can be installed with Pro does contain those libraries along with scipy and matplotlib. You will be able to perform most of your data analysis work with those libraries and you won’t need to download any package managers such as conda or pip. Jupyter notebook, formerly known as IPython notebook, is another great tool for interactive data analysis and visualization. In case you are not familiar with installing external libraries in Python, check this post on how to install pip; it’s very easy.  It’s all about downloading and executing a Python file. Then to get started with data analysis in Python and ArcGIS Pro:

1. install arcgis pro 1.2
2. install python 3.4.3 for arcgis pro
3. add to PATH system variable C:\Python34
4. run pip install ipython
5. run pip install jupyter

And now you have everything you will need to become a data scientist!

Working with csv files in Python

Have you ever needed to process some large datasets? Well, it all depends on how you define “large”. In this post, I will refer to large datasets as to relational tables or delimited text files of several GBs of size containing 10+ mln rows.

When I have to do some data processing or filtering, I often start analyzing what kind of toolset or framework should I use to perform the work. Say you have a large .csv file and you want to create a new .csv file that is filtered by a column (so you basically want to create a new .csv based on the SQL where clause).

Well, you can do that with ArcGIS. It is possible to add text files including .csv files into ArcMap and open its attribute table. You will be able to make selections based on attributes, calculate statistics, and many more. After having a selection created, you can export selected rows into a new comma separated file. However, in order to make selection work on the whole dataset (and not only the first 2000 rows visible in the attribute table), it seems as you need to move to the end of table. This can take rather long time; so it’s probably best to use the Definition Query tab in the Table Properties dialog box. This will make visible only those rows that match your criteria. Now you can export the whole table with the query on to a new text file; pay particular attention to what kind of default demiter will used when exporting a text file from ArcMap and whether any new columns such as OBJECTID is added to each of the source rows. Another caveat is to watch out carefully for any decimal delimiters that are used because they can cause problems for comma-delimited text tables. Refer to this KB article to learn more.

Another software used by many GIS professionals is FME and this task can be done within this framework, too. Probably the easiest way to do this is to use the AttributeFilter transformer to obtain only those rows that meet your criteria. You will be able to supply a Separator Character and Character Encoding among other options. Very handy and easy to build. Filtering 20mln rows csv file with two columns and writing to a new csv file with FME 2015 wouldn’t take more than several minutes on a decent machine.

Most DBMS will let you to import and export csv files, too. SQL Server, for instance, is also capable of importing a .csv file using the SQL Server Import and Export Wizard. You will be able to load a csv file into a SQL Server table, then create a new table with SELECT INTO with a where clause to keep only needed rows. You will also be able to script this process by using the SSIS packages or by using the BULK INSERT to insert csv rows into a table.

And… you can do this in Python! There are many ways to do this such as reading file lines or using built-in modules such as csv or external ones such as unicodecsv. Be careful when reading large files with Python; very often I see people reading all the file rows into a single object, and this can cause out of memory error even on powerful machines. Learn more about iterators in Python – the reader objects that will be created are generators that you can iterate (go through just once).

Here is a couple of samples on how to read/write csv with Python to help you get started:

OOP approach for ArcGIS development with arcpy

I have blogged earlier about creating helper functions for arcpy which can make you more productive. The basic idea is that you can wrap some of the chunks of code you call often in various projects and then store and maintain them just in one place. A great example of this would be using arcpy.Describe():

Let’s say you have a geodatabase and as a part of your workflow you need to find out some of its properties. You would get individual properties of the geodatabase; alternatively, you would construct a dictionary to access various attributes stored within the same Python object, just like you see in the sample above.

Today I want to show you how to implement this by using the object-oriented programming (OOP) paradigm. You haven’t probably seen that many examples of building new classes for use with arcpy. This is just a really primitive sample that can help you get a sense what is like to build a class and what advantages it provides. It’s important to understand that you might benefit from building own classes that are not present in arcpy. Even more, a new class might inherit all of its properties from an existing arcpy class such as arcpy.SpatialReference(), but be extended with extra properties and methods that you have yourself created.

As you see, we have started just by obtaining the geodatabase properties via the Describe properties. But then we went further and created own properties that would hold some useful information you would like have at hand while working with the geodatabase. It might be super helpful to have JSON like representation of all the features in the feature class, for instance. Let’s see now how we can quickly access various objects inside the geodatabase.

There is a ton of other things you could do now when you have all the feature classes and its features available. One thing to keep in mind is that it does take some time to read the features and feature classes properties, so when creating an instance of a large geodatabase, this process can take quite a long time. Unless you don’t need to access all the features within all the feature classes within the geodatabase, try to construct only those objects which are relevant for your work.

Export feature class attribute table into an Excel 2007 with xlsxwriter

Did you ever need to export the attribute table of your feature class into an Excel workbook? I am fairly sure you did. Maybe someone without ArcMap needed to take a look at a piece of data or build a graph within the Excel based on the attributes. You just need to take some of your data into an enterprise system that require an Excel file as input.

Previously, before the ArcGIS 10.2 was released, you had to build custom Python scripts for exporting the attributes. There are some custom scripts that were made available for 10.0+ users, too. Alternatively, you could export the attribute table into a .txt/.csv file which you could later import in Excel. Take a look at this Esri KB article HowTo: Export an attribute table to Microsoft Excel to learn more.

At 10.2, script tools Table to Excel and Excel to Table were made available in ArcGIS for Desktop for all license levels. These tools are based on the xlwt Python site package. However, these tools can work only with .xls files (Excel 97-2003 format) which means you won’t be able to generate an Excel from a feature class with more than 65,536 rows.

I’ve recently needed to generate multiple Excel files from rather large feature classes with some fancy styling; this is where another Python site package – xlsxwriter – came handy. It provides a very clean and fast interface for creating new Excel files. In fact, generating an Excel file from a feature class (much like what the Table to Excel geoprocessing tool does) can be done in this very snippet of code.

Keep in mind that it would be very easy to filter out what rows you want to export by using a where clause within the cursor definition. The xlsxwriter has an amazing suite of features for doing very fancy things within an Excel workbooks such as generating charts, using formulas, and provide data validation rules. The project is very active and there are many users, so it would be safe to include it in your production workflows.