Expanded Pattern Matching in File Geodatabases (and documentation would be nice)

12-22-2017 11:05 AM
Status: Open
Labels (1)
MVP Esteemed Contributor

I recently ran into an issue that caused me to review the SQL reference for query expressions used in ArcGIS—Help | ArcGIS Desktop.  In the Strings section of the SQL reference I came across the following line:

Refer to the documentation of your DBMS for a list of supported functions.


I decided to take that advice and look up supported string functions with various DBMSs, particularly pattern matching with the SQL WHERE clause:


Looking at the list, one might ask, "where is the link for file geodatabases?"  My response, "exactly, where is that documentation?"  There are some pages that provide examples and speak around the issue, e.g., Building a query expression and SQL reference for query expressions used in ArcGIS, but I haven't been able to find a page that definitively and succinctly states the support like all of the other pages referenced above.


If one were to find the documentation (by the way, please share the link if you do), what would become clear is that file geodatabases have rudimentary support for pattern matching in strings.  The only other DBMS format, if we can call it a DBMS, with such limited support is the shape file.  Given when shape files were defined/introduced, I am willing to give a pass for them, but I can't say the same for file geodatabases.


The file geodatabase introduced some big improvements over the personal geodatabase when working with spatial data, but it wasn't and still isn't summarily better because it had to make some trade offs.  For all the promise that came with the original release of the file geodatabase, there has been an equal amount of disappointment over the lack of effort to continuing improving the format.


It is time for file geodatabases to enter the 21st Century and support regular-expression pattern matching with strings, or at least regular-expression-like functionality.


I opened a related enhancement request just for the documentation part:

#ENH-000110409 [Enhancement] Provide documentation for supported SQL pattern matching

UPDATE:  The enhancement request above is focused on documentation.  I have subsequently logged an enhancement request to actually improve functionality:

ENH-000110699 : Add regular-expression pattern matching with strings in the File Geodatabase. 


Huh, went from "New" to "Reviewed" within a week or so.  I will take "Reviewed" over "Not in Product Plan," but it would be nice for Esri to provide a bit more feedback on their review.


Not too exciting an announcement given the Statuses for ArcGIS Ideas docs

tagged and bagged....

Reviewed - Idea that has been read by Esri and categorized/ tagged and is ready to be promoted and interacted with through the Geonet site.

I agree, but I am trying to take a glass-half-full view.  It could be worse, they could just deny it.  Wait, maybe that wouldn't be worse because at least we wouldn't be waiting for engagement from Esri any longer....


Regarding ENH-000110699, Esri updated the status to "Not In Current Product Plan," so don't go holding your breath.  It really is sad how Esri created the file geodatabase and then walked away, has invested little to nothing in improving it, and it sure could use some improvements.


Hi all, for the record and for the fellow regex fans out there, regular expressions are supported in Data Interoperability and Data Reviewer extensions, which is where a lot of data remediation work happens, and of course there is always the re module in Python which could be used to find records and deal with them flexibly.


Bruce, thanks for the comments.  Unfortunately, both of those workarounds are expensive, one in terms of actual money and the other in terms of processing.  Limited regular expression functionality is supported in practically all "database" platforms, which makes its absence from the file geodatabase quite conspicuous.  The file geodatabase is showing its age, I wish Esri had a plan for what's next with file-based geospatial data storage.


Personally I would be tempted by a SQLite flavor but writing the C code to support REGEXP is a bit daunting :-).


[ENH-000110409 Provide documentation for supported SQL pattern matching] is marked as In Product Plan.  

Like Joshua stated, 

ENH-000110699, Esri updated the status to "Not In Current Product Plan,"

The explanation provided with that status update was "SIMILAR TO has been implemented in the File Geodatabase. It is a SQL standard implementation that provides access to Regular Expressions. Using Regular Expressions on like is not part of the SQL standard, even though many RDBMS systems have chosen to add support. With File Geodatabase SQL we follow the SQL standard wherever possible."  Hopefully that helps those who are interested in this functionality.

As for the status of this idea, well, since it asks for expanded pattern matching and it looks like that is is not in the current product plan, and also asks for documentation, which is in the product plan, we'll just leave this at reviewed and when we get the documentation this will be partially implemented.


Kory, where is SIMILAR documented for the file geodatabase?  When I check file-geodatabase-api/FileGDB_API_1.5.1 at master · Esri/file-geodatabase-api · GitHub , I can't find it covered in the FGDB SQL documentation?  Also, I can't find mention of it in SQL for reporting and analysis on file geodatabases—Help | ArcGIS Desktop or SQL reference for query expressions used in ArcGIS—Help | ArcGIS Desktop .