Printing pretty tables with Python in ArcGIS

This post would of interest to ArcGIS users authoring custom Python script tools who need to print out tables in the tool dialog box. You would also benefit from the following information if you need to print out some information in the Python window of ArcMap doing some ad hoc data exploration.

Fairly often your only way to communicate the results of the tool execution is to print out a table that the user could look at. It is possible to create an Excel file using a Python package such as xlsxwriter or by exporting an existing data structure such as a pandas data frame into an Excel or .csv file which user could open. Keep in mind that it is possible to start Excel with the file open using the os.system command:

os.system('start excel.exe {0}'.format(excel_path))

However, if you only need to print out some simple information into a table format within the dialog box of the running tool, you could construct such a table using built-in Python. This is particularly helpful in those cases where you cannot guarantee that the end user will have the 3rd party Python packages installed or where the output table is really small and it is not supposed to be analyzed or processed further.

However, as soon as you would try to build something flexible with the varying column width or when you don’t know beforehand what output columns and what data the table will be printed with, it gets very tedious. You need to manipulate multiple strings and tuples making sure everything draws properly.

In these cases, it is so much nicer to be able to take advantage of the external Python packages where all these concerns have been already taken care of. I have been using the tabulate, but there are a few others such as PrettyTable and texttable both of which will generate a formatted text table using ASCII characters.

To give you a sense of the tabulate package, look at the code necessary to produce a nice table using the ugly formatted strings (the first part) and using the tabulate package (the second part):

The output of the table produced using the built-in modules only:

builtin

The output of the table produced using the tabulate module:

tabulate

 

 

Advertisements

Multiple Ring Buffer with PostGIS and SQL Server

Recently I needed to generate multiple ring buffers around some point features. This can be done using a dozen of tools – Multiple Ring Buffer geoprocessing tool in ArcGIS, using arcpy to generate multiple buffer polygons and merging them into a single feature class using the buffer() method of arcpy.Geometry() object, or by using open source GIS tools such as QGIS. This is also possible to achieve using relational database that has support for the spatial functions. In this post, I would like to show you how this can be done using the ST_Buffer spatial function in PostGIS and SQL Server.

In order to generate multiple buffer distance values (for instance, from 100 to 500 with the step of 100) in SQL Server, I would probably need use CTE or just create a plain in-memory table using declare; in other words, this is what it takes to run range(100, 501, 100) in Python.

In the gist below, there are two ways to generate multiple buffers – using the plain table and the CTE.

Generating a sequence of distances in Postgres is a lot easier thanks to the presence of the generate_series function which provides the same syntax as range in Python.

Visualizing computational geometry concepts using JTS TestBuilder

In this post, I would like to let you know about an excellent piece of software, Java Topology Suite (JTS).

JTS is an open source library of spatial predicates and functions for processing geometries. It provides a complete, consistent, and robust implementation of fundamental algorithms for processing linear geometry on the 2-dimensional Cartesian plane.

A funny thing about it is that JTS

is used by most java based Open Source geospatial applications, and GEOS, which is a C++ port of JTS, is used by most C based applications.

So, all the downstream projects using GEOS such as various Python wrappers around GEOS such as shapely and even the PostgreSQL extension, PostGIS, all of them really work against the JTS using the GEOS as the interface for communication. So the JTS is a very, very powerful Java library.

If you are not a Java developer, though, this might be of little interest to you. However, there is another little application, called JTS TestBuilder, which provides a GUI for geometry exploration and is an interface into the JTS API. It is not so famous as other pieces of open source GIS stack, such as QGIS or GRASS, though. Also its documentation is outdated and scarce, so you would need to find out how to use the application on your own.

Nevertheless, it is an indispensable tool for anyone who spends a fair amount of time working with computational geometry or spatial data processing applications. It would also serve as a great visualization tool for GIS instructors who need to visually explain how GIS algorithms operate. I have used it to show how Convex Hull is created from a set of points, for instance. One obvious advantage of JTS TestBuilder is that you do not need to run any heavy GIS applications and the “geometry modification – running analysis – seeing the result” cycle is really short.

