Updates to ArcSDE via Oracle trigger

Question

Is it possible and/or feasible for an Oracle trigger to periodically update ArcSDE via a REST geoprocessing service?

Does anyone have any experience with such a thing?

The reason for this is that I have an Oracle database that is separate from the SDE database, and there are number of business rules that we want to run first, before inserting the geometry into SDE.

So the basic workflow would be:
1. Point features via ArcGIS Server via ArcSDE loaded onto web map
2. User moves point or edits attributes
3. Edits to points first get written to Oracle, then, via trigger to ArcSDE

• Dowlers: Would it be possible to work the other way? Place the trigger on the SDE table and carry out your business logic there? The trigger could easily access/update the second non spatial database. I would consider this approach because I'm not sure how feasible it is to have a map service edit fire a trigger on a non SDE database without also having a trigger on SDE

Answer 1

Oracle database can access web-based services (incl. REST) via built-in UTL_HTTP package.
You formulate & set-up the request, send it and receive & process the response.

I don't have any experience with the package, but your workflow seems to be doable.

Update
The code could be like this:

DECLARE
 req utl_http.req;
 resp utl_http.resp;
 name VARCHAR2(256);
 value VARCHAR2(1024);
BEGIN
 utl_http.set_proxy('proxy.my-company.com', 'corp.my-company.com'); 
 req := utl_http.begin_request('http://www.my-company.com/rest/process');
 utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');
 resp := utl_http.get_response(req);
 LOOP
 utl_http.read_line(resp, value, TRUE);
 dbms_output.put_line(value);
 END LOOP;
 utl_http.end_response(resp);
EXCEPTION
 WHEN utl_http.end_of_body THEN
 utl_http.end_response(resp);
END;

You just wrap this into you trigger.

Answer 2

Have you considered using ArcGIS Feature Services?

• yalestar: Hmmm... not sure how that would help exactly?

Answer 3

What database platform is the ArcSDE database hosted on ?

Using Oracle and SDO_GEOMETRY as the data type this could work with the workflow below, but you need to take over management of the layer yourself rather than letting ArcSDE do this for you otherwise ArcSDE will get confused by the direct updates to the business table.

As suggested by @ujjwalesri you may find it easier to use a Feature Service to edit the features directly, or investigating the use of Spatial Views if that is possible in your architecture.

Pre-requisites

• A SQL data type is used to store the geometries in the layer, for example SDO_GEOMETRY or ST_GEOMETRY. SDEBINARY will be difficult to work with as the geometry isn't stored in the business table for your layer so isn't easy to update with a trigger.
• ArcSDE Layer is registered with USER maintaining OBJECTID column (see http://help.arcgis.com/en/geodatabase/10.0/admin_cmds/support_files/datamgmt/sdelayer.htm), or
• The layer is made available thorugh ArcGIS as a query layer.

Update Workflow

1. Data update on Oracle database, fires trigger to update geometry
2. Trigger updates/inserts deletes on business table for layer in remote SDE database
3. ArcSDE sees updated records in layer

If you are wishing to make these kind of direct updates to layers from the database side then you are really only using ArcSDE like a viewer interface to see the spatial data held in your database tables. Once you start updating spatial information directly from the database then it's no longer trivial to edit this using the ArcGIS/ArcSDE versioning and editing stack.