topic Re: Duplicate objectid in Views - Best practise to solve? in Geodatabase Questions

Duplicate objectid in Views - Best practise to solve?

AndersMark — Thu, 09 Feb 2023 12:55:28 GMT

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.

Re: Duplicate objectid in Views - Best practise to solve?

jcarlson — Thu, 09 Feb 2023 14:36:38 GMT

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.

Re: Duplicate objectid in Views - Best practise to solve?

AndersMark — Mon, 20 Feb 2023 12:59:24 GMT

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?

Re: Duplicate objectid in Views - Best practise to solve?

Alvaro1977 — Thu, 18 Jan 2024 14:10:07 GMT

i have de same problem, you can resolve de issue

Re: Duplicate objectid in Views - Best practise to solve?

Bud — Thu, 18 Jan 2024 15:42:20 GMT

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:

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_
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.

Re: Duplicate objectid in Views - Best practise to solve?

Bud — Thu, 18 Jan 2024 15:41:14 GMT

@Alvaro1977

What kind of database? Oracle?

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

Re: Duplicate objectid in Views - Best practise to solve?

Alvaro1977 — Thu, 18 Jan 2024 15:47:49 GMT

Yes, it worked,

Re: Duplicate objectid in Views - Best practise to solve?

Alvaro1977 — Thu, 18 Jan 2024 15:49:17 GMT

yes, i use rownum in the query .

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

Re: Duplicate objectid in Views - Best practise to solve?

AndersMark — Mon, 22 Jan 2024 20:51:34 GMT

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