Here I’ve loaded cities of California along with the state boundary and created a convex hull for the boundary geometry.

2018-03-14 17_41_18-JTS TestBuilder

Having said that, you can work in the following manner:

  • Use your favorite GIS database management tool to get WKT of a geometry you would like to inspect or analyze.
  • Use the JTS TestBuilder to draw the features.
  • Run JTS Geometry Functions constructing new geometries or answering spatial questions.
  • Load the results of the analysis back into your GIS (either for ad hoc exploration or for storage).

The code to read the features into WKT and write back from WKT using arcpy:

 

JTS TestBuilder can also help you to learn something new with regard to GIS theory. If you think that you are a well seasoned GIS professional who can amaze others by mentioning a few cool names like Voronoi or Thiessen, I encourage you to explore the geometry functions JTS TestBuilder provides. I am pretty sure just a few of you have heard of:

  • Koch snowflake which are used a lot in space-filling as well as cartographic simplifaction algorithms.
  • Seirpinski carpet which is not used extensively in GIS yet, but there are some emerging applications regarding urban pattern analysis.

If you would like to take advantage of the computational geometry algorithms implemented in JTS, there are also ports to .NET and JavaScript.

Another very similar application that is particularly popular among math teachers is GeoGebra. I have been using it for a while, too, but it lacks export of result geometries into WKT which can be put into a geospatial database or drawn directly in a desktop GIS application such as ArcMap or QGIS. You can try GeoGebra online or by installing a desktop application. It is also available as an app for iOS, Android, and Windows Phone.

Desktop PyQt application for executing SQL queries against Esri file geodatabase

As I was always interested in building GUIs for some GIS operations, I thought that exploring PyQt deeper would be fun. A project has started as an experimental playground to see what functionality PyQt provides. As I spent more time working with PyQt, I have started wondering what it would take to build a useful desktop application.

Because I often find myself in need of querying a file geodatabase’s datasets, I have decided to build a GUI-based SQL editor that would let me execute SQL queries against a table or a feature class and draw the result set in a table form for further visual inspection. I have thought that other GIS users and developers may find this application useful and I therefore have decided to start a GDB: GitHub repository to let others take advantage of my work. Here it comes, check it out!

GDBee_sample

GDBee is a PyQt5 desktop application which you can use to write and execute SQL queries against tables and feature classes stored inside an Esri file geodatabase. It provides a tabbed interface which lets you connect to multiple geodatabases within a single session. It has a rich code editor featuring auto-completion (with suggestions), syntax highlight, and result set export.

If you are a QGIS Desktop user, you are already able to execute SQL against file geodatabases using QGIS DBManager plugin, but GDBee has some extra features that the DBManager is missing (for instance, you do not need to add your datasets as layers first and you can choose to copy individual cells instead of the whole row) from the result table.

Because Python is so widely used in the GIS community, I thought it would make sense to take advantage of Python bindings of GDAL (via GEOS) to be able to connect to a file geodatabase and execute SQL queries. Working with a file geodatabase via GEOS makes it possible to take advantage of SQL spatial functions that are otherwise inaccessible to an ArcGIS user!

The application provides multiple features:

  • Working with multiple geodatabases using multiple tabs (single geodatabase connection per tab)
  • Exporting result sets into various formats (WKT strings to paste into QGIS using QuickWKT plugin, arcpy code to paste into ArcMap Python window, pandas data frame via .csv file (which can be taken into geopandas), and Markdown table via .md file or plain text)
  • Executing SQL query with respect to the user selection (only selected text is executed)
  • Loading/saving SQL queries from and to text files on disk
  • Convenient keyboard shortcuts for query execution (F5 and Ctrl-Enter) and tab interaction (Ctrl-N and Ctrl-W for opening and closing tabs)
  • Copying data from the result set table (either individual cell values or row(s) with the headers preserved) – ready to paste properly into an Excel sheet
  • Choosing whether you want to have geometry column in the result set as WKT

