topic Re: Parse JSON location data from array in ArcGIS GeoEvent Server Questions

Parse JSON location data from array

LeoLadefian5 — Thu, 28 Feb 2019 19:46:47 GMT

I need to parse some location data from a field that is an array, coordinates. I also need to flatten this out as I need to send it to the SDS. What is the process to flatten a JSON and extract attributes? Geoevent Service creator? My thought was to use some sort of JSON query like event_location.geometry.coordiantes{1} for the X and Y geometry fields, but I don't believe this will work. Here is the data I'm looking at:

Re: Parse JSON location data from array

DanielCota1 — Wed, 13 Mar 2019 17:41:08 GMT

Hi Leo Ladefian‌,

One common method of flattening out the JSON using GeoEvent is to configure the input to specify the JSON Object that you want to pull the prospective fields from. In this case, I believe you want to pull from status_changes. If you specify this for the JSON Object in the input, then upon saving and starting the input, GeoEvent will pull data and create a definition from everything under status_changes. You can then use this definition for other connectors. You can also leverage the Field Mapper processor to map this definition to another that may include it's own "coordinates" field that you manually create.

Now, extracting the coordinates is what may be a little tricky. Since it is a little more deeply nested in the JSON, we may have to create a separate input connector that polls the JSON, but this time, only specify the event_location.geometry object (JSON queries do work in this regard) in the input configuration. This way the definition that gets created will include the "type" and "coordinates" fields. From there, you can output this into a JSON file and then use the Field Enricher (File) processor to enrich the original GeoEvent service from the paragraph above with the two fields from this file.

Finally, assuming all goes well, the output can receive the data from status_changes along with the data from the "coordinates" field and will be able to be viewed on a web map.

I realize that this workflow is a little tedious and sounds a little unorthodox, but this is the only way I can think of to both flatten the JSON and extract the data from the nested field.

I hope this helps out.

-Daniel

Re: Parse JSON location data from array

JeffreyWilkerson — Thu, 14 Mar 2019 16:09:34 GMT

Leo,

I'm pretty new to GeoEvent server, but I think you would need to create a new input connector that parses the JSON structure according to what you describe. We are setting up a new 10.7 Pre Release GeoEvent server and the GTFS connector is not working for some reason. To get around it I created the Python script below. This runs through Task Scheduler and polls the JSON web service every 5 minutes or so, and then parses the bus locations out to individual streams (the source just has everything in one JSON object). The resulting individual stream is pushed through a TCP port that GeoEvent server picks up through a TCP input connector.

The main point is that the code (Python in this case) is parsing the JSON object for specific values. First, the JSON feed is dumped into a list (output = json.loads(data)) and then each individual 'entity' is selected for individual variables (i.e. bus["vehicle"]["trip"]["trip_id])). I think this is what you mean by "flattening out" the JSON data. In your case you could cycle through the status changes (not sure if there is more than one) using ' for status in output['status_changes']:' and get a coordinate using status["event_location"]["geometry"]["coordinates"][0].

To set this up through an input connector itself would mean coding it in Java, something I've still got to figure out.

Good luck, Jeff.

import arcpy
import urllib, json, os
import time
import socket
from datetime import datetime, timedelta

def GetBusLocations():
## Create socket connection to hostname/port on which a TCP GeoEvent input is running
host = {Your service IP}
port = 5563
tcpSocket = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
tcpSocket.connect((host, port))

JsonWebData = "https://transitdata...?format=json"
from urllib import urlopen

src = None

try:
while True:
src = urlopen(JsonWebData)
data = src.read()
output = json.loads(data)

## Loop through feed entities
for bus in output['entity']:
tripId = str(bus["vehicle"]["trip"]["trip_id"])
busId = str(bus["vehicle"]["vehicle"]["id"])
routeId = str(bus["vehicle"]["trip"]["route_id"])
startDate = str(bus["vehicle"]["trip"]["start_date"])
startTime = str(bus["vehicle"]["trip"]["start_time"])
timeStamp = str(bus["vehicle"]["timestamp"])
latitude = str(bus["vehicle"]["position"]["latitude"])
longitude = str(bus["vehicle"]["position"]["longitude"])
bearing = str(bus["vehicle"]["position"]["bearing"])
speed = str(bus["vehicle"]["position"]["speed"])
status = str(bus["vehicle"]["current_status"])
stopId = str(bus["vehicle"]["stop_id"])

## Subtract 7 hours from timestamp to equate to local time.
dTS = (datetime.utcfromtimestamp(float(timeStamp)) - timedelta(hours=7)).strftime('%Y-%m-%d %H:%M:%S')

## For TESTING
## print("Bus ID: %s" % busId)
## print("Route: %s" % routeId)
## print("TripID: %s" % tripId)
## print("Date: %s" % startDate)
## print("Timestamp: %s" % timeStamp)
## print("Lat: %s" % latitude)
## print("Long: %s" % longitude
## print("Bearing: %s" % bearing)
## print("Speed: %s" % speed)
## print("Status: %s" % status)
## print("Stop: %s" % stopId)

if busId is not None and startDate is not None and startTime is not None:
## Build fields to send to GeoEvent.
msg = tripId + "," + \
busId + "," + \
routeId + "," + \
startDate + " " + startTime + "," + \
dTS + "," + \
latitude + "," + \
longitude + "," + \
bearing + "," + \
speed + "," + \
status + "," + \
stopId + "\n"

## For TESTING
#print(msg)

## Send message to TCP socket
tcpSocket.send(msg)

time.sleep(5)

except:
time.sleep(30)
pass

finally:
if src:
src.close()

if __name__ == "__main__":
GetBusLocations()

Re: Parse JSON location data from array

LeoLadefian5 — Mon, 25 Mar 2019 17:50:30 GMT

Thanks Daniel, so once I've connected the event_location json input, do I create another geoevent service that outputs this to a json file? Then I take the json file and enrich the fields in the original service, however it's asking for a join field, which the coordiantes input doesn't retain.

Re: Parse JSON location data from array

MarielaDel_Rio — Thu, 28 Mar 2019 13:11:10 GMT

In GeoEvent, when you create your input, indicate the X and Y fields as the image. In the services, flatten the input with a mapper including the fields you want. Map

I have not figured out how to work with the trips endpoint though.

Re: Parse JSON location data from array

LeoLadefian5 — Thu, 28 Mar 2019 13:57:30 GMT

bingo, thats what I ended up doing.

Re: Parse JSON location data from array

MarielaDel_Rio — Thu, 28 Mar 2019 14:04:15 GMT

Do you know how to use the /trips end point? I would like to get the trips as features to make reports of those who exit a geoFence and for how long, but I have not figured out how to field map or save the group of coordinates for the routes. One route may have many points in a json array.

Thanks

Re: Parse JSON location data from array

LeoLadefian5 — Thu, 28 Mar 2019 14:10:59 GMT

the route "feature collection" is considered a geojson, so I would use the geojson input for that.

Re: Parse JSON location data from array

MarielaDel_Rio — Thu, 11 Apr 2019 20:09:49 GMT

I tried using geoJson but it does not bring any events. With the Json input, at least I can get the events into a json file, but I get nothing with a geoJson.