The capability to integrate readily different vendor databases has been a major need of the oil and gas industry. The primary reason for this need is obvious: Integration creates synergy. More can be accomplished with two databases working together than can be done if each is only used separately.

To meet this need, Nehring Associates has developed an oil and gas field cross-reference table for the United States. This table combines the three different national field code systems that are currently available: those by Nehring Associates (NRG), IHS Energy, and the Energy Information Administration (EIA). This section describes the conceptual problems involved in creating a field cross-reference table for the United States and how we resolved them, the structure and rationale of the field cross-reference table, and the practical problems we addressed in developing the table.

For those who have never attempted to create a comprehensive field cross-reference table, matching up different systems might seem to be merely an easy programming task. All one has to do is match up field names among the three different systems, a task that is readily susceptible to current hardware and software capabilities. Such an approach can be quickly shown not to be the entire answer (although it is certainly a useful starting point).

First, there are many small differences in the spelling and punctuation of field names, differences which preclude a quick computerized match. More importantly, each of the field code systems to be used - EIA, IHS, and NRG - has a different conceptual basis. EIA assigns a unique field code to each unique field name; thus different fields with the same name, either within a state or in different states, have the same field code in the EIA system. IHS assigns a unique field code to each unique field name within any state; thus different fields in the same state with the same name have the same field code in the IHS system. Nehring Associates assigns a unique code to each geographically and geologically distinct field. Given these conceptual differences, we concluded that a conceptually sound field cross-reference table requires at least two elements instead of one for a unique tie. Because all three field code systems include the county in which the field is located, we concluded that the combination of field code and county provided the most comprehensive and efficient link among the three systems.

The second major problem is that oil and gas fields, as we define them at Nehring Associates, can be either simple or complex entities. (The entity status of each field in the database is indicated by our field composition and additional field composition indicator variables.) Simple fields are readily cross-referenced; their names are usually the commonly recognized state field names. Complex fields can be quite difficult to cross-reference; their names in our database may be hyphenated names combining two or more common field names or names of our own devising which usually incorporate but are not limited to a common field name. A cross-reference table that is easy to use has to indicate both the existence and the nature of this complexity.

These two considerations - the necessity of a two-element unique identifier and field complexity - were the primary determinants of our cross-reference table design as shown below.

The Field Cross-Reference Table Structure

This table is drawn from our General Field Information (FLDINFO) and New Fields (NEWFLDS) tables and a field code master table provided to us by IHS Energy. The table follows a few simple naming conventions to make the source of each variable transparent. Where the variable is both drawn from a Nehring Associates table and is created by Nehring Associates, the variable name begins with “NRG”. Where the variable is both drawn from the IHS table and is created by IHS, the variable name begins with “IHS”. Variables drawn from Nehring Associates tables that have a source other than Nehring Associates either have no prefix (the standard county codes from
API and the state/district codes from EIA) or have a prefix indicating their source (the EIA and state field codes).

The table is built around the three nationwide field code systems (NRG, IHS, and EIA) and the field names associated with the Nehring Associates and IHS field codes. The EIA field names are so similar to the IHS field names that we decided that a separate EIA field name was redundant. Because field location is critical to determining field identity, we include standard state/district and county codes and both the Nehring Associates and IHS state and county names. Both state names are included because IHS uses only the full state name while Nehring Associates uses the state/district name. Both county names are included because of differences in abbreviation and punctuation between Nehring Associates and IHS, primarily in the offshore. The Nehring Associates Additional County Indicator is included to provide a concise signal of locational complexity. An “N” indicates a simple, i.e., single, county location; a “Y” indicates a complex, i.e., a multiple, county location. The Nehring Associates Field Composition variable provides the complete and concise Nehring Associates field definition. The Nehring Associates Additional Field Composition Indicator provides a concise signal of whether the field composition is simple (an “N”) or complex (a “Y”). The State Field Code variable, which is not available for all states, provides a link to many of the various state databases. The Nehring Associates Field Centerpoint (latitude and longitude) provides a link to spatial databases, such as those that use polygons created from all IHS wells in the same IHS field.