You can look at its GitHub repository: GDBee: GitHub repo. You may find this PyQt desktop application useful if:

  • You would like to be able to interrogate your file geodatabase datasets using SQL (instead of Python-based interface such as Esri arcpy or open-source ogr)
  • You are an ArcGIS user that does not want to have QGIS Desktop installed just to be able to execute SQL against a file geodatabase
  • You use SQL on a daily basis working with spatial databases (such as PostgreSQL or Microsoft SQL Server) and want to be able to execute ad hoc SQL queries against file geodatabase datasets without loading them into a proper DBMS database
  • You already have a lot SQL code targeting tables stored in a DBMS spatial database and you would like to be able to reuse this code when targeting a file geodatabase

Do you think there is some other functionality that should be added? Please let me know by submitting an issue in the repository.

Python progression path for GIS professionals

Over last years, I was working with Python almost full time either scripting some desktop GIS workflows or developing code for the back-end geoprocessing services using arcpy. I learned all kinds of Python packages, everything from data science packages such as pandas and numpy to more widely applicable ones such as xlsxwriter and reportlab. Being able to find a package and start using it producing the outputs needed in a matter of minutes is one of the key selling points of Python, I think.

However, due to the presence of such a large number of resources that are related to Python (just check this repository on GitHub – A curated list of awesome Python frameworks, libraries, software and resources)- one might feel a bit lost. There are so many things to learn, which are the most important ones? It also makes things a bit more complicated for niche developers or GIS analysts who do Python programming just occasionally. I have also experienced frustration being unable to identify the key competence areas to focus on and how to track my progress. Am I learning Python packages that are relevant for geospatial operations? What else should I learn after I’ve managed a certain feature of the language or a framework?

The result of this thought process is a public repository on GitHub which I am working on. It’s called Progression path for a GIS analyst who wants to become proficient in using Python for GIS: from apprentice to guru which is inspired partially by the awesome-python and partially by a SO post Python progression path – From apprentice to guru.

This is an attempt to provide a structured collection of resources that could help a GIS professional to learn how to use Python when working with spatial data management, mapping, and analysis. The resources are organized by progress category so basically everyone should be able to learn something new along the way. The resources will include books, web pages and blog posts, online courses, videos, Q/A from GIS.SE, links to code snippets, and some bedtime readings.

Be sure to check this one out, pick a topic of interest and start working on it. Also, feel free to star the repository if you have a GitHub account 🙂

Useful resources in computer science/math for GIS Analysts

A lot of people who are studying GIS at school or already working as GIS analysts or GIS consultants often wonder what kind of competence will help to be attractive for employers and what domains of expertise are going to be in demand in the foreseeable future.

Usually the kind of questions GIS professionals ask is how much a GIS analyst should learn from other domains. So, we are wondering how much math, statistics, programming, and computer science should GIS analysts learn. Naturally, knowing what kind of GIS specific expertise is in demand is also very helpful. I have several posts on how get better at GIS here, here, and here.

To know what kind of GIS tools can do what kind of job is definitely helpful. This is much like a woodworker should know what kind of tools he has in his toolbox and what tools are available in the woodworking shop. Finding an appropriate tool for a certain job is not so hard nowadays with the Internet search engine and QA sites. However, the ability to understand both how data processing tools work and what happens behind the scenes to be able to interpret the analysis results is indispensable.

What is often true for many GIS analysts is that during their studies the main focus was on the GIS techniques and tools while math and CS courses were supplementary. This makes sense and the graduates are indeed most often competent GIS professionals capable of operating various GIS software suites, provide user support, and perform all kind of spatial analysis. However, it is also possible that in a career change, a person who hasn’t done any studies on GIS, is working as a GIS analyst and needs to catch up a bit. For those people who feel that they lack background GIS competence that they should had a chance to learn during their studies, or for you who just want to learn something that could help to have a broader view and give a deeper understanding of the GIS, I have compiled a list of useful links and books. Please enjoy!

