idea Expanded Pattern Matching in File Geodatabases (and documentation would be nice) in ArcGIS Pro Ideas

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

JoshuaBixby — Fri, 22 Jan 2021 14:13:22 GMT

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:

Oracle: Pattern-matching Conditions (Covers LIKE and REGEXP_LIKE)
PostgreSQL: PostgreSQL: Documentation: 9.3: Pattern Matching (Covers LIKE, SIMILAR TO, and regular expressions)
SQL Server: Pattern Matching in Search Conditions (Covers LIKE)
Access/Personal GDB: Like Operator (Microsoft Access SQL) (Covers LIKE)
SQLite/GeoPackage: SQLite Query Language: expression (Covers LIKE, GLOB, REGEXP, and MATCH)

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.

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

JoshuaBixby — Fri, 22 Dec 2017 23:10:42 GMT

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.

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

JoshuaBixby — Wed, 10 Jan 2018 01:02:37 GMT

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.

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

DanPatterson_Retired — Wed, 10 Jan 2018 13:37:18 GMT

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.

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

JoshuaBixby — Wed, 10 Jan 2018 14:24:48 GMT

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....

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

JoshuaBixby — Thu, 25 Jan 2018 17:12:10 GMT

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.

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

BruceHarold — Wed, 21 Mar 2018 15:12:29 GMT

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.

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

JoshuaBixby — Wed, 21 Mar 2018 15:21:46 GMT

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.

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

BruceHarold — Wed, 21 Mar 2018 15:35:10 GMT

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

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

KoryKramer — Wed, 28 Mar 2018 13:57:40 GMT

[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.

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

JoshuaBixby — Wed, 28 Mar 2018 14:10:07 GMT

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 .

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

KoryKramer — Wed, 28 Mar 2018 14:29:51 GMT

I suppose that would be part of the documentation piece needed here. I'll make sure to get this to the team that will be updating the documentation.

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

JoshuaBixby — Wed, 28 Mar 2018 14:42:16 GMT

Thanks, Kory, documentation would be most helpful.

Just to point out, neither the title nor the content of this idea ever stated "add regular expression support using LIKE," so I am not sure why the product team assumed that is what was being requested.

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

JoshuaBixby — Wed, 28 Mar 2018 18:25:48 GMT

I just tested on ArcGIS 10.6.x and ArcGIS Pro 2.1.x, and SIMILAR TO works. This is great news, it is a shame Esri never documented it. Any ideas for how long it has been supported?

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

JoshuaBixby — Wed, 09 May 2018 16:36:15 GMT

For those who find this idea and discussion and am wondering about documentation, the most descriptive and closest documentation I have found for matching observed behavior of SIMILAR TO with file geodatabases is SIMILAR TO . It will be nice when Esri finally documents this behavior.

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

DougBrowning — Thu, 01 Nov 2018 21:39:22 GMT

I am testing this and SIMILAR TO does not work when using wildcards but does work otherwise.

So AG3 similar to 'AG3' works but

AG3 similar to 'AG%' does not work. (AG_ does not work either)

What I really want to do is [1-9]!

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

JoshuaBixby — Fri, 02 Nov 2018 12:56:52 GMT

Doug Browning‌, I got all 3 of your expressions to work (%, _, and [1-9]).

I am willing to keep troubleshooting this issue with you, I suggest you start a new question in the Geodatabase‌ space. I

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

DougBrowning — Fri, 02 Nov 2018 14:42:24 GMT

Using Similar To or LIKE?

I tested the heck out of it and never got it going. On 10.5.1 using a File Geodatabase (.GDB)

Watch

But strangely this works

And this does not

So maybe the % does not work in combo with []?

What am I missing?

Thanks

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

JoshuaBixby — Fri, 02 Nov 2018 15:21:32 GMT

As mentioned above, it doesn't fully work until 10.6.x. See /blogs/tilting/2018/07/19/like-and-similar-file-geodatabases-and-pattern-matching

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

DougBrowning — Fri, 02 Nov 2018 15:54:22 GMT

Found more documentation and this seems to work

TopCanopy Similar To 'AG[0-9]*'

It just does not like []%