Select to view content in your preferred language

ETL Pattern: Working With Unsupported Database Platforms

486
1
08-27-2024 11:53 AM
BruceHarold
Esri Regular Contributor
2 1 486

Connecting to (geo)relational data is fundamental to working in ArcGIS, and many popular database platforms are supported as workspaces.  However, there are also many unsupported database platforms out there. How can you use them?  Well, this connectivity gap is so common across information technology a standardized way to bridge the gap emerged long ago, namely ODBC.

ODBC stands for Open Database Connectivity, a widely used technology that provides a standardized way for programs to talk to databases.  While slower than native drivers, it is plenty performant for everyday usage, and very easy for GIS analysts to install, given sufficient permissions.  In ArcGIS Pro, OLE DB is used to implement ODBC connectivity, so documentation is organized under Work with OLE DB Connections.

While the documentation for OLE DB connections in Pro correctly states that connections are read-only, the database being connected to may be read-write using other approaches in Pro, such as the ArcGIS Data Interoperability extension or a suitable Python module.

In the ModelBuilder session below, I am ingesting normalized data from a Microsoft Access connection into an ArcGIS Pro project geodatabase, including spatially enabling a column storing well-known-text (WKT) geometry.   Four tables are joined to create the desired output information product for GIS mapping and analysis.  The result can be refreshed on demand should the source data change.

ETL of OLE DB data into geodatabaseETL of OLE DB data into geodatabase

Of course, if your work is supported by simply reading an OLE DB source, you don't need any ETL steps at all!  I'm just demonstrating using a core tool - ModelBuilder - for some typical tasks.

Why would you choose an unsupported database platform for your GIS work?  The thing is you might not choose to, it may just be a fact of life for your organization.  For example, I have seen organizations in the energy sector collaborate using Microsoft Access databases, with various apps using parts of the schema, and the whole project, from inception to archival, depending on the database. ArcGIS Pro can collaborate in this environment.

OLE DB connections in ArcGIS Pro are configurable in a three-stage process:

  1. Install and configure an ODBC driver (if not already present)
  2. Create a Windows Data Source Name (DSN)
  3. Create an OLE DB connection in Pro

Note in the DSN link above you have flexibility with who can access an OLE DB connection in Pro, one or any user logged onto the machine or a file-based approach suitable for wider sharing of a connection.

Here are my tables read from Microsoft Access, they are joined Person->BusinessEntityAddress->Address->StateProvince.

OLE DB tables from AccessOLE DB tables from Access

After joining and creation of geometry the data is mapped!  Note the data is fake, it's the venerable AdventureWorks data from Microsoft, created for tutorial purposes (but not included in the blog download), there aren't really cyclists floating in the middle of Port Phillip Bay in Victoria, Australia!

Person table, spatially enabledPerson table, spatially enabled

In a couple of related posts (local files, cloud files) I showed that remote files can be used as tabular sources and databases respectively, and the same is true of OLE DB connections - if an ODBC driver exists for a database, and the database is cloud hosted or web-aware, then you can use web data via OLE DB.  There are many web-aware databases and data stores offering ODBC drivers.

In summary, OLE DB connections offer a simple way to extend the reach of ArcGIS into databases or data stores that are not supported ArcGIS workspaces.

1 Comment