There are lots of great questions answered on the GIS.SE web site; here is just a few:

Great books:

Spatial Mathematics: Theory and Practice through Mapping (2013)
This book provides gentle introduction into some mathematical concepts with focus on mapping and might be a good book to start learning math in GIS. No advanced background in math is required and high-school math competence will be sufficient.

Table of contents

  • Geometry of the Sphere
  • Location, Trigonometry, and Measurement of the Sphere
  • Transformations: Analysis and Raster/Vector Formats
  • Replication of Results: Color and Number
  • Scale
  • Partitioning of Data: Classification and Analysis
  • Visualizing Hierarchies
  • Distribution of Data: Selected Concepts
  • Map Projections
  • Integrating Past, Present, and Future Approaches

Mathematical Techniques in GIS, Second Edition (2014)
This book gives you a fairly deep understanding of the math concepts that are applicable in GIS. To follow the first 5 chapters, you don’t need any math except high school math. Later on, the book assumes that you have good knowledge of math at the level of a college Algebra II course. If you feel that it gets hard to read, take an Algebra II course online at Khan Academy or watch some videos from MIT to catch up first and then get back to the book. What I really liked about this book is that there are plenty of applicable examples on how to implement certain mathematical algorithms to solve the basic GIS problems such as point in polygon problem, finding if lines are intersecting and calculating area of overlap between two polygons. This could be particularly useful for GIS analysts who are trying to develop own GIS tools and are looking for some background on where to get started with the theory behind the spatial algorithms.

Table of contents

  • Characteristics of Geographic Information
  • Numbers and Numerical Analysis
  • Algebra: Treating Numbers as Symbols
  • The Geometry of Common Shapes
  • Plane and Spherical Trigonometry
  • Differential and Integral Calculus
  • Matrices and Determinants
  • Vectors
  • Curves and Surfaces
  • 2D/3D Transformations
  • Map Projections
  • Basic Statistics
  • Correlation and Regression
  • Best-Fit Solutions

GIS: A Computing Perspective, Second Edition (2004)
The book is a bit dated, but it is probably the best book in computer science for a GIS professional. It provides very deep understanding of the computational aspects that are used in GIS.

Table of contents

  • Introduction
  • Fundamental database concepts
  • Fundamental spatial concepts
  • Models of geospatial information
  • Representation and algorithms
  • Structures and access methods
  • Architectures
  • Interfaces
  • Spatial reasoning and uncertainty
  • Time

Practical GIS Analysis (2002)
This book is a unique example of a book for GIS professionals who want to see how the basic GIS algorithms and tools work. The exercises that follow give readers a chance to execute many common GIS algorithms by hand which let truly understand even some complex operations such as generating TIN or finding the shortest path on a street network. The software used as a reference is ArcView GIS 3, but it is still relevant as the GIS concepts haven’t changed much since then.

Table of contents

  • GIS Data Models
  • GIS Tabular Analysis
  • Point Analysis
  • Line Analysis
  • Network Analysis
  • Dynamic Segmentation
  • Polygon Analysis
  • Grid Analysis
  • Image Analysis Basics
  • Vector Exercises
  • Grid Exercises
  • Saving Time in GIS Analysis

Maths for Map Makers (2004)
I haven’t read this book so don’t have anything to comment on this. Sorry!

Table of contents

  • Plane Geometry
  • Trigonometry
  • Plane Coordinates
  • Problems in Three Dimensions
  • Areas and Volumes
  • Matrices
  • Vectors
  • Conic Sections
  • Spherical Trigonometry
  • Solution of Equations
  • Least Squares Estimation
  • References
  • Least Squares models for the general case
  • Notation for Least Squares

