Auto incrementing ID field alternative to ObjectID

2419
4
05-29-2014 07:01 AM
Highlighted
New Contributor
I am looking for suggestions for how to implement an auto-incrementing ID field as an alternative to ObjectID in an Arcgis geodatabase 10.2.1 and SQL server 2008. We have learned the lesson to not use ObjectID as a feature identifier on the user end of projects because the ObjectID will not be retained if we need to migrate data.

I know it is not possible to use the Identify function in SQL on a versioned dataset. We are exploring using a trigger on the adds table to auto generate based on the previous record, but I am not sure the logic will work. Has anyone had any success creating a separate auto generating ID field?

For our project this needs to happen on the backend in the SQL server because we have both a mobile app with offline editing, and also a web app all editing the same data source.
Reply
0 Kudos
4 Replies
Highlighted
New Contributor II
Jill,
Did you ever find a solution for this?
-Mike Ross
Reply
0 Kudos
Highlighted
New Contributor
We are still trying to figure it out. Our dba is looking into the trigger on the adds table. Another idea we had is to create a related table with an identity field, joined to a versioned view using the globalID, populating our ID field in the versioned view with the value from the identity field. We are still in early development with this but I will update this post after we test some of our ideas.
Reply
0 Kudos
Highlighted
New Contributor III
Reply
0 Kudos
Highlighted
MVP Frequent Contributor
The following assumes you have a unique ID field (SQL, GUID in SDE), that in SQL is set to "newsequentialid()". Lets assume the field is called "LocationID". Note how that column is NOT INCLUDED in this trigger! In the Adds table, it is a NON-SEQUENTIAL ID, but when it hits the business table, it becomes sequential. The rest of the trigger deals with using SQL geometry operations to autopopulate some columns. This will ONLY work on data that is versioned with move edits to base checked, which kills any replication plans you have. If you can't move edits to base, you'll need to wrap this up in a stored proc using a cursor, which IMHO was more work than it's worth.




CREATE TRIGGER [dbo].[INSERT_VM_PLOTS_Location_ID]
ON [dbo].[VM_PLOTS]
INSTEAD OF INSERT NOT FOR REPLICATION
AS BEGIN
SET NOCOUNT ON;
INSERT dbo.VM_PLOTS(
OBJECTID, VError, MapSource, SourceDate, MapMethod, QuadName, UnitCode, EditDate, UnitName, IsExtant, HError, County, State, LAT, LON, Watershed, PlaceName, TrailName, StreamName, Landform, Meta_MID, Road, Elevation, Year, Notes, Coord_Units, Coord_System, Datum, UTM_Zone, ManagementZone, ParkDistrict, EditBy, CreateBy, CreateDate, RegionCode, Restriction, Soil, Geology, HistoricDisturb, Veg, Level4EcoReg, AvgRain, MeanDailyTemp, Loc_Name, Directions, Description, Loc_Notes,X_Coord, Y_Coord, Shape
)
SELECT
a.OBJECTID, a.VError, a.MapSource, a.SourceDate, a.MapMethod, b.NAME, a.UnitCode, a.EditDate, a.UnitName, a.IsExtant, a.HError, c.NAME, d.NAME, a.LAT, a.LON, e.HU_12_Name, a.PlaceName, a.TrailName, a.StreamName, a.Landform, a.Meta_MID, a.Road, a.Elevation, a.Year, a.Notes, a.Coord_Units, a.Coord_System, a.Datum, a.UTM_Zone, g.FCSubtype, f.District, a.EditBy, a.CreateBy, a.CreateDate, a.RegionCode, a.Restriction, j.MUNAME, k.GLG_SYM, l.CLASSES, h.VitalName, p.EPA_REGION, q.RANGE, a.MeanDailyTemp, a.Loc_Name, a.Directions, a.Description, a.Loc_Notes,a.X_Coord, a.Y_Coord, a.Shape
From
(SELECT
OBJECTID, VError, MapSource, SourceDate, MapMethod, QuadName, UnitCode, EditDate, UnitName, IsExtant, HError, County, State, LAT, LON, Watershed, PlaceName, TrailName, StreamName, Landform, Meta_MID, Road, Elevation, Year, Notes, Coord_Units, Coord_System, Datum, UTM_Zone, ManagementZone, ParkDistrict, EditBy, CreateBy, CreateDate, RegionCode, Restriction, Soil, Geology, HistoricDisturb, Veg, Level4EcoReg, AvgRain, MeanDailyTemp, Loc_Name, Directions, Description, Loc_Notes,
SHAPE = CASE WHEN SHAPE IS NOT NULL  
        THEN SHAPE ELSE Geometry::STPointFromText('POINT(' 
          + CAST(X_Coord AS VARCHAR(20)) + ' '  
          + CAST(Y_Coord AS VARCHAR(20)) + ')', 26917) END, 
      X_Coord = CASE WHEN SHAPE IS NULL THEN X_Coord ELSE SHAPE.STX END, 
      Y_Coord = CASE WHEN SHAPE IS NULL THEN Y_Coord ELSE SHAPE.STY END
FROM inserted)
AS a
LEFT OUTER JOIN grsm.dbo.USGS_24K_TOPOMAP_BOUNDARIES AS b
ON b.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN grsm.dbo.GRSM_COUNTIES AS c
ON c.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN grsm.dbo.GRSM_STATES AS d
ON d.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN grsm.dbo.WBD_12 AS e
ON e.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN grsm.dbo.RANGER_DISTRICTS AS f
ON f.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN IANDM.dbo.GRSM_VEG AS h
ON h.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN grsm.dbo.GRSM_GMP_ZONE AS g
ON g.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN iandm.DBO.GRSM_Soil_Taxonomy AS j
ON j.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN IANDM.dbo.grsmglg AS k
ON k.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN IANDM.dbo.GRSM_DISTURBANCE_HISTORY AS l
ON l.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN IANDM.dbo.GRSM_LEVEL_IV_ECOREGIONS AS p
ON p.Shape.STContains(a.Shape) = 1
LEFT OUTER JOIN IANDM.dbo.AVERAGERAINFALL as q
ON q.Shape.STContains(a.Shape) = 1;
end



I take it one step further and get this all going in SQL/SDE, without declaring a globalID in Arc Cat, then change the object flags on the "LocationID" column in sde_column_registry to "256", thus giving myself a sequential globalid. The difference in performance is immediate and drastically improved.
Reply
0 Kudos