Inserting an autoincremental identifier in your GIS table

Last friday someone asked me if there's a way to automatically insert an unique identifier into a Oracle Spatial table during an editing session in ArcGis. Based on my knowledge of sequences and triggers I immediately answered 'yes'. In this post we'll explore the possibilities.

The challenge

The organisation wants to assure that each object in a particular table is automatically assigned an unique business-id. This business-id is the identifier that all future users will be using when referring to the object in any form of communication. So the business-id has to have meaning to the users, which is accomplished by adding a code in front of the unique number.

• Construct a unique identifier with a fixed width (9 positions) based on a code and a number (e.g. ST0012345) and add it to a GIS dataset in Oracle Spatial.

The solution

First off, we need to create a sequence in the database that will generate a unique number every time the sequence is called.

CREATE SEQUENCE unique_seq
  START WITH 1
  INCREMENT BY 1
  NOCACHE
  NOCYCLE;

After the sequence is generated, we create a trigger on the intended target table that calls on the sequence and constructs a unique code of 9 characters long. This code is inserted in a VARCHAR field called BUSINESS_ID.

CREATE OR REPLACE
TRIGGER "<instance>"."<table>_trg"
  BEFORE INSERT ON <instance>.<table>
  FOR EACH ROW
DECLARE
  unique_id INTEGER;
BEGIN
  IF (:new.BUSINESS_ID is null) 
  THEN
    unique_id := unique_seq.nextval;
    :new.BUSINESS_ID := 'ST'||LPAD(TO_CHAR(unique_id),7,'0');
  END IF;
END;

When the trigger is compiled users can start editing. Upon insertion of a new feature it will automatically get a new business-id.

Usage across multiple tables

We are planning to populate multiple tables with a business-id. Each table will have it's own leading fixed code  followed by a number ('ST0012345', 'DU0001235', 'KD0005467' etc.). Say for instance we want to ensure that the unique number is truly unique even when the leading codes are trimmed of the business-id. In this case, the sequence can also be used across multiple tables. We can define multiple triggers like shown above, each referencing the same sequence. Simply replace the <table> placeholder with the appropriate table names.

• You can use the same field name (e.g. BUSINESS_ID) or several field names (BUSINESS_TABLE1_ID, BUSINESS_TABLE2_ID, BUSINESS_TABLE3_ID etc) depending on your needs.
• Of course, this will only work within one instance. (If you need to use a sequence across multiple instances, I'd seriously reexamine your database design.)

Versioning

In ArcGis Server, we have the possibility to set up our edit-table as versioned. This means that users don't directly edit the base table, but users get their own version to edit. All edits in a version are temporarily stored in ADD/DELETE tables until the version is reconciled with the base table.

It is possible to define a trigger that will assign a unique identifier on a versioned table. For this, we need to identify the name of the ADD-table that holds the edits until reconciliation. With the use of the following SQL-statement we can get the registration-id for the base table.

SELECT registration_ID
,      owner
,      table_name 
FROM sde.table_registry 
WHERE table_name = '<base_table>';

REGISTRATION_ID OWNER                            TABLE_NAME                 
--------------- -------------------------------- ----------------------------
            288 <USER_NAME>                      <BASE_TABLE>                 

Now, in the database instance, we will find a table named A<registration_id>, in this example A288. If we look at the table definition for this table, we'll see it consists of the same fields as our base table. This table will hold all edits to the base table while users are editing.

If we define the trigger on the ADD-table, all newly inserted features will automatically be assigned the correct business-id. And once the version is reconciled, all edits including the business-ids will move to the base table.

But be warned. Switching between versioned and unversioned mode can lead to some unexpected behavior. You'd have to define triggers to both the base-table and the ADD-table. Moving edits to base in versioned mode is actually inserting new records in the base-table, which fires off the trigger. Adding the IF-THEN-END part to the trigger should prevent the trigger from overwriting the business-id, but this we haven't thoroughly tested yet.