A day in the life of a petro-data manager - intro

[Update: Talend made the early version complex, so simpler one was posted later]

Have you ever been given plain text geodata and wondered how to database and map it? And has this happened to you lately with tens of thousands of lines of data? Well help is at hand! Here is an ETL  workflow (extract, transform, load) useful to any data manager in or out of petroleum using free tools:

1. pull 50K lines of flat ASCII well data from the US BOEM (MMS in 2008)
2. extract to a readable spreadsheet using the source government metadata
3. transform that using free Talend Open Studio with data QC check tools
4. load it into PPDM WELL table in SQL Server Express also a free tool
5. post the data to MSFT Access or in QGIS, ArcMap and ArcGIS Online 

The result is a complete web map of all the data in their geographic and economic context of Gulf of Maxico platforms, shipping lanes, leases and seismic anomalies (as in geologic types... not as in earthquakes!)  - please be patient when clicking here - there are 50k wells to load - it may be small data but it's still a lot over the web!

click to enlarge

All this is done according to PPDM well data standards, and show that those are not hard to implement at all. In fact this exercise shows how low the barrier to entry is to a community of practise, that literally puts "over $100M worth of knowledge" at our disposal.

So how did we get here? A full exercise available on request has been created from ppdm.org data model used with permission using US BOEM oil&gas well data free to use, using Talend Open Studio and Microsoft SQL Server Express also free,  to extract a WELL table following the Professional Petroleum Ddata Model an iinternational industry standard for oil&gas well data.

This is the input:

click to enlarge

This is the intermediate output:

click to enlarge

The PPDM WELL table looks like:

click to enlarge

QGIS is free and easy without well symbols:

click to enlarge

ArcGIS Online post without symbols but 50K wells may exceed its bandwidth:

click to enlarge

Best to post it in ArcMap, style the well symbols using the MMS metadata::

click to enlarge

Set against BOEM infrastructure data as above, this can be served up as an ArcGIS service that is shared as the map at the top.

The upside is that this process is scalable - Talend was originally written for 'big data', and the process is the same for full databases - so the limit is literally the sizeof your dataset or of your demand.

Now please cut over to the companion Map Blog that will detail some of the processes, in just enough detail to outline the tools and caveats, and engage you to take it further to the full exercise. Stay tuned...