Double exposure of creative abstract binary code hologram on laptop background.

At GeoDecisions, we balance science and art when organizing, integrating, and visualizing data. We leverage technology to drive innovative solutions for geographic information systems (GIS) and geospatial data.

A common function of a database management system (DBMS) is translating raw data from one table and inputting it into another table as structured data. For example, a company has a web application where people can log in and upload their information in real time. Data extraction happens when the uploaded data is read and written into another database, such as SQL Server, allowing additional access points to the volumes of data.

What is Data Extraction?

Data extraction is a crutial component of data analysis and is defined as gathering specific data points from a larger pool of data. The larger data pool can be data from different platforms, sources, or database systems. Getting meaningful results or solving the challenge can be impossible without proper data extraction.

The two types of data extraction are manual and automated. Automated data extraction is the most common in the GIS and geospatial data environment and involves programming.

One way to improve the quality of data extraction is incremental extraction. Incremental extraction limits data extraction to a date range or specific data sources. This approach keeps the data current and allows for more meaningful analysis.

Various data and file geodatabase extraction tools are available, including:

Web scraping.
Text extraction.
Data preparation.
PDF data extraction.

The best tool depends on specific needs, data types, and data volumes.

Extract, Transform, and Load

Extract, transform, and load (ETL) is the process of collecting, organizing, and storing data. The first step in the process, extract, is collecting the data from various sources. Next, transform, is cleaning and organizing the data to make it meaningful and reliable. And the last step, load, is storing the data in the final location for easy reference and further analysis.

There are numerous sources of GIS and geospatial data available. One trend we’re noticing is discovering ways to increase data movement and processing speed and streamline integration with Esri® technology.

Our GIS team recently created an innovative solution that required our web application to integrate with an Esri file geodatabase (FGDB). The two most difficult challenges were:

Architecting our ASP.NET Core 7.0 and Entity Framework (EF) Core 7 web application to read Esri FGDB in .NET (C#).
Writing the results into a SQL Server database table with a geometry column using EF Core 7.

The following information summarizes our solutions and provides links to reference sources.

Reading FGDB in .NET

Before we began, we researched available Esri solutions and resources to read FGDB in .NET. We found an older, minimally supported Esri library on GitHub. The problem was that the .dll files were compiled against older versions of .NET, which are incompatible with .NET 7.

As GIS software development experts, we leveraged our previous success using the open-source GDAL library to process FGDB. This led us to MaxRev-Dev’s open-source GDAL wrapper for .NET: gdal.netcore. While the documentation in this library didn’t answer all of our questions, we could use portions of it in our solution.

Writing FGDB into SQL Server

The second challenge was to write the features we read from the file geodatabase into a SQL Server database table with a geometry column using EF Core 7. We regularly reference the widely used NetTopologySuite library. This library provides ample amounts of documentation and examples. The last piece to fit into the puzzle was getting GDAL to work with it.

The Data Extraction Process

A process flowchart identifying the four steps we took to extract and save data from a FGDB in ASP.NET Core with EF. — The four steps to extract and save data from an FGDB in ASP.NET Core with EF.

1. Add Packages Via Visual Studio

Add the MaxRev.Gdal.Core, MaxRev.Gdal.WindowsRuntime.Minimal, and Microsoft.EntityFramework.Core.SqlServer.NetTopologySuite NuGet packages to project in Visual Studio.

2. Create Entity Model and Context Entries

Use the dotnet ef dbcontext scaffold tool to create the entity model and context entries for writing to tables.

Regardless of if you have already done this step, make sure to do it again. NetTopologySuite will add important context information for the geometry column to store the data correctly.

Here is a lesson learned: for one table, we added a geometry property to the EF model class without adding the correct mapping in the database context class. The result was incorrect, and we had difficulty debugging the issues.

3. Create Controller Class Method

Create a controller class method to handle file uploads. This controller class method will be similar to the following code:

[HttpPost("UploadFGDB")] 
[DisableRequestSizeLimit] 
[RequestFormLimits(ValueLengthLimit = int.MaxValue, MultipartBodyLengthLimit = int.MaxValue)] 
public async Task<ActionResult<List<string>>> UploadFGDB([FromForm] IFormFile file) 
{ 
if (file != null) 
{ 
await FgdbService.WriteToGeoDatabase(file); 
return Ok();
}
}

4. Send Uploaded Data to Service Class

The FGDBs are uploaded as zip files. The controller sends the uploaded data to a service class for processing. Our service class methods looked like this:

public async Task WriteToGeoDatabase(IFormFile file) 
{ 
   //Write the contents of the zip file out to the file system since the 
   //GDAL library cannot read the file from memory
   using var zip = new ZipArchive(file.OpenReadStream(), ZipArchiveMode.Read); 
   var tempFolderPath = $"<local_system_path>/{Guid.NewGuid().ToString()}"; 
   zip.ExtractToDirectory(tempFolderPath); 
   //In our application we can safely assume that the only folder in the zip file 
   //is a FGDB’s directory
   var fgdbFolder = zip.Entries.FirstOrDefault().FullName.Split('/').FirstOrDefault(); 
   ///Read in the data from the FGDB and parse it into the EF model 
   using var fileGdbDriver = Ogr.GetDriverByName("OpenFileGDB"); 
   using var dataSource = fileGdbDriver.Open($"{tempFolderPath}/{fgdbFolder}", 0); 
   //In our application we can assume that the first layer is the one we want but 
   //if we needed to loop through layers, we could do that.
   var layer = dataSource.GetLayerByIndex(0); 
   var layerDefinition = layer.GetLayerDefn(); 
   //WKT reader is part of the NetTopologySuite and it will allow us to convert 
   //the GDAL geometry to the NetTopologySuite geometry
   var wktReader = new WKTReader(); 

   //DatabaseTableModel is a sample EF model that will be written to the database
   var databaseRecords = new List<DatabaseTableModel>(); 

   var ogsFeature = layer.GetNextFeature(); 
   while (ogsFeature is not null) 
   { 
var dbRecord = new DatabaseTableModel(); 
//Read the attributes of the feature from the GDAL features into the 
//DatabaseTableModel properties based on the known names of the FGDB columns
dbRecord.Id = ogsFeature.GetFieldAsInteger("OBJECTID");
//populate any additional properties as needed…

var geometry = ogsFeature.GetGeometryRef(); 
if (geometry != null) 
{ 
   //ExportToIsoWkt will create string representation of the geometry 
   //in the WKT format from GDAL’s native format
          geometry.ExportToIsoWkt(out var wktGeometry); 
   //the WKTReader will translate the WKT into NetTopologySuite’s native format 
          dbRecord.Geometry = wktReader.Read(wktGeometry); 
} 
databaseRecords.Add(dbRecord); 
ogsFeature = layer.GetNextFeature(); 
   } 
   //Clean up file system now that we’re done reading the FGDB
   Directory.Delete(tempFolderName, true); 
   //Write the data to the database (_contextFactory is injected into the class)
   using var context = _contextFactory.CreateDbContext();
   context.DatabaseTableModels.AddRange(databaseRecords);
   await _context.SaveChangesAsync();
}

Successful File Extraction

Using these libraries and converting them using well-known geometry (WKT) representation will allow you to read FGDBs and write them into SQL Server databases in .NET.

About the Author

A man wearing a dark suite jacket with a dark blue shite and a maroon and gray striped tie smiling for the camera.

Chris Eby
Developer
Email Chris
Connect on LinkedIn

GeoDecisions Blog

How to Extract a File GeoDatabase in ASP.NET