A day in the life of a petro-data manager - Part I - Shorthand

[Update: a simpler workflow that uses  for-fee & for-free software is posted here]

After intoducing the process to extract, transform & load  (ETL) www.boem.gov well data into a www.ppdm.org database, here is the short version expanded over on my sister blog.

This came about from converging circumstances:

• PPDM posted an exercise with a mix of for-free + for-fee software, say, v.0.1
• rewritten using entirely free / open-source software to remove barriers to entry
• catalyst was Professor Tom Mueller of California University of Pennsylvania
• class notes document enough caveats that this iteration will be really v.0.9
• these will be updated with ETL software robust enough to release as v.1.0
• other catalyst be PPDM and PUG Online jointly promoting oil&gas workflows

This is the workflow as it stands today with some scope to simplify it:

click image to enlarge

Here is the concluding paragraph from the course brief in the sister blog, so stay tuned:

This is a very quick run-though of a simple yet complete ETL exercise any petrodata manager can be faced with today. Here are the main takeaways, but as mentioned already, the full class notes and data will be posted on the PPDM website:

• Install and configure SQL Server and Talend Open Studio
• Extract flat ASCII files from public source
• Transform Excel files to PPDM WELL
• Load same to SQL Server database, and
• Verify & display:
• Access and SQL Server database
• QGIS and ArcGIS maps, and
• ArcGIS Online direct or via ArcMap
• Workflow diagram outlining the entire process

Here are plans for future class notes to address issues noted at the beginning:

• Caveat 1: Excel intermediaries needed as Talend didn’t reformat files directly as intended
  Improvement 1: Try an ETL tool like Safe FME that can do that directly
• Caveat 2: we loaded those same Excel files into desktop and web maps via file geodatabase
  Improvement 2: Try the same ETL tool to output GIS and web maps directly

source: Wikimedia Commons