Connecting GeoEvent to a PostgreSql View Table?

[AdamRepsher_BentEar]

Hello All,

I guess the Subject question is not completely correct - in the way that I am thinking.

I am here to ask if I am understanding correctly - There is a way to "connect" GeoEvent Server to a table (or view) in a in a non-spatial or non-SDE database.  Maybe the correct way of saying this is that we can register a database (in my case PostgreSQL) with ArcGIS Server and then, in GeoEvent, connect to the Feature/Web service that is now available in Server.

Am I getting this concept right?  Here's my workflow:

1. Create a Database Connection to generate an .sde file through ArcGIS Pro.
2. Add a user maintained data store within Portal.  (I believe that registering the db connection as a data store within the federated ArcGIS Server would be an alternate way to do this.)
3. Publish layers in bulk from a user-managed data store

My db user has all of the correct privileges....

In this scenario, should I be able to utilize a "Poll an ArcGIS Server for Features" input to grab the most recent events from those published payers?

Thank you for your help,

--Adam 

[EricIronside · Accepted solution]

Hey @AdamRepsher_BentEar 

Yes, you are approaching this correctly. The ArcGIS Server user (by default arcgis) must have at least read privileges on the view(s)/table(s) in the database that are to be published in the web service.  The database does not need to be a geodatabase, the user just needs that read-only access. For read only access, the web service can be a map service or a feature service.  Once the service is created it needs to be owned by the ArcGIS Server/Portal user that is defined in the GeoEvent data store connection, or it can be public. 

[AdamRepsher_BentEar]

Thank you @EricIronside 

I wanted to check because I am not getting any features back with this input.  The user is more than read access, and the owner of the service is the one defined in the GE data store connection.

My problem may be proximity and timeout (although I don't know how to interpret - or even find - any log entries) because the GE server is in Amazon West and the Feature Service is in Amazon East.

At this moment, this is a, "I would like to make this approach work for future projects" case - and this is the only available deployment of Enterprise with a connected PostgreSQL DB that I have access to.  So, thank you very much for your reply!!

--Adam

[EricIronside]

Hey @AdamRepsher_BentEar 

You can test your hypothesis outside of GeoEvent Server by doing a direct request against the REST api of the Feature Service from the machine GeoEvent Server is running on, outside of GeoEvent (query a layer in the REST end point with where = '1=1' in a browser and see how long it takes).  If it takes more than 30 seconds, you can increase the timeout property on your GeoEvent input. If it returns no data, the issue is in the feature service. If it returns with data in less than 30 seconds, the issue may be inside your GeoEvent Server input.

[AdamRepsher_BentEar]

@EricIronside 

Interestingly - my query works there - and it is pretty fast.
(now - just before this, I spent time modifying my views on the PostgreSQL db to only see the latest records)

I went to check Pro - and I can't open a table to look at the views - I get a Error code 400, Invalid query, Bad syntax in request.

I tried to Join to points in Portal.  No - {"messageCode": "AO_100215", "message": "JoinFeatures failed."} Failed to execute (JoinFeatures).

I then went into GeoEvent - and immediately saw that my input had recorded thousands of messages.  When I checked the output with Logger - I found exactly what I needed!

XXXX-sensortext-IN,103248,2021-05-19T20:50:50.000Z,PZ_TL_A,"{""level"":13,""battery"":4,""rssi"":42,""percentage"":35,""timestamp"":1621457449929}"

I have no idea why I can't do anything with the view in Pro or can't join it in Portal - but I am overjoyed that I can see it now in GeoEvent!

Now, see that JSON in the last field?  I need to figure out how to pull those field values out of that text string and insert them into a point Feature Service's appropriate fields.  (They are fixed sensors).

Fun!

--Adam

[EricIronside]

Did you let GeoEvent Server Create the GeoEvent Definition for you? If not, let it create a new definition (name it something like 'devices-in-auto'). Once it has been created, make a copy of it and name the new copy something like 'devices-in'. Make a copy of 'devices-in' and name it something like 'devices-flat'. Now edit 'devices-flat' and remove the grouped fields (with the values you see above) and re-add those fields to the GeoEvent definition (there should no longer be any groups in your definition).  Now go back to your service, update the input to use existing definition and choose 'devices-in', after the input, add a field mapper that maps 'devices-in' to 'devices-flat'.  To get to the grouped items from above, use the . notation (e.g. group.level or group.rssi).

[AdamRepsher_BentEar]

@EricIronside 

Amazing.

I am still trying to wrap my head around what you wrote.  As soon as I do, I am going to try it out.  I'll save the regular expression as a last-ditch.

THANK YOU!

--Adam

[AdamRepsher_BentEar]

ACTUALLY @EricIronside ,

Maybe I left out an important piece.  The fields in that CSV above (and in the definition) are:

• id - Integer
• timestamp - Date - TIME_START
• device_id - String - TRACK_ID
• data - String

The 'data' string field is what holds the JSON - as text.

Is this still going to work?

--Adam

[EricIronside]

Yes, that will still work. You will want to field map into a new GeoEvent definition that has all the new fields you want to extract out of the string (id - int, timestamp - date, device_id - String, data - String).

Then use a Field Calculator for each field you want to extract from the string, the expression is replaceAll(jsonStringField, '.*"rssi":(\d+),.*', '$1') (where rssi is replaced by the name of the value you are trying to get out) and putting the result into the respective existing field.

id = replaceAll(jsonStringField, '.*"id":(\d+),.*', '$1')

timestamp = replaceAll(jsonStringField, '.*"timestamp":(\d+),.*', '$1')

device_id = replaceAll(jsonStringField, '.*"device_id":"(\w+)",.*', '$1')

data = replaceAll(jsonStringField, '.*"data":"(\w+)",.*', '$1')

[AdamRepsher_BentEar]

@EricIronside 

I am wondering if making a "field mapper that maps 'devices-in' to 'devices-flat'" will work in this case.  The Regular Expressions are getting really out of control because I am finding that a number of fields are being returned as "human-readable" instead of just a simple integer.  Many different returns - with spaces, double-spaces, hyphens and spaces, hyphens and spaces and double-spaces, multiple versions of all....

The problem is, this is what I am working with (this is what I get out of the :

water-sensortext-IN,103248,2021-05-19T20:50:50.000Z,PZ_TL_A,"{""level"":13,""battery"":4,""rssi"":42,""percentage"":35,""timestamp"":1621457449929}"

//GeoEvent Definition
//id is Integer
//timestamp1 is Date (this is the date/time at the feed aggregator)
//device_id is String
//Data is String (this is the JSON cast as a String - in quotes "{}" )

That 4th field starting with "{ is  JSON - but in STRING format - GeoEvent does not see it as nested when letting it create its own GeoEvent Definition.  Actually - it's not nested.

Is there a way to force it to see that string as just JSON?