Monday, 22 December 2014

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

After outlining the process to extract, transform & load  (ETL) www.boem.gov well data into a www.ppdm.org database, a course brief is posted 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

Sunday, 23 November 2014

Web Maps on Steroids, Part II

I showed a couple of months ago ways to post mega-datasets online without choking the system. This month OSGeoUK gave me the opportunity to present the same on PostGIS Day 2014, as postGIS was the backbone of two of these examples on GIScloud and GeoCloud2. Thanks to OSGeoUK and British Computer Society for hosting this.

Sunday, 9 November 2014

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

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:

Saturday, 4 October 2014

Big data and maps reloaded

Social media really work! +Vicky Gallardo posted on Google+ that the map is not the territory, with a wonderful big data map by Ben Allen of Denton TX (near where I used to live). That prompted a post by the same title on my companion map blog. The distinction between data and maps has long kept me up at night (see my blog tagline), and many others to judging by exhibits and media only in London (near where I live now).

Saturday, 27 September 2014

Web maps on steroids

The last 6 blog posts over the last 3 months chronicled the use of dynamic maps using time attribute - years for historic ship tracks and wind data from same 150-350 yrs ago - not to animate maps but to filter them by decade and manage data fetches on ArcGIS Online. A parallel series of posts showed mega data sets on Amazon Web Services, as Mapcentia assured me postGIS handled giga datasets...

Saturday, 6 September 2014

On joining and merging historic multi-lingual geodata

Earlier posts chronicled the history even the beauty of historic shipping and climate data from CLIWOC. British, Dutch, French and Spanish maritime agencies transferred paper logs to digital records. In doing so look-up tables allowed to convert multi-lingual records into quantifiable attributes. Something odd (to me) happened in the process of mapping these: over 1/4M records doubled to almost 1/2M when look-ups were joined and then wind and direction tables merged to create maps symbolised by wind force and orientation.

Wednesday, 27 August 2014

The happenstance art of maps

I showed recently how CLIWOC weather data from ships captains logs dating 1662 to 1885 totalled almost 1/2M points. It started with a 1/4M ships tracks, and combining look-up tables from four maritime agencies they yield numeric wind force and direction...

Friday, 15 August 2014

Dynamic maps final (for now)

[Update: longhand version posted on my catalog blog]

This is the end installment of progressing from static to dynamic maps online. A few lessons learned along the way on posting a quarter million point dataset, which ballooned to almost half a million after links & joins...

Saturday, 9 August 2014

Lessons from 'static to dynamic maps'

Last month related the stumbling blocks in posting too much data on arcgis.com - time animation pushed the limits of stock web service even when limits are set above 250K points - and other services don't offer animation as yet.

Sunday, 27 July 2014

From static to dynamic maps, continued

Last month I reported on posting on arcgis.com some time-stamped, time-slider or time-aware maps - I showed my WhereIsAndrew map - I also mentioned how time-sliders are a great way to roll-up diverse datasets that are time dependent. CLIWOC Captain's ships logs was the other example cited, and I proceeded to post it as a service from ArcMap on arcgis.com.