Question

Use Httpcaller to update feature in database based on incoming feature values

  • 10 February 2020
  • 3 replies
  • 10 views
K

Hello

I am using HttpCallers to populate fields in an ArcSDE database based on values from a Json feed. I have a case where incoming features (lets say feature 1) has two attributes 'References type' and 'References MessageID'.

"References type" would have values like "Update" or "Cancellation" and References MeesageID will have the ID of an older record that is already in the database (feature 2), and is affected by the new feature (feature 1) and the specifications in 'References Type'

So I need to make a call where I after filtering out features that have values in "References Type" column looks in the database to find Feature2.ID = Feature1.References MessageID. And then populates another field in feature 2 ("References") with values from "References type" & "ID" in feature 1.

I hope someone can help me how make this in FME preferably using httpCaller.

3 replies

A

Hi @kristianbj,

Could you provide a bit more details so we can best help you? What type of database (Microsoft SQL Server, Oracle, Postgres, etc.) are you working with? And to set the Request URL parameter in the HTTPCaller, are you looking to access data via ArcGIS REST API/ArcGIS Online feature services, or are you looking to create web services in other ways? Thank you!

 

K

Hi @alyssaatsafe

Im writing to MSSQL Server. Im trying to use http calls like this one:

$(WMX_url)/@Value(jobid)/extendedProperties/@Value(tableName)/@Value(recordId)/update

With the following private parameters:

 

And using post method to populate fields in the database:

 

So now im looking for a http call, that makes an update in a field of a feature based on a ID match with a new incoming feature, that has a reference to this old feature in a column "reference_messageID".

 

My own idea was first to use a tester to find all incoming features that have value in column "References Type", becomes if they have a value there they need to affect an older feature. Then I thought of looping through the existing data in the database (preferably using a HttpCaller transformer) to find match with ID from old feature being equal to value in column reference_messageID in new/incoming feature. If it finds a match between these two (oldfeature.ID=newfeature.referencemessageID) then it should populate a field in the old feature "References" with the ID value from the new feature + the type written in column "References Type" being either 'Update' or 'Delete' for an example.

But I cannot really make it work, and Im not that experienced with looping in FME.

 

Thanks so much for your time.

 

A

Hi @alyssaatsafe

Im writing to MSSQL Server. Im trying to use http calls like this one:

$(WMX_url)/@Value(jobid)/extendedProperties/@Value(tableName)/@Value(recordId)/update

With the following private parameters:

 

And using post method to populate fields in the database:

 

So now im looking for a http call, that makes an update in a field of a feature based on a ID match with a new incoming feature, that has a reference to this old feature in a column "reference_messageID".

 

My own idea was first to use a tester to find all incoming features that have value in column "References Type", becomes if they have a value there they need to affect an older feature. Then I thought of looping through the existing data in the database (preferably using a HttpCaller transformer) to find match with ID from old feature being equal to value in column reference_messageID in new/incoming feature. If it finds a match between these two (oldfeature.ID=newfeature.referencemessageID) then it should populate a field in the old feature "References" with the ID value from the new feature + the type written in column "References Type" being either 'Update' or 'Delete' for an example.

But I cannot really make it work, and Im not that experienced with looping in FME.

 

Thanks so much for your time.

 

Hi @kristianbj, thank you for providing the screenshots and explanation.

Is the http request in your example able to update individual records in the database based on recordId? I created a simplified example based on the process you described, and the actual parameter settings in the HTTPCaller may need adjustments depending on how the http request was set up to update the database. I have also attached the workspace here.

So I created a JSON file called feature1, with properties Reference type and Reference MessageID and a database table called feature2 with fields ID and References. In the workspace, first read feature1.json with JSON reader, followed by a Tester to test Reference type with “Attribute Has a Value”. Then connected the Passed port from the Tester to the HTTPCaller, where I had an HTTP request that would update the record matching to References MessageID, and update the References field with values specified in the Upload Body in the HTTPCaller. As the workspace processes data feature by feature, it would work as if it is looping. Each References MessageID would trigger one individual http request.

Hope this helps a bit. Just out of curiosity, is there a particular reason you prefer using HTTPCaller to update the database, instead of using SQL with SQLExecutor for example? :)

Reply


Community Stats

28,662
Posts
109,325
Replies
36,528
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.