Well identifiers
How Novi UDM Data identifies wells and wellbores, and how to match your API numbers to them.
Every well has a WELL_KEY and every wellbore has a WELLBORE_KEY. Both are stable strings built from the regulatory identifier.
Join on the keys. Use NATIVE_ID to match identifiers that come from outside Novi.
Key formats
Section titled “Key formats”| Key | Format | US example |
|---|---|---|
WELL_ |
{COUNTRY}:{NATIVE_ |
US:API10:4212340567 |
WELLBORE_ |
{COUNTRY}:{NATIVE_ |
US:API10:4212340567:0 |
The parts are columns on the same row:
| Column | On WELL |
On WELLBORE |
|---|---|---|
COUNTRY |
ISO 3166-1 alpha-2 code, for example US |
Same as its well |
NATIVE_ |
API10 in the US, UWI-DLS in Canada |
API14 when a borehole and event suffix is available, otherwise API10. UWI-DLS in Canada. PLANNED for a planned wellbore with no regulatory ID yet |
NATIVE_ |
The regulatory ID in canonical form: 10 digits for API10, no hyphens | The wellbore’s regulatory ID. API14 is 14 contiguous digits. For PLANNED it is a Novi ID |
SEQUENCE_ |
Not used | Ordinal of wellbores that share a NATIVE_, starting at 0 |
SEQUENCE_NUMBER is 0 on every row in the current release: no NATIVE_ID yet resolves to more than one wellbore.
Match your API numbers
Section titled “Match your API numbers”WELL.NATIVE_ID holds the API10. Normalize the values you have before you match: strip everything that is not a digit, then take the first 10 digits.
Dashed forms, API10, API12, and API14 then all match.
SELECT w.WELL_KEY, w.WELL_NAME, w.NATIVE_ID, w.STATE, w.COUNTY, w.STATUSFROM NOVI_WELLS.WELL wWHERE w.NATIVE_ID IN ( SELECT LEFT(REGEXP_REPLACE(value, '[^0-9]', ''), 10) FROM TABLE(SPLIT_TO_TABLE('42-000-00001, 4200000002, 42000000030000', ',')))ORDER BY w.WELL_KEY;Replace the sample list with your own comma-separated API numbers.
Rules of thumb
Section titled “Rules of thumb”- Join well-level attributes on
WELL_KEYand wellbore-level data onWELLBORE_KEY. - Count wells with
COUNT(DISTINCT WELL_KEY). Count wellbores withCOUNT(DISTINCT WELLBORE_KEY). WELL_NAMEis for display. It is not a key.OPERATOR_IDidentifies an operator.OPERATOR.NAME,OPERATOR_REPORTED, andOPERATOR_ORIGINALare names, not keys.OPERATOR.NAMEis stored as the source spelled it and may carry padding, so match it withLOWER(TRIM(NAME)).COUNTYis a county or parish name.COUNTY_CODEis the five-digit FIPS code.STATEis the two-letter USPS code.LEASE_NUMBERonWELLis a state lease or production-unit identifier, not a key. Texas files the Railroad Commission district and lease number joined by a semicolon, for example08;39152. Oklahoma files the 14-digit production unit number. It is NULL in every other state today.
Planned
Section titled “Planned”- API14 wellbore identifiers for US wells. The model supports
API14for wellbores. Distinct US wellbores keyed on API14 are planned. - Canadian wells. Canadian wells will use the
CAcountry code and Canadian UWI schemes such asUWI-DLS, for exampleCA:UWI-DLS:00/02-13-069-09W5/0:0for a wellbore. See Releases.
Glossary
Section titled “Glossary”| Term | Meaning |
|---|---|
| API10 | The 10-digit API well number, the US regulatory identifier for a well |
| API14 | The 14-digit API number, which identifies a single wellbore or completion |
| UWI | Unique well identifier: the API number in the US, or the Canadian equivalent (UWI-DLS) |
NATIVE_ / NATIVE_ |
The regulatory identifier of a well or wellbore and the kind of identifier it is |