Duplicate objectid in Views - Best practise to solve?

[AndersMark]

I've run into a problem with an Oracle View that produces duplicates of column OBJECTID.

The underlying geo-table has of course unique objectid:s but in the view, every geometry can occur severeal times. This violates functionality that requires that the ObjectID be unique. https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/object-id.htm

The view is a registred view in ArcGIS. 

 In my case I have a ArcMap service that consumes the view and when duplicate objectids are spotted it aggregates all rows with the same objectid. Maybe thats fine with the geometrys, as many lines can exist in a poly line field. The problem is the attributes where only one of aggregated rows will represent the geometry/row when consumed in the map service. Maybe it's the first row of the aggregated records that will display in the map or one of the others, it's hard to tell. It feels random. 

Is there a best practise to solve this kind of issue?

I have tried to make the view generate it's own unique value in the OBJECTID column instead reading that's  written in the underlying table but when I do this it's not possible to register the view in ArcGIS. 

[jcarlson]

I would recommend using SELECT DISTINCT together with ORDER BY to control the order in which your duplicates appear, if you need to consistently choose one result. Can you share the full query, though? If you're aggregating everything properly, there shouldn't be any duplicates.

[AndersMark]

I will try to explain:

I can't use distinct because every line in the view are correct which means it wouldn't be correct to use just one of the duplicate objectid:s. I.e. data would be missing. The view joins several tables inlucing one geometry table. The same geometry kan be used from more that one row in the view. This is the reason for duplicate object ids. The problem is that ArcGIS depends on that every geotable or geoview contains unique values for OBJECTID.  I a view doesn't fulfill that need, ArcGis will automatically aggregate duplicate objectid:s and create a multipart feature but with attribute values from just one of the aggregated records. When checking information about  one of those geometries you will see every geometry but only attribute values for one of them.  This leads to lack of information which is not preferred. 

Is the problem more clear now?

[Alvaro1977]

i have de same problem, you can resolve de issue 

[Bud]

@Alvaro1977 

What kind of database? Oracle? 

If not Oracle, then does your RDBMS have the equivalent of Oracle's ROWNUM pseudocolumn? 

[Alvaro1977]

yes, i use rownum in the query . 

select cast(rownum as number(38.0)) as objecid,
d.shape,

[AndersMark]

See first row - "I've run into a problem with an Oracle View..." so yes, it's Oracle. 

[Bud]

I've had success using Oracle's ROWNUM pseudocolumn as a fake/unique ObjectID when there are duplicate ObjectIDs in the query.

select
    cast(rownum as int) as rownum_,
    r.*
from
    my_user.roads r

But a couple of extra steps are required:

1. ArcGIS doesn't seem to recognize the ROWNUM values as integers. So I need to cast it as an int.
   1. Alternatively, cast it like this: cast(rownum as number(38,0)) as rownum_
2. When it comes to creating a view, Oracle doesn't like it when I name the column as ROWNUM, since that's a reserve word. I need to name it ROWNUM_ with an underscore (or something similar), or name it as OBJECTID.

Related: Converting double field to integer in database view so ArcMap query layer can use field as unique id...

[Alvaro1977]

Yes, it worked, 

[AngeloCirocco]

Hi all,

I would like to clarify for future readers that pseudo-columns such as ROWNUM should not be used as the unique identifier (OID) for ArcGIS views.

While values generated by ROWNUM may appear unique within the result set of a specific query, they are not stable identifiers. These values can change as a result of modifications to query predicates, sorting, joins, execution plans, or changes to the underlying data. Consequently, they do not reliably represent the same row over time and are therefore unsuitable for use as an OID field in ArcGIS.

As outlined in the Esri documentation, ArcGIS requires a unique identifier field to support feature selection, editing workflows, and other core operations. When working with database views, it is important to identify an attribute, or combination of attributes, that uniquely identifies each row. Where necessary, multiple fields can be combined and encoded into a unique integer value that can be exposed as the OID field for the view.

The most appropriate identifier will depend on the underlying data model, so data custodians should use their knowledge of the dataset to determine a stable, consistent, and unique value that can be represented as an integer and maintained over time. The key requirement is that the OID must uniquely identify a feature and continue to refer to the same feature across successive queries.

For further guidance, please refer to the Esri documentation:

https://doc.esri.com/en/arcgis-pro/latest/help/data/databases/unique-identifier-fields-in-database-t...

Thanks.