Translate a SQL update to a Velocity Output --

JeffSilberberg

I have a task that I am not sure how to achieve if it's even possible in Velocity.. 

So, there is a FEED into a complex Analytic that has multiple Feature Layer and Stream Layer Outputs.  

Now, there is a new FEED the contents of which are four simple data elements.  What I am looking to do is update one of the original Feature Layers created in the first analytic that has about 60 data elements but only a single data element should be changed with data from the new feed and it would be based on the Primary Key (Track Field). 

If this were a SQL data set a simple UPDATE FL.Vehcile t1 SET State = t2.state FROM Feed.presence t2 WHERE t1.VehicleID = t2.VehicleID  might be all that's needed or an INSERT .......... On CONFLICT UPDATE ........

I just don't think I have ever had an output NODE that was not a full INSERT instead of an UPDATE.  

Hoping someone can point me at some documentation I have not found yet. 

TIA -- 

 

 

Nicole_Phaneuf

Hey @JeffSilberberg 

The workflow for achieving this will depend on if the Feature Layer you are looking to update has a Start Time, End Time, and/or geometry specified. If any of these key fields and/or geometry are specified and the new feed has the same key fields/geometry type, we can directly update the output feature layer using a partial schema if we are using the Feature Layer (existing) output's option to "Keep only latest feature for each Track ID value."

For example, I have a Feature Layer output that has Track ID and Start Time values defined with Point Geometry. This feature layer has the following schema:

I want to update a few (but not all) of the fields in my feature layer's schema. I've created a new HTTP Poller input that has the same Track ID and Start Time fields as well as the same point geometry. This source also includes a subset of my feature layer's fields.

If I connect this new HTTP Poller feed or data source to a Feature Layer (existing) output set to "Keep only latest feature for each Track ID value" in a real-time or big data analytic, I note that there are no validation errors even though the pipeline's schema does not exactly match the feature layer's schema. When the analytic is run, existing features (identified by Track ID) are updated to use the new attribute values from the HTTP Poller input. Any fields that were not explicitly included in the HTTP Poller's schema will retain their original values.

In the situation that the feed/data source I want to update does not have the same key fields and geometry type as my feature layer, I could use the Join Features and Map Fields tools to achieve a similar outcome by using the join to populate incoming records from the new input with the appropriate key field/geometry values.

For example, I may want to update some attributes in a feature layer that has a Point geometry type, but my new data source only has a partial schema without geometry. In this case, we would see a validation error if we try to connect the input to our feature layer output, as the geometry types do not match. I can work around this using a Join Features and Map Fields tool. 

On the Target port of the Join Features tool, I've connected my new HTTP Poller feed which has a partial schema update (no geometry). On the Join port, I've connected the feature layer that I want to update so that I can get the geometry for each Track ID and join it to the data from the HTTP Poller feed (note that I've set the "Change geometry of target feature to geometry of join feature" parameter to True. If we were missing other key fields, we could add them in this join as well). After the join, I use a Map Fields tool to remove the COUNT field created by the Join Features tool before the Feature Layer (existing) output so that the schemas match. Now that the pipeline includes the correct geometry, I can run this analytic as expected to update my feature layer. We are considering future enhancements to expand this workflow to allow attribute edits without also providing the geometry.