In populating the table, we found it helpful to think in terms of a two-dimensional matrix. The two dimensions are field location and field composition. Each dimension has two values, simple or complex. Thus, each oil and gas field can belong to one of four possible categories. Fortunately, most fields (80.7%) have both a simple location and a simple composition. Each of these fields has only one row in the cross-reference table. The next most populated category, with 11.2% of the fields, is those fields with a simple composition and a complex location. Each of these fields has one row in the cross-reference table for each county in which the field is located. The third category, with 5.7% of the fields, is those fields with a simple location and a complex composition. Each of these fields has one row in the cross-reference table for each field component (as defined by NRG). The fourth category, which is fortunately the smallest with only 2.4% of the fields, is those fields with both a complex location and a complex composition. For those fields, we have attempted to provide one row for each component and each county in which that component exists. The entire table lists 16,175 distinct oil and gas fields (all those in the 21st Update of the Significant Oil and Gas Fields of the United States Database) and 22,775 rows. Because of the two-dimensional and dual value character of the table, there is no primary key within it.

Creating the Field Cross-Reference Table involved two major processes. The first process was to match the Nehring Associates and IHS field codes by field name. The second process was to determine correctly and completely the counties in which each field was located.

The first process began with a computerized match up of field names by location. More than 75% of the fields, mostly those with a simple composition, could be matched in this way. We then visually compared a retrieval of Nehring Associates fields that lacked an IHS match with the IHS master field list, matching up most of the field names which were not an exact digital match because of differences in spelling, character count, punctuation, or abbreviation. During this step, we also matched all Nehring Associates “partial” fields, that is, those limited to only a particular formation or group of formations within a field, to their full IHS equivalents. This comparison resulted in a match rate of field names in excess of 97%.

Our final step in this process was to examine in detail all of the several hundred remaining match failures. These were all cases where there was no IHS field for a Nehring Associates field. The largest category here was our suspense fields (Database Status = “SP”) that had not yet begun to produce. Because the IHS field code system is tied to fields that have producing wells, it does not yet include these fields. The second largest category was old (and usually very small) fields and field components that were abandoned several decades ago. Because they had no producing wells when the IHS field code system was initiated, these fields were not initially included in the IHS system. The IHS field code system was also found to be problematic for a number of Niagaran reef fields in Michigan and to lack fields in Missouri altogether. These problems were corrected by IHS for the first update to the table. The current version of the table has a 99.8% match rate between the Nehring Associates and IHS fields (excluding discovered but not yet developed fields).

The second process began with a computerized match-up of county names for each field. For nearly all fields with a simple location, we were able to achieve a complete match. Failures to match for this category of fields were the result of differences in the spelling of county names. We corrected all the onshore county name spellings in the Nehring Associates database to reflect the API spelling conventions that IHS uses (primarily by adding a space after the “MC” in all county names beginning with those two letters). Offshore, where Nehring Associates follows precisely the API pseudo-county name spelling and IHS does not, we matched records through a visual comparison.

For those fields with a complex location, i.e., those in two or more counties, we undertook a comprehensive review of field location, using the stated IHS and Nehring Associates locations, combined with various state and federal sources and recent maps. We added counties (primarily to the Nehring Associates file and to a much lesser extent the IHS file) where the evidence was conclusive that a field spread across two or more counties. We deleted counties (primarily in the IHS file and to a much lesser extent the Nehring Associates file) where the evidence was conclusive that a field was not in a county in which one of the files asserted it to be. The relevant tables in our database were revised to reflect these changes, which ran to several hundred additions and deletions. For several fields in Texas, particularly in RRC Districts 7B and 9, we also changed the field name and field composition by incorporating the county name into the field name to indicate more precisely specific fields. This was necessary because we found several geographically distinct fields with the same name in the same district.