1

I have a spatial data model implemented in PostgreSQL / PostGIS with several alphanumeric and spatial tables. The spatial data is edit mainly through QGIS.

As I want to be able to edit the spatial data in ArcGIS environment and publish a Feature Service in ArcGIS Server, I am trying to implement a solution where I have a column as primary key (PK) managed by PostgreSQL and an OBJECTID column to function as PK in the ArcGIS environment managed by SDE (as solution proposed in: Feature Service from plain PostgreSQL/PostGIS in ArcGIS Server 10.2)

To be able to still edit the spatial data through QGIS I had to implement a trigger function that will fill the OBJECTID (SDE PK) field with the appropriate value. In order to do this, I am using the sde.next_rowid function, but the values returned by this function are different (much higher) than the ones attributed to new features created in ArcGIS environment, as if they were using different sequence numbers.

I know that sde.next_rowid function is using the ids defined in the [table_owner].i[sde_id_from_table_registry], but does anyone have idea where SDE is getting the ids for new features created in ArcGIS environment?


Example:

When I run the this SQL statement in PostgreSQL:

SELECT sde.next_rowid('bdsiguser', 'pg_npolicia_pt');

I get a value of 144150.

But, when I edit the same feature class (bdsiguser.pg_npolicia_pt) in ArcGIS and create a new feature, it gets the objectid 143754!

And the next oid values, through each of the methods would be sequencial, i.e., 144151 and 143755 accordingly.


Note: I am using ArcGIS 10.2.2 and PostgreSQL 9.2.2 with PostGIS 2.0.6 (ESRI's recommended PostgreSQL/PostGIS version for this ArcGIS version)

  • 9.2.2 is the minimum supported PostgreSQL version. It is recommended that you use the most recent stable build. It's not recommended to edit tables registered to a geodatabase outside of ArcGIS. If the values are different, you may have the wrong iN function, or the data may have been corrupted during testing. Please Edit the question to specify the values generated by the two access methods. – Vince Mar 14 '18 at 12:20
  • @Vince: The function that I am using to retrieve the next oid from SDE PK is sde.next_rowid, which in turn will use and update the iN table. I am not accessing this table directly, just checked which data it had and how it was updated. – Carlos MSF Mar 14 '18 at 13:12
  • Yes, that's the function, but you report it generates different values than features added by ArcGIS Server. What are those values? – Vince Mar 14 '18 at 13:23
  • @Vince: I've edited my question with an example – Carlos MSF Mar 14 '18 at 13:25
  • @Vince: After observing this behaviour, I've tested this in a feature class created in ArcCatalog and edited only in ArcGIS and there was also a discrepancy on the values returned by the two methods. – Carlos MSF Mar 14 '18 at 13:29
1

Apparently, this is a common case of "it is not a bug, it's a feature..." :)

The Vince's answer to this (ArcGIS sde i5,i6,i7 tables) question shed some light on the subject.

In order to cope with the slow performance perceived, during bulk loading operations, of database's SEQUENCE type, that was used to feed the unique identifier, ESRI came to implement, in some SDE DBMS implementations, an alternative system for managing the attribution of new OIDs. This would enable the reservation of a range of OIDs to be managed at chache level.

To support the OIDs attribution, auxiliary table and functions are created during the SDE registration of the table to manage OIDs. These table and functions are given names derived from the sde id attributed to the table in the sde.table_registry table (Assuming N as the sde id attributed to the table, there will be created the table iN and the functions iN_get_ids and iN_return_ids.

When editing in ArcGIS Desktop environment, if you enter the edition mode and start creating features, a range of OIDs will be reserved for the subsequent operations. Therefore, if you then request a new OID through the database using the sde.next_rowid function, it will return you the next (non reserved) OID, which will be a bunch of numbers after the last OID attributed to the last feature created in ArcGIS.

The so called reserved OIDs will expire after some time, originating some funny facts like described here, where you will experience some gaps in the sequence of OIDs attributed to new features, even if you only edit the layer in ArcGIS environment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.