The Intellog Blog

Recent work has involved taking the new approach described in the previous post and slightly reworking the load logic so it’s fed from the ERCB.LicenseRecord table instead of ERCB.LicenseIssued and other VIEWs.  These changes were relatively minor, and primarily involved the ERCB.putWell, ERCB.putIdentity, and ERCB.putPoint stored procedures, and the VIEW ERCB.Point.

ERBC.LicenseRecord contains (or will contain) an instance for each and every license record which appears in the main body of each and every ST1.  However, there is still some logic which depends on knowing which ST1 was loaded most recently — the ‘current’ ST1, that is — and these instances were isolated in the view ERCB.LicenseRecordCurrent.  It simply looks at the dt column in ERCB._DailyActivityList, and SELECTs only those instances for that particular date.

One significant change was to recode ERCB.Point (the VIEW) into ERCB.Point() (the user-defined, table-valued function).  This was to enable multiple steps and temporary tables to be used to improved performance.  ERCB.Point() does not take any input parameters, which in my mind, makes the use of a user-defined, table-valued function in this situation a bit of a stretch.  Because of this, simply merging the logic of ERCB.Point() directly into ERCB.putPoint was considered, until it was discovered ERCB.putLocus was also using the previously-defined VIEW.

One other visible change to the logic was the addition of the ERCB.relabelWell stored procedure which changes Well.lbl in the event an amendment of type UNIQUE ID: is encountered.  It’s pretty simple; it uses ERCB.Well.  This VIEW does all the heavy lifting when it comes to determining the current Well.lbl, and what the new one should be.  ERCB.relabelWell simply looks at this VIEW, and then builds an UPDATE statement with it.  It’s fairly certain ERCB.relabelWell will be merged in with ERCB.putEvent at some point in the future, but for the time being it was isolated as a separate task, and added to the ERCB.dtsx package.

SQLQuery27.sql was then used to take the calculated well lat/longs, generate some KML, and project them onto the terrain with Google Earth (GE).  A check of a statistically significant sample of the wells on GE indicated nothing significant had broken with the calculation as a result of the changes described above, and in the previous post.

It then became an exercise of methodical testing.  Loading the first day for which there were records (2001-01-02), check the tables after the load is complete, then moving onto the next day, and so on.  Approximately three iterations of load, purge and starting over were completed, the most recent of which got to 2001-01-12.  It was on this date the first change of a well’s geographic location occurred due to an amendment.   The next job is to ensure the old location has been dealt with correctly, and the new location has been assigned to the well.  The specific example in question was 107/14-04-093-12W4M/00, and needs to be audited prior to loading more data.