FileGDB.LinqPadDriver 0.1.1

FileGDB Driver for LINQPad

A driver for LINQPad that allows read-only access to the Esri File Geodatabase (also known as File GDB, FileGDB, or FGDB).

About

Esri File Geodatabases store spatial data as relational tables persisted in a bunch of files in a folder with the extension .gdb.

This package contains a driver for LINQPad that allows it to read File Geodatabases. The driver makes use of a small .NET library to read the File GDB; it does not use Esri's File Geodatabase API nor any other library.

How to use in LINQPad

1. Install the driver
2. Add a connection to a File Geodatabase
3. Write queries (or any other .NET code)

Start LINQPad and click on Add connection. This opens the Choose Data Context dialog. At the bottom, click on View more drivers to open the LINQPad NuGet Manager. Click on Settings and make sure that the location of this NuGet is a package source; if not, add a new package source (see LINQPad documentation for details).

Once the driver is available to LINQPad, in the Choose Data Context dialog select "FileGDB Driver" and click Next. The File GDB Connection dialog appears, where you enter the path to the File GDB's .gdb folder.

A new data connection entry will appear and list all the tables in the File GDB. You can drag those table entries to a Query pane and use Intellisense to write LINQ queries against the table. For example:

Tables.MY_SHAPE_TABLE.Dump();
Tables.MY_SHAPE_TABLE.Select(row => row.SHAPE.ShapeBuffer).Dump();
Tables.MY_SHAPE_TABLE.Select(row => row.SHAPE.Bytes).Dump();
Tables.MY_SHAPE_TABLE.GetRow(5).Dump(); // get row by Object ID

As another example, here is how to retrieve properties about XY coordinate storage for each spatial table:

from entry in Catalog where entry.IsUserTable()
let table = entry.OpenTable()
where table.GeometryType != GeometryType.Null
let gdef = table.Fields.First(f => f.Type == FieldType.Geometry).GeometryDef
orderby entry.Name
select new {
  TableName = entry.Name, gdef.XOrigin, gdef.YOrigin,
  gdef.XYScale, // stored in the File GDB
  XYResolution = 1.0/gdef.XYScale, // reported by ArcGIS
  gdef.XYTolerance
}

Geometries can be formatted as WKT (well-knwon text format) with the ToWKT() method available on both the Shape and the ShapeBuffer class, and for convenience also directly on the GeometryBlob class (the optional integer argument is the number of decimal digits):

Tables.MY_SHAPE_TABLE.Select(r => r.SHAPE.ToWKT())

Query across all tables

The catalog exposed through the File GDB LINQPad driver is enumerable and each entry has an OpenTable() method. The returned table wrapper is itself enumerable, allowing you to write queries across all tables in a File GDB. For example, to find all multipart polyline features in any table:

from c in Catalog
where c.IsUserTable()
let table = c.OpenTable().Enumerable()
where table.GeometryType == GeometryType.Polyline
from row in table // SelectMany
where row.Shape.ShapeBuffer.NumParts > 1
select new {
  TableName = entry.Name, row.OID,
  Shape = Util.OnDemand(row.Shape?.ShapeType.ToString(), () => row.Shape),
  row.Shape.ShapeBuffer.NumParts,
  Operator = row.GetValue("OPERATOR")
}

• The catalog entry's OpenTable() method returns a plain FileGDB.Core.Table object, which is not enumerable; the extension method Enumerable() returns a wrapper around the table that is enumerable.
• The returned row objects have OID (type long), Shape (GeometryBlob, may be null), and Fields (list of the row's fields) properties.
• They also have a GetValue(name) method that returns the value of the named field or null if the row has no such field.
• A table does not know its name; to have the table name in the result rows, refer to the catalog entry's Name property (actually, the table wrapper returned by Enumerable() does have a Name property, but beware that the real table object of the underlying library does not).
• LINQPad's Util.OnDemand() facility may be useful to allow “drill down” on the results.

As another example, count shapes, parts, and vertices per table; again we use Enumerable() to make the plain table returned by OpenTable() enumerable:

// Count parts and vertices per feature class:
from c in Catalog
where c.IsUserTable()
let n = c.Name
let t = c.OpenTable().Enumerable()
where t.GeometryType != GeometryType.Null
select new {
  Name = n,
  Shapes = t.Count(r => r.Shape is not null),
  Parts = t.Sum(r => r.Shape?.ShapeBuffer.NumParts ?? 0),
  Points = t.Sum(r => r.Shape?.ShapeBuffer.NumPoints ?? 0)
}

Geodatabase Items

On an abstract level, Esri software organizes data as items of various types. The item types form a hierarchy with Item at the root. For example, a FeatureClass is an AbstractTable, which is a Dataset, which is a Resource, which is an Item; and a CodedValueDomain is a Domain, which is also a Dataset.

• type hierarchy is stored in the GDB_ItemTypes table
• item details are defined by XML stored in the GDB_Items table
• can use XElement.Parse() to access the XML content

A good view on Esri's understanding of what's in the Geodatabase can be obtained like this:

from i in Tables.GDB_Items
join t in Tables.GDB_ItemTypes on i.Type equals t.UUID
select new {
  Type = t.Name, i.Name, i.Path,
  Definition = i.Definition is null
    ? null
    : Util.OnDemand("Definition", () => XElement.Parse(i.Definition))
}

Limitations

• this is experimental code and comes with no warranty
• only a subset of the File Geodatabase is supported
• Raster fields are not supported
• MultiPatch geometries are not supported
• only full table scans are supported
• indices are not used and not accessible
• no concurrency control (no locking)
• strictly read-only (no updates)

References

• LINQPad: https://www.linqpad.net/
• Even Rouault's FGDB specification: https://github.com/rouault/dump_gdbtable/wiki/FGDB-Spec
• Esri's File Geodatabase API on GitHub: https://github.com/Esri/file-geodatabase-api

This project would not have been possible without Even Rouault's detailed FGDB specification.