Populate a SDE Feature Class from an independent SQL Database

[LoganCaraway]

We have real time data that is collected  in the field that then lives on a SQL database. I have a sde point feature class (I believe its on a separate server) for the corresponding data. My question is, what is the best way to link these together so that the sde FC is always updated with what was recorded in the field without interrupting services i.e. the web map the sde data is hosted on as well as other applications that use the same data.

One solution I have thought of would be to have a script that dumps the SQL data into a csv at the desired time increments and then do a simple table join with the  feature class. It has been expressed to me that it would be more desirable to reduce the moving parts (and reliability upon a csv) and attempt a SQL to SQL/SDE transaction if at all possible.

We are on 10.2

Any thoughts would be appreciated!

-L

[JoeBorgione]

When it comes to making front end edits to a sql database that is accessed via SDE, my thoughts are buyer beware.  You've got another thread on a similar topic, and I think the answer provided to the OP is a good one. IMHO you would be better served by updating your SDE data via ArcGIS.

I use SDE to manage an enterprise database that includes a street centerline feature class. However, I replicate the SDE feature class(es) to a production file geodatabase that several published services point to, including geocoding services used in 9-1-1 dispatch.  For me and my application, separating the edit data (field data) from the production (consumed) data works quite nicely.

With respect to your question about interrupting services that view the data: in order for those services to see any changes, you'll need to refresh the data by restarting the service.  In my case, I have two different services; when one is hot, the other is not.  That is, at any given time only one service directory is being accessed and the data therein consumed.  This way I can shut down the 'cold' service, do my replication process, and rebuild any locators or map services without interrupting the users.  Then I restart the cold service and point my users to it, so now it's hot and the other is not; repeat the rebuild process for the now-cold services so both are current.

[Anonymous User]

Original User: vangelo

Best practice for a SQL solution would be to use the native Geometry datatype
in a simple feature class where your SQL code updates the rowid column with
a sequence (or the SQL-Server equivalent thereof).  You can therefore have
realtime updates without requiring service restart.

The fallback is to use an ArcSDE client application to update the ArcSDE-registered
SDEBINARY storage layer. The se_toolkit 'asc2sde' and 'sdeupdate' command-line
utilities were designed to do ASCII update of ArcSDE layers, and will also insert/
update into a table that is running a live service.  The drawback to these tools is
the deprecation of the ArcSDE 'C' API -- they will not be available for post-10.2
geodatabases (though I'm working on a Python replacement).

Please keep in mind that realtime data feeds are notorious for poor spatial query
performance, due to the high degree of spatial fragmentation.

- V

[JoeBorgione]

Hey Vince- will the current approach work with versioned feature classes?  And will your new approach work for one?

[Anonymous User]

Original User: vangelo

Some of the se_toolkit tools can query versioned tables, but none of them can
insert or update into versions (though I started some edits that would allow
'sdeupdate' to operate in a large chain of change states).  In general, a realtime
insert application isn't likely to be versioned, so we're talking about a very
different use paradigm.

I'm not prepared to talk about the Python port until after I see how it works out.

- V

[CyrilCherian]

I would suggest to create a simple windows service that run 24/7. This service will read data from SQL Server database and use ArcObjects to update SDE featureclass. Once you are using ArcObjects, it is easy for you to update versioned or un-versioned geodatabse.