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 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
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 (
WKTstrings to paste into QGIS using QuickWKT plugin,
arcpycode to paste into ArcMap Python window,
pandasdata frame via
.csvfile (which can be taken into
geopandas), and Markdown table via
.mdfile 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 (
Ctrl-Enter) and tab interaction (
Ctrl-Wfor 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
- 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.