Unique attribute value across multiple layers

Question

We are in the process of cleaning up our asset database which contains day 20 of layers generally in file geodatabase format and includes point, line and polygon types and some non spatial tables.

Is there a method or data format to create a unique integer ID for each record across all the layers? Ideally it would auto populate when entering new records to avoid gaps or duplication. A method that can be accessed by multiple software types would be advantageous. I am locked into ESRI software, but personally am more comfortable with QGIS. It would also be advantageous for the data to be created in AutoCAD and Microsoft Excel.

The purpose of the ID is to allow interaction with other closed database systems used by my employer, and to allow for including attribute connectivity in the layers (eg upstream and downstream manholes ID on sewer main).

I was planning to adopt a strategy of giving each layer a number and then each record a decimal manually (eg 19.00007) but thought this could be limiting and prone to error

Answer 1

You might want to consider generating a UUID field for every table. QGIS supports GeoPackages, which are based on SQLite. There are discussions over at SO for how to generate SQLite UUID fields. See also this GeoPackage discussion on UUIDs

If you're willing to make the transition to PostGIS, there is an inbuilt function for UUID generation.

If you need a general solution that produces an integer, you could just generate it from the objects themselves. Either use something like the regclass/oid of the table in question, or just produce a running integer list for the tables in the database (starting at a high number, to avoid running into issues where, for instance, row 11 in table 1 ends up with the same "ID" as row 1 in table 11), and then use that in conjunction with @row_number to get an integer made from the table id, plus the row id.

Finally, you can do this in Python. Assuming you have a Geodatabase:

# Dependencies. GPD might not be necessary but makes it simpler
import geopandas as gpd
import fiona
import uuid

# Read one GDB; produce another one with all tables updated to have a UUId field and a homemade ID field    
path = yourgdb
new_path = yourgdbx_indexed

# Read the GDB, iterate through it and create the two fields. Write another GDB    
gdb_layers = fiona.listlayers(path)
for l in gdb_layers:
    df = gpd.read_file(path,layer=l)
    df['uuid'] = [uuid.uuid4() for _ in range(len(df.index))]
    #update the name of the 'homemadeid' field to get the next line to work.
    #df['homemadeid'] = df['table_number'] + df.index
    df.to_file(new_path, layer=l, driver="FileGDB")

Bear in mind you don't want to use this as a primary key for loading tables into ArcGIS, as it requires a smallint type integer as a unique id row. But this would be a way to deconflict across tables.