Create New Field w/ Field Calculator (Regular Expression) between :

4182
17
07-28-2016 10:52 AM
Mtclimber03
Occasional Contributor

Hey guys,

I'm not sure if using the field calculator (regular expression) is the function in GeoEvents I should be using to accomplish what I need so let me try and explain. I have geoevents recieving a JSON REST endpoint that contains data that looks like/similar to the below.

{

"record" : {

  "ID" : "18",

  "PARENT_RECORD_ID" : "0",

  "PARENT_PAGE_ID" : "0",

  "PARENT_ELEMENT_ID" : "0",

  "CREATED_DATE" : "2016-06-29 17:43:32",

  "CREATED_BY" : "username",

  "CREATED_LOCATION" : "36.863681:42.992592:557.165466:65.000000:18.446595:-1.000000:-1.000000:1467211407.825839",

  "CREATED_DEVICE_ID" : "917f86ddc676fa0ef39f910c6460838d4451f0b8",

  "MODIFIED_DATE" : "2016-06-29 17:43:34",

  "MODIFIED_BY" : "username",

  "MODIFIED_LOCATION" : "36.863681:42.992592:557.165466:65.000000:18.446595:-1.000000:-1.000000:1467211407.825839",

  "MODIFIED_DEVICE_ID" : "917f86ddc676fa0ef39f910c6460838d4451f0b8",

  "SERVER_MODIFIED_DATE" : "2016-06-29 17:43:38",

  "project_number" : "8675309j",

  "project_name" : "name of project",

  "project_nickname" : "nickname",

  "date_of_report" : "2016-06-29",

  "data_type" : "completed_activity",

  "admin_div_1" : "some_governorate",

  "admin_div_2" : "Any_Town",

  "admin_div_3" : "Any_Loc",

  "admin_div_4" : "Any_where",

  "a99999_benev_funding" : 204,

  "a99999_benev_funding_male" : 0,

  "a99999_benev_funding_female" : 204,

  "a91602" : 85,

  "a91602_male" : 28,

  "a91602_female" : 57,

  "a99999_children_counseling" : 16,

  "a99999_children_counseling_male" : 6,

  "a99999_children_counseling_female" : 10,

  "a99999_hhheads_seminars" : 0,

  "a99999_hhheads_seminars_male" : 0,

  "a99999_hhheads_seminars_female" : 0,

  "a99999_cohesion_activities" : 47,

  "a99999_cohesion_activities_male" : 18,

  "a99999_cohesion_activities_female" : 29,

  "a90107" : 31,

  "a90107_male" : 15,

  "a90107_female" : 16,

  "a90309" : 97,

  "a90309_male" : 39,

  "a90309_female" : 58,

  "a90308" : 12,

  "a90308_male" : 6,

  "a90308_female" : 6,

  "a99999_leaders_trained" : 0,

  "a99999_leaders_trained_male" : 0,

  "a99999_leaders_trained_female" : 0,

  "a99999_cfs" : 40,

  "a99999_cfs_male" : 20,

  "a99999_cfs_female" : 20,

  "a50200_health_services" : 366,

  "a50200_health_services_male" : 128,

  "a50200_health_services_female" : 238,

  "a50200_dental" : 0,

  "a50200_dental_male" : 0,

  "a50200_dental_female" : 0,

  "a50200_obgyn" : 76,

  "a50200_obgyn_male" : 0,

  "a50200_obgyn_female" : 76,

  "a99999_emergency" : 60,

  "a99999_emergency_male" : 30,

  "a99999_emergency_female" : 30,

  "data_submission_status" : "validated",

  "assign_to" : "username"

  },

From here I need to use the record.CREATED_LOCATION coordinates to generate two new fields for latitude and longitude that can be used for the geometry. The 1st two sections of the record.CREATED_LOCATION are the x:y. Can some one point me in the right direction for what needs to be done to achieve this? Or alternatively let me know if there is another place in the geoevents manager I should be setting this up? So far I have my JSON feed successfully passing records into a feature service, just not mapping the points.

0 Kudos
17 Replies
AlexanderBrown5
Occasional Contributor II

Charlie,

The field calculator (regular expression) is exactly what you need to help parse through your JSON and extract the proper coordinates.  Since I do not have access to your JSON REST endpoint, I took your example record and utilized the "Watch a Folder for New JSON Files" connector.

1. Create a new GeoEvent definition for your input (copy the auto generated definition from the first time you recorded a GeoEvent through the REST endpoint).  My original input I called JSON_IN:

     Add three fields to your new copy:

    • Coords (String)
    • x (Double)
    • y (Double)

My new output definition I called JSON_IN-copy (new fields at the end)

2.  Begin to extract coordinates and calculate geometry:

    • Field Mapper -- Map original GeoEvent Definition to the new one
      • Name: fm
      • Processor: Field Mapper
      • Source: JSON_IN (your original input)
      • Target: JSON_IN-copy (your modified output with three fields).
      • Make sure your three new fields are included at the bottom:

                        

    • Field Calculator (Regular Expression) -- Process the Latitude/Longitude from original JSON
      • Name: Pull Coordinates
      • Processor: Field Calculator (Regular Expression)
      • Field Name: ${CREATED_LOCATION}
      • Pattern: ^(\d*.\d*):(\d*.\d*)
      • Target Field: Existing Field
      • Existing Field Name: Coords
        • Sample value written to Coords field:  36.863681:42.992592

    • Field Calculator (Regular Expression) -- Calculate X  - Search from the beginning of the Coords field value for all characters prior to a colon.
      • Name: x_calc
      • Processor: Field Calculator (Regular Expression)
      • Field Name: ${Coords}
      • Pattern: ^(\d*.\d*)   or   ^[^:]+
      • Target Field: Existing Field
      • Existing Field Name: x
        • Sample value: 36.863681

    • Field Calculator (Regular Expression) -- Calculate Y
      • Name: y_calc
      • Processor: Field Calculator (Regular Expression) - Locate value before a colon (from end of the Coords field)
      • Field Name: ${Coords}
      • Pattern: (\d*.\d*)$  or   [^:]+$
      • Target Field: Existing Field
      • Existing Field Name: y
        • Sample Value: 42.992592

    • Field Calculator - Process Geometry - Combine values of X, Y into a geometry field
      • Name: Process Geometry
      • Processor: Field Calculator
      • Expression: '{"x":'+ x +',"y":'+ y +'}'
      • Target Field: New Field
      • New Field Name: Geometry
      • New Field Type: GEOMETRY
      • New GeoEvent Definition Name: Final_EXAMPLE

That process will output:

You can now see the fields at the end including the geometry in " ".  With a geometry assigned, you should be able to output to a Feature Service.   I have a hunch that you can accomplish this in maybe a step or less, depending on your experience level with regular expressions.   However, this worked just fine for me.

Let me know if you have any questions.

Regards,

Alex

Mtclimber03
Occasional Contributor

Hi Alexander,

Thanks for this sample. I have it about 99% working but I think it is getting hung up somewhere in the service. Maybe the last step? In my monitor page I can see that I have data coming in and the input also indicates this. However, I am not getting an output. See screenshots attached below. Any ideas where this might be getting hung up?

screenshots

0 Kudos
AlexanderBrown5
Occasional Contributor II

Hi Charlie!

I am glad you are 99% there.  The issue is within your Pattern*: inputs for your x_calc and y_calc.

For the pattern in x_calc:

 

These are two different regular expressions:

^(\d*.\d*)  

^[^:]+

I put both so you can choose one or the other (same result).  Since you have the whole expression "^(\d*.\d*)   or   ^[^:]+"  this will not work.

The same for your y_calc

These are two different regular expressions:

(\d*.\d*)$

[^:]+$

That is my bad for not making that clearer.  So for each pattern in x_calc and y_calc just choose one of the two options.

Here is a tip of what I generally do to help with these type of issues:

Set up an external TCP socket (you can see the tutorial https://www.arcgis.com/home/item.html?id=b6a35042effd44ceab3976941d36efcf  (Module 1.pdf : page 42).  I will then add the output GeoEvent service and methodically write out each step.

For example:

1.   dart_cross_sector_assessment_retaken_villages-in   =>  FM Liberated Villages (Field Mapper) => TCP Output

         A. Look at the results through the TCP socket window to ensure the output is correct (fields)

2. dart_cross_sector_assessment_retaken_villages-in   =>  FM Liberated Villages (Field Mapper) => Pull Coordinates (Field Calculator Regular Expression) => TCP Output

        A. Look at the results through the TCP socket window (check fields and values)

So on until you get to the end.  This always helps me determine if there is a problem with one of the processors.  This type of workflow will help you determine which processor to look at in more detail.

I am interested in your responses to my questions above, especially the GeoEvent logs.

~Alex

0 Kudos
Mtclimber03
Occasional Contributor

Hi Alexander,

Thanks for the reply. Here is what I received in the logs immediately following the submission of a datapoint into the receiver following the change you mention above to my x_calc and y_calc expressions. I haven't looked into the TCP socket tool yet.

org.apache.cxf.jaxrs.utils.JAXRSUtilsProblem with writing the data, class java.lang.String, ContentType: application/jsonJan 5, 2017, 9:47:03 AMERROR
com.esri.ges.messaging.jms.MessagingImpl[GeoEvent-messaging] An unexpected error occurred while trying to receive a message. Output may not have processed message. Error: null.Jan 5, 2017, 9:47:00 AMERROR
org.apache.camel.processor.DefaultErrorHandlerFailed delivery for (MessageId: ID-SPAWS-GIS01-51368-1481222371083-0-169482 on ExchangeId: ID-SPAWS-GIS01-51368-1481222371083-0-169489). Exhausted after delivery attempt: 1 caught: com.esri.ges.manager.geoeventdefinition.GeoEventDefinitionManagerException: GeoEventDefinitionManager failed to add temporary GeoEventDefinition: GeoEventDefinition GeoCalc_dart_cross_sector_assessment_retaken_villages has duplicate fields.. Message History --------------------------------------------------------------------------------------------------------------------------------------- RouteId ProcessorId Processor Elapsed (ms) [355896db-7647-4fa2] [355896db-7647-4fa2] [direct://355896db-7647-4fa2-8940-2abb83e54c41 ] [ 198] [355896db-7647-4fa2] [bean39 ] [bean[ref:inboundStreamRecipientListManagerBean method: getRecipients] ] [ 199] [c0c70da9-c932-4d77] [process115 ] [Processor@0x49cc9dee ] [ 2] [c0c70da9-c932-4d77] [multicast111 ] [multicast ] [ 197] [c0c70da9-c932-4d77] [to112 ] [direct:c0c70da9-c932-4d77-8433-2bea056ceb70:fe4122c4-bc3f-4c0a-84ad-c0edae8bf2] [ 30] [c0c70da9-c932-4d77] [process113 ] [Processor@0x5244068 ] [ 0] [c0c70da9-c932-4d77] [multicast109 ] [multicast ] [ 29] [c0c70da9-c932-4d77] [to110 ] [direct:c0c70da9-c932-4d77-8433-2bea056ceb70:19c1deda-9b2c-4204-8773-b97db40500] [ 4] [c0c70da9-c932-4d77] [process112 ] [Processor@0x704fbe08 ] [ 1] [c0c70da9-c932-4d77] [multicast108 ] [multicast ] [ 3] [c0c70da9-c932-4d77] [to109 ] [direct:c0c70da9-c932-4d77-8433-2bea056ceb70:0e4661c2-9ab4-41d7-8966-f17413613d] [ 3] [c0c70da9-c932-4d77] [process110 ] [Processor@0x3aec617 ] [ 0] [c0c70da9-c932-4d77] [multicast106 ] [multicast ] [ 3] [c0c70da9-c932-4d77] [to107 ] [direct:c0c70da9-c932-4d77-8433-2bea056ceb70:3d22739d-cd0e-4f40-803b-a05e7e0d8a] [ 2] [c0c70da9-c932-4d77] [process111 ] [Processor@0xf62a140 ] [ 0] [c0c70da9-c932-4d77] [multicast107 ] [multicast ] [ 2] [c0c70da9-c932-4d77] [to108 ] [direct:c0c70da9-c932-4d77-8433-2bea056ceb70:4eb7cbdb-dfdd-4259-8d30-3bdef4ebbf] [ 3] [c0c70da9-c932-4d77] [process114 ] [Processor@0x533e4e68 ] [ 1] Exchange --------------------------------------------------------------------------------------------------------------------------------------- Exchange[ Id ID-SPAWS-GIS01-51368-1481222371083-0-169489 ExchangePattern InOut Headers {breadcrumbId=ID-SPAWS-GIS01-51368-1481222371083-0-169482, CamelRedelivered=false, CamelRedeliveryCounter=0} BodyType com.esri.ges.messaging.jms.GeoEventImpl Body 7b90d774-bb09-4ebd-af0e-7580bb1382da,record : null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,[],null ] Stacktrace --------------------------------------------------------------------------------------------------------------------------------------- com.esri.ges.manager.geoeventdefinition.GeoEventDefinitionManagerException: GeoEventDefinitionManager failed to add temporary GeoEventDefinition: GeoEventDefinition GeoCalc_dart_cross_sector_assessment_retaken_villages has duplicate fields.. at com.esri.ges.manager.geoeventdefinition.internal.GeoEventDefinitionManagerImpl.addTemporaryGeoEventDefinition(GeoEventDefinitionManagerImpl.java:142)[369:com.esri.ges.manager.internal-geoeventdefinitionmanager:10.4.0] at Proxycddc1bf4_eb8e_4ac1_bfb1_e69ad6bf4414.addTemporaryGeoEventDefinition(Unknown Source)[:] at com.esri.ges.processor.fieldcalculator.FieldCalculator.lookup(FieldCalculator.java:166)[320:com.esri.ges.framework.processor.fieldcalculator-processor:10.4.0] at com.esri.ges.processor.fieldcalculator.FieldCalculator.process(FieldCalculator.java:132)[320:com.esri.ges.framework.processor.fieldcalculator-processor:10.4.0] at com.esri.ges.processing.camel.GeoEventServiceRouteBuilder$4.process(GeoEventServiceRouteBuilder.java:391)[282:com.esri.ges.framework.processing.camel-processing:10.4.0] at org.apache.camel.processor.DelegateSyncProcessor.process(DelegateSyncProcessor.java:63)[180:org.apache.camel.camel-core:2.15.1] at org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:77)[180:org.apache.camel.camel-core:2.15.1] at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:448)[180:org.apache.camel.camel-core:2.15.1] at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:191)[180:org.apache.camel.camel-core:2.15.1] at org.apache.camel.processor.Pipeline.process(Pipeline.java:118)[180:org.apache.camel.camel-core:2.15.1] at org.apache.camel.processor.Pipeline.process(Pipeline.java:80)[180:org.apache.camel.camel-core:2.15.1] at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:191)[180:org.apache.camel.camel-core:2.15.1] at org.apache.camel.component.direct.DirectProducer.process(DirectProducer.java:51)[180:org.apache.camel.camel-core:2.15.1] at org.apache.camel.processor.SendProcessor.process(SendProcessor.java:129)[180:org.apache.camel.camel-core:2.15.1] at org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:77)[180:org.apache.camel.camel-core:2.15.1] at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:191)[180:org.apache.camel.camel-core:2.15.1] at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:448)[180:org.apache.camel.camel-core:2.15.1] at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:191)[180:org.apache.camel.camel-core:2.15.1] at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:109)[180:org.apache.camel.camel-core:2.15.1] at org.apache.camel.processor.MulticastProcessor.doProcessParallel(MulticastProcessor.java:736)[180:org.apache.camel.camel-core:2.15.1] at org.apache.camel.processor.MulticastProcessor.access$200(MulticastProcessor.java:83)[180:org.apache.camel.camel-core:2.15.1] at org.apache.camel.processor.MulticastProcessor$1.call(MulticastProcessor.java:304)[180:org.apache.camel.camel-core:2.15.1] at org.apache.camel.processor.MulticastProcessor$1.call(MulticastProcessor.java:289)[180:org.apache.camel.camel-core:2.15.1] at java.util.concurrent.FutureTask.run(FutureTask.java:266)[:1.8.0_65] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)[:1.8.0_65] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)[:1.8.0_65] at java.lang.Thread.run(Thread.java:745)[:1.8.0_65]Jan 5, 2017, 9:47:00 AMERROR
com.esri.ges.processor.fieldcalculator.FieldCalculatorExpression ['{"x":'+ x +',"y":'+ y +'}'] evaluation failed: operator+(arg0:[NonGroup], arg1:[NonGroup]) evaluation failed because of Unavailable GeoEvent field 'x' cannot be used as an argument arg1:[NonGroup]Jan 5, 2017, 9:47:00 AMERROR
com.esri.ges.processor.regexfieldcalculator.RegularExpressionFieldCalculatorField Calculator (Regular Expression) failed to assign value 'null' to the field 'y' of GeoEventDefinition 'Generated-dart_cross_sector_assessment_retaken_villages-iFB': Could not find a field with the name y in the geoEventDefinition Generated-dart_cross_sector_assessment_retaken_villages-iFB.Jan 5, 2017, 9:47:00 AMERROR
com.esri.ges.processor.regexfieldcalculator.RegularExpressionFieldCalculatorField Calculator (Regular Expression) failed to assign value 'null' to the field 'x' of GeoEventDefinition 'Generated-dart_cross_sector_assessment_retaken_villages-iFB': Could not find a field with the name x in the geoEventDefinition Generated-dart_cross_sector_assessment_retaken_villages-iFB.Jan 5, 2017, 9:47:00 AMERROR
com.esri.ges.processor.regexfieldcalculator.RegularExpressionFieldCalculatorField Calculator (Regular Expression) failed to assign value 'null' to the field 'coords' of GeoEventDefinition 'Generated-dart_cross_sector_assessment_retaken_villages-iFB': Could not find a field with the name coords in the geoEventDefinition Generated-dart_cross_sector_assessment_retaken_villages-iFB.Jan 5, 2017, 9:47:00 AMERROR
0 Kudos
AlexanderBrown5
Occasional Contributor II

Charlie,

The bottom three messages show they are trying to calculate a field in your original GeoEvent definition instead of the newer copy.  The messages on top let you know it is not writing to the output (which makes sense because something is off in the steps before the output.  I would look into the TCP tool to help troubleshoot or write to a file output; going through each step and repeating.

You can also try restarting the GeoEvent Service in the manager, or restarting the entire windows GeoEvent service.

Once you start working through each step and looking at the output, just post and Ill keep helping!

~Alex

0 Kudos
Mtclimber03
Occasional Contributor

Hi Alexander,

I changed the input to now direct to the copy version of the definition we created which has eliminated the 1st two errors in the logs. I now have the following before failing to output.

com.esri.ges.processor.fieldcalculator.FieldCalculatorExpression ['{"x":'+ x +',"y":'+ y +'}'] evaluation failed: operator+(arg0:[NonGroup], arg1:[NonGroup]) evaluation failed because of GeoEvent field 'x' of type [Double] and value 'null' cannot be used as an argument arg1:[NonGroup]Jan 5, 2017, 10:25:42 AMERROR

Below is the data that is being processed in the sample record submission for CREATED_LOCATION field 

36.192520:43.332160:283.459053:5.000000:3.000000:5.080000:118.125000:1482836588.000356
0 Kudos
AlexanderBrown5
Occasional Contributor II

Charlie,

I apologize, I lost track of this thread.  Did you ever find a resolution?

~Alex

0 Kudos
Mtclimber03
Occasional Contributor

Hi Alex,

I didn't but I have changed jobs and the project I was using the above for has wrapped up. While we weren't able to successfully parse out the preferred field we wanted to use for X,Y we were able to work with our data collection input source to get what we needed for the project. We were so close to resolving this though!

AllWeatherHeather
Occasional Contributor

Hi Alex,

I've been trying to follow your instructions to parse through kml data using the out of the box XML input connector. However, I'm having trouble getting the field mapper to work. Regardless of what I put in the field mapper (or field reducer) the output I get via TCP or CSV looks the same. Any ideas? Is there a better way to manage a KML input?

Thanks,

Heather

0 Kudos