FGDB Does not support fid properly (can select, can't use in where)

[wil]

The FGDB reader handles selecting features by a single id. For example:

ogrinfo -q -al -fid 21517 HaitiOSM.gdb roads

but does not handle the "where fid in" syntax like most adapters do, eg

ogrinfo -q -al -where 'fid in (21517,21518)' HaitiOSM.gdb roads

which produces the error message:

ERROR 1: Error: Failed Searching (An expected Field was not found or could not be retrieved properly.)

It does however let you select the field "fid" but not make a query that includes this field in the where clause. So, the first of the two below works, the second does not:

ogrinfo -q -al -sql 'select ogr_style,fid from roads where osm_id=48367580' HaitiOSM.gdb roads

ogrinfo -q -al -sql 'select ogr_style,fid from roads where fid=21517' HaitiOSM.gdb roads

As a result if you are trying to query (extract, etc) the features based on a set of IDs it cannot be done (assuming there is no other primary key and you want to use the OGR id).

[Even Rouault]

Try adding "-dialect OGRSQL" to your ogrinfo commandline. I'm pretty sure it will fix the error.

Explanation: the FGDB driver hasn't a specific implementation of ExecuteSQL(), so it is processed by the OGR generic code. However, in order to increase performance, the where clause of a SQL request (or the one specified by -where) is directly processed by the FGDB API. In the case, you specify a OGR specific field that the FGDB API does't know, hence the error message that comes from it. By specifying -dialect OGRSQL, you request that the generic engine processes the entire SQL request, including the where clause (but at the expense of less performance of course since it will be evaluated after getting each feature sequentially, without using any index that might exist).

And direct "-fid XXX" works since it uses a completely different API that retrieves directly a feature from its fid.

[wil]

Thanks for the feedback. That solves the "-sql" case, but still fails to handle the "-where" case. And, as outlined it is much slower:

For:

1. ogrinfo -q -al -fid 21517 HaitiOSM.gdb roads
2. ogrinfo -dialect OGRSQL -q -al -sql 'select fid from roads where fid=21517' HaitiOSM.gdb roads
3. ogrinfo -dialect OGRSQL -q -al -where 'fid in (21517,21518)' HaitiOSM.gdb roads

1. Takes 3.494 seconds (3.084 user, 0.360 sys) [using time from command line]
2. Takes 0.08s (0.04 user, 0.04 sys).
3. Fails.

It would be great if there was a directive for "ogrinfo" that indicates the "-where" option should use something similar (even if only for the specific fid in case) or that the "-fid" option could allow comma separated primary keys.

[wil]

Sorry, to clarify the comment above (didn't preview formatting):

1. ogrinfo -q -al -fid 21517 HaitiOSM.gdb roads
2. ogrinfo -dialect OGRSQL -q -al -sql 'select fid from roads where fid=21517' HaitiOSM.gdb roads 
3. ogrinfo -dialect OGRSQL -q -al -where 'fid in (21517,21518)' HaitiOSM.gdb roads

The results (seemingly) a primary key lookup, a sequential scan, and unsupported.

[Even Rouault]

Your timings for 1 and 2 (1 being slower than 2) are strange. Are you sure of that ? The primary key lookup done with -fid should be faster than the sequential scan of the SQL statement.

Yes, -dialect only applies to -sql. You can always transform a -where someting info a -sql "select * from roads where something". I'm not sure of the capabilities of the FGDB API, but perhaps you could use the "native" name of the primary key instead of the OGR specific fid. Anyway, there are some limitations in the way OGR SQL can interact with the underlying engine of the driver, so I think we just have to live with that, especially as there are workarounds (see similar discussion in http://trac.osgeo.org/gdal/ticket/4022).

[wil]

Sorry, my mistake. I got them backwards. 1 is 0.08 seconds, 2 is over 3 seconds.

[Jukka Rahkonen]

Workarounds given, closing ticket as wontfix.