Exploring Spatial Analysis in GIS (1996)
I haven’t read this book either. I guess this one might be hard to find, but have listed it here just in case.

Good luck with the readings!

OpenStreetMap to ArcGIS Network Analyst

Over some years, I have seen that many people are looking for ways to get started with the network analysis in ArcGIS. However, some may not have access to a properly prepared routing dataset, which can be used directly in ArcGIS by using the Network Analyst extension. I have blogged a bit on various street data sources before, so, by now, you should have a good idea about where to look for street data.

A couple of days ago, I found a very interesting resource on how to process the OpenStreetMap data in order to get a routable network dataset which can be used for network analysis purposes directly in ArcMap. The tools are available at the 52° North web site, the open source software initiative, and were written by Eva Peters.

After using the tools for some time and testing them extensively during some evenings (I’ve compiled some nice network datasets over Swedish metropolitan areas), I’ve decided to summarize how to use them to help those who want to get their hands on a properly configured ArcGIS network dataset for their geographic area, in order to learn how various Network Analyst solvers work.

Prerequisites:

  • ArcGIS 9.3.1 or 10.0 installed (either license would work – ArcView, ArcEditor or ArcInfo. You can even use your ArcGIS Engine license, if you have one). If you don’t have ArcGIS installed yet plan to install one, check the Esri system requirements page.
  • ArcObjects .SDK for Java 9.3.1 or 10.0 installed. If you don’t have SDK installed yet plan to install one check the Esri system requirements page. Since you are not going to develop anything with the ArcObjects SDK for Java for now, you don’t really need to install JDK (Java Development Kit). In fact, you can install JRE (Java Runtime Environment).
  • JRE is required. This can be downloaded from the Oracle web site. The version JRE6 has been tested by Eva, so why not download the jre-6u31-windows-i586.exe package there. I’ve used this one, too.

This is pretty much it.

After you’ve installed the software we need, just do a couple of checks to see that everything is alright.

  1. Start ArcMap to see that the installation went well.
  2. Check that the Java installation is OK: open the cmd and enter “java –version” to check if Java works fine. You should get a message like this:

C:\Documents and Settings\Administrator>java -version
java version “1.6.0_31”
Java(TM) SE Runtime Environment (build 1.6.0_31-b05)
Java HotSpot(TM) Client VM (build 20.6-b01, mixed mode, sharing)

  1. Download the tool from the 52 North web site. The tool itself is a .jar file (Java ARchive) which you can think of as a kind of .exe file in Windows. You will run this .jar file much like you would do with the .exe file. The easiest way to run the tool is to use a batch file (with the .bat extension), which will refer to the ArcObjects Java libraries we need and Java itself.
  2. Create a .bat file by using any text editor like Notepad (I like Notepad2). Enter this text into the .bat file:

“C:\Program Files\Java\jre6\bin\java” -cp “C:\Program Files\ArcGIS\java\lib\arcobjects.jar”;”C:\Program Files\osm2nds\osm2nds_ArcGIS_10.0_gui_1.1.jar” org.n52.osm2nds.core.userinterface.gui.Main

PAUSE

Where:

  • “C:\Program Files\Java\jre6\bin\java” is the default path to the Java installation folder
  • If using ArcGIS 9.3.1 – “C:\Program Files\ArcGIS\java\lib\arcobjects.jar” or
  • If using ArcGIS 10.0 – “C:\Program Files\ArcGIS\Desktop10.0\java\lib\arcobjects.jar” is the default path to the ArcObjects for Java installation folder
  • C:\Program Files\osm2nds\osm2nds_ArcGIS_10.0_gui_1.1.jar” is the path to the folder where you saved your tool downloaded from the 52North page.

Don’t forget that the path may vary depending on the Windows version used (in 64-bit Windows you would get ArcObjects installed in the “Program Files (x86)” folder).

You can find more notes on the installation and running the tool at the 52 North web page, if needed, since this post is just a brief overview. Let me know what you think about the tools and whether this is something useful!