Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have a Python script which runs on a SDE feature class daily (as a scheduled task). I would actually like this script to run whenever a new row is added to the feature class (data is captured in the field using a mobile device and posted to the server).

I was considering using a trigger as mentioned in this question as my data is not versioned, but I do not want to overcomplicate things if there is an inbuilt SDE tool which can achieve this or something similar. The data is currently stored in ArcSDE 10/SQL Server 2008, but I do have ArcSDE 10.1/SQL Server 2012 available.

Currently when the script runs, it checks for NULL values in a certain field. If it finds it, it selects those rows and performs several geoprocessing operations on them. It then populates that field with a value to show that everything has been processed.

While I have a lot of experience editing/reconciling/posting/versioning SDE data, I only have limited experience with the database/admin side of it (assigning privileges, basic dabbling in SQL management studio), with a basic understanding of the underlying architecture.

share|improve this question
1  
Even if it were possible, this sounds very inefficient. I think your approach of batch updating rows on a schedule makes a lot more sense. –  blah238 Jun 3 '13 at 7:42
    
@blah238 agreed. –  Cindy Williams Jun 3 '13 at 8:54

1 Answer 1

up vote 1 down vote accepted

First of all: No, SDE does not have any built in feature of event triggered processing. As far as I know you can probably do something working with the CASE Tools where you can implement your own types of featureclasses with their own behaviour, but that means ArcObjects programming.

I think database triggers can be used for this, and this wouldn't be too complicated, but as mentioned here calling a rather big piece of software (like a python/arcpy script) out of a database trigger (meant to be minimalistic) brings you a couple of things, you should at least think of:

  1. Every INSERT fires the trigger. So 5 rows inserted means that in the worst case, your python script is started 5 times. You possibly have to prevent that by some mechanism.
  2. Can you solve the task of filling those NULL Values natively in SQL? Then this would be done much more efficient, because SQL Server would manage concurrency issues.
  3. If you really need to run a Python script, then on which machine is the Interpreter running? On the database server means, that you can call it with a system call out of that trigger. If its on a remote machine, you probably need ArcGIS Server to publish the script as a geoprocessing tool and call it via http (asynchron).
  4. As mentioned in 1. you need a mechanism that ensures, that the script only runs when it really should run.

I guess the best will be, to keep your script running periodically, but to shorten the time period to only some minutes. Add a table where you can store flags which indicate a change in your Featureclass. Use a database Trigger to set the "changed" flag. Modify your script to run only if the changed flag is set, or if not then simply quit.

This way, your script will fill your data in short periods of time without running into raceconditions.

You can read here how you activate and use system calls in sql server triggers (although the example is for PHP, but thats just anoter interpreter).

Also read through all disadvantages the authors are mentioning.

share|improve this answer
    
I like the idea of a script checking the flag status before running the other tools. I think I will go with the idea of running the script more frequently (say, once every 2 hours instead of only once a day), and the check will prevent it from running the entire script unnecessarily. –  Cindy Williams Jun 3 '13 at 8:53
1  
Thats how we do most of our data maintenance. We have a single job table which is polled every quarter hour for things/jobs to do, and then calls the python function associated with that job. Due to the fact that ESRI does not allow long running transactions without taking the database offline, thats not the most elegant but the most responsive way to get your data up to date. Alternatively we are trying to get spatial functions into the database (as CLR type implementations) and do the jobs there (where transaction contexts and triggers are available). –  Jürgen Zornig Jun 3 '13 at 12:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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