Handy Bash scripts to run OGR command line tools

If you are a user of GDAL installation (which includes OGR), then you could take advantage of running OGR command line tools such as ogrinfo and ogr2ogr which are very handy and often more efficient in data processing comparing to Python scripts written using shapely or fiona. As a rule of thumb, if your Bash script is within 20-30 lines of code then you are doing okay. If it gets longer, it is worth switching to Python for readability and maintainability of the code.

Make sure to review the Python GDAL/OGR Cookbook!, it has a ton of useful examples. Below are some snippets you can use; they will also help you learn Bash if you are not familiar with it yet.

If you will be writing a lot of Bash, I suggest using an IDE that supports it. I have been using PyCharm with an amazing plugin BashSupport. It takes the experience of writing Bash scripts to a new level. It provides syntax highlight, auto-completion, and hover hints.

Advertisements

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.

Read file geodatabase domains with GDAL and use XML schema

As I keep working on the registrant Python package, I have decided to add support for generating report about file geodatabase for a case when you don’t have any ArcGIS software installed on a machine yet would like to use the package.

To be able to read an Esri file geodatabase using GDAL, you can either use OpenFileGDB driver or FileGDB driver which relies on FileGDB API SDK. I thought that it would be nice to avoid being dependent on a third-party library, so I am using plain GDAL which makes it possible to interrogate a file geodatabase without having any ArcGIS software installed.

You can look into the source code to see how information about tables and feature classes as well as their fields can be pulled. In this post, I will just show you how you can read file geodatabase domains. This is a bit special because there are no built-in methods for reading domains. There is a GDAL enhancement ticket that targets this, but for now the only workaround I found is to run a SQL query against a metadata table, get an XML string back and then parse it. Esri has published a technical paper XML Schema of the Geodatabase which definitely helps to navigate the XML representation.

Using domains can be handy in a situation when you would like to report not the actual data stored in a table and accessible through OGR (that is, codes), but rather the human-readable representation (that is, values). For doing this, you would need to grab an XML representation of a particular feature class and see what domains are used for a specific field.

The Python code for reading domains and then finding out what fields have domains assigned is below:

 

 

Convert an Esri geodatabase feature class to GeoJSON

I bet many of you using Esri geodatabase datasets needed at some point to convert your data to some other formats for interoperability. GeoJSON is one of those formats that can be understood by many other systems and APIs.

If you have ArcGIS Desktop 10.5+, you can use Features To JSON geoprocessing tool as in version 10.5, Esri has added optional geoJSON parameter. Please mind that this tool didn’t have this parameter in previous versions.

If you are still on ArcGIS Desktop 10.4 or an earlier version, you could use the private interface of arcpy.Geometry objects that can expose some of the information about the geometries in the form of geoJSON and then do the rest on your own using json module:

g = arcpy.PointGeometry(arcpy.Point(45, 45), arcpy.SpatialReference(4326))
g.__geo_interface__
{'coordinates': (45.0, 45.0), 'type': 'Point'}

If you have ArcGIS Pro installed, then you can use the Features To JSON geoprocessing tool which has always had this optional geoJSON parameter which will do the trick.

If you need to do a conversion on a machine without ArcGIS Desktop, you could use ogr2ogr command (which will convert the Counties shapefile into a .json file in geoJSON format):

.\ogr2ogr.exe -f “GeoJSON” “C:\GIS\output.json” “C:\GIS\Temp\Counties.shp” -select NAME,POP2000

You could also use some other conversion tools such as ogr Python module:

What I really like about geoJSON is that it’s possible to draw its features online very easily such as using http://geojson.io (you can even edit, style and share your geoJSON using this service!) or on GitHub (they have pretty good support for geoJSON). On GutHub, you can even show the geoJSON features on a basemap directly in the source code or in a gist. Look at this example!