Generating AddressIDs When Adding Geometry via the Web

[DanielSereno]

Aloha All,

Our addressing technicians (who are not GIS schooled/trained) maintain address points via a simple ArcGIS for Portal web mapping application. NOTE: I have not had time to research, implement and then train our technicians on how to efficiently use this solution: Address Data Management | ArcGIS Solutions for Local Government.

At any rate, we are now trying to update some of our enterprise systems and are in the middle of developing a new planning and permitting system that essentially brings quite a few gis layers together, parcels, streets, address points, etc. We now need to assign and maintain ADDRESSIDs and while this can be accomplished through Attribute Assistant (AA), as I previously mentioned, our technicians are not using ArcMAP and I am not aware of any way AA can be utilized in this instance.

We are a 10.6.1 deployment with 3 MSSQL 2012 SDE instances called Gisedit, Staging, and Production. Currently, the aforementioned web mapping application writes to the Gisedit instance and then nightly scripts update the data to Production. From Production, REST services are built to be consumed by our new enterprise planning and permitting solution.

So, the $64,000 question is how do you assign and maintain ADDRESSIDs when creating geometries via a web application? In other words, when a new record is inserted, via a browser, how do we assign a unique, non-repeating ID to each address point upon creation?

Mahalo!

Dan Sereno

County of Maui

Enterprise GIS Manager

[RobertScheitlin__GISP · Accepted solution]

Dan,

   Let me begin by saying this is not an easy task. The way I have seen people doing this is through a database trigger. Here are some threads on this subject:

Creating a sequential Unique ID when a new feature is added 

Auto-Increment Field within SDE 

https://www.arcgis.com/home/item.html?id=6997e32c54bd40ca8ca13e1fdaf5948b 

[DanielSereno]

Aloha Robert,

Thank you for the reply!

Utilizing the URLs you provided I was able to setup a sequence and trigger to generate an AddressID for each address point added via our web based addressing application.

Here are the two SQL statement that I ran (SITEADDRESSPOINT is the table where our addresses live and ADDPTKEY is the addressID field I was looking to populate):

CREATE SEQUENCE dsa.seq_addressid
START WITH 176000
INCREMENT BY 1

CREATE TRIGGER dsa.trg_insert_addressid
ON dsa.SITEADDRESSPOINT
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ID float
IF (SELECT INSERTED.ADDPTKEY FROM INSERTED) IS NULL
BEGIN
SELECT @ID = (NEXT VALUE FOR dsa.seq_addressid)
SELECT @ID
UPDATE dsa.SITEADDRESSPOINT
SET ADDPTKEY = @ID + 1
WHERE OBJECTID IN (SELECT OBJECTID FROM INSERTED)
END
END

Mahalo!

Dan Sereno

County of Maui

Enterprise GIS Manager