Continuing with Development of the ERCB Load Logic
Continuing with the development of the logic to load data from the ERCB’s daily ST1, the ERCB.putInstant procedure was created. It simply queries ERCB._LicenseIssued.dt column, and creates an instance of base.Instant, if necessary.
It was then time for the ERCB.putEvent stored procedure. This creates an instance of base.Event, as you would expect, from the name of the procedure. This is linked to the previously created base.Instant instance though base.defines (’Instant-defines-Event‘…get it?). ERCB.putEvent then creates an instance of E.affects, which links the event to E.Well (’Event-affects-Well’). The presence of a well in the ERBC.LicenseIssued view triggers the creation of a license event (id_Type = 92), and the presence of a well in ERCB.LicenseCancelled triggers the creation of cancellation event (id_Type = 84).
I was under a slightly incorrect impression of how the re-entry region of the ST1 works. I had assumed it was referring to a previously issued well license. But it turns out, it refers to a license in the same ST1. This led to the conclusion there are really two types of licenses issued; ‘normal’ ones, and re-entry licenses. To handle this, a new instance of base.Type was created (id = 111), to reflect the re-entry license. Logic was then added to ERCB.putEvent which LEFT OUTER JOINs ERBC._LicenseIssued and ERCB.LicenseReentered. If there is a ‘hit’ on LicenseReentered, id_Type = 111 is used, otherwise id_Type = 92. The steps above were then edited into ERCB.dtsx.
This all tested OK, so the ST1 for 2001-01-03 was used as a target for the logic, and it seemed to work just fine. It resulted in a combined total of 187 wells. SQLQuery27.sql was modified to JOIN E.Well, E.locates, base.Locus, base.delimits and base.Point and generate the necessary KML tags. The results were placed in MultiSurfaceOffsetTest.kmz. The lat/long conversion logic seem to be holding up well, with lots of them lining up right over artifacts on the ground when viewed with Google Earth.
Next up, implementation of events for typeCd and substanceCd. Sadly, the ERCB didn’t start specifically identifying the substance on the ST1 until 2003-09-28. On that date, they went from a four line format to a five line format, and started to include substance, as well as drilling operation, well purpose, and well type. For the period from 2001-01-01 though 2003-09-27, therefore, something else will have to be used to determine these characteristics for the well. I think it will be possible to get this from ERCB.LicenseIssued.laheeClassificationDsc.
Posted on 16th May 2008
Under: Developers' Journal | No Comments »
