Taking 811 Locates and converting them to a polygon feature layer

[JeffreySteele]

Hello,

I've been given the task of finding a way to process incoming email notifications like the one attached, extract the locate information from these emails, and display it on an ArcGIS online map (similar to the Map example) for our utility crews in the field. They need this information to carry out their locates and report back to our 811 locate system.

While there are ticket management solutions available, our small community currently lacks the budget for such services. My goal is to parse the email contents and convert them into a polygonal feature. However, the email only provides N, S, E, and W coordinates. If anyone has suggestions or can point me in the right direction to achieve this task, I would greatly appreciate it.

Thank you for your time.

[MobiusSnake]

Does your 811 system have any APIs?  Parsing emails is possible but it might be easier and more robust if there's something like a REST API you could make requests from instead.

[JeffreySteele]

Yes, they sent me this:
Thank you for your interest in our Positive Response feature. The Web Service/API process can be set up to work with most ticket management systems and will require you to set up an external interface on your end to upload data into our system.

It has been configured to allow a separate response for each utility type associated with the service area. Currently, those utility types are SEWER, STORM, STREETLIGHTS, TRAFFIC LIGHTS, and WATER.

Below is the URL for our web service. This URL also exposes the WSDL, which contains the XML definition of what the response envelope and its return will look like.

[MobiusSnake · Accepted solution]

Assuming the API exposes a query that allows you to get new tickets from a specific timeframe (e.g. "give me everything new in the last 15 minutes"), I'd do something like this:

• Create a hosted feature layer with polygon geometry and any attribution you're going to need for these sites (e.g. ticket IDs).  Don't share this layer with most users.
• From that layer, create a hosted feature layer view with read-only access.  Share this with your webmap users.
• Create a hosted feature layer that contains two tables, but no actual layers.
  • The first of these layers will be used to store the last time you queried your API.
  • The second of these layers will be used for logging API messages such as errors.
• Create an ArcGIS Online Notebook containing Python code that uses the requests library to query your API for everything since your last query (or something special in the event it's the first run, e.g. everything in the last 24 hours).
  • Use the Python API's edit_features method to write each ticket from the API response to your hosted feature layer as a new polygon feature.
  • Store your new time as the "last query time" in your first table.
  • Write any errors or warnings to your log table.
• Schedule the notebook to run at an acceptable interval, e.g. every hour.  (Maximum frequency possible is every 15 minutes.)

[JeffreySteele]

Thank you for the information and the help. This should be interesting since I'm field guy and was brought into the office recently.

[JeffSilberberg]

@JeffreySteele 

   Good Morning..  I do similar processing all the time although it's in a Unix style environment not Windows if that matters.   

    Set up a "Vacation" handler in your mail agent so that the incoming message gets routed into a mail handling script.  Then parse the message with Perl, PHP, Python, or a simple (relativity speaking) AWK (GAWK) script.  As a last step use Velocity or a simple Rest API to add the new entry to the feature layer as needed.. 

Also, an alternative may be just to develop a Zap to handle the email incoming portion without dealing with the infrastructure.   https://zapier.com/

[ZhongmingAn]

I did similar stuff using pywin32 to access my outlook and 811 locate information. I have a function script and a trigger. I only have to run the trigger and it will be good for a couple of days before it stops responding or some error comes up, and I have to rerun the trigger, or even restart outlook sometimes. It will run the function every time a new email is sent to my 811 folder and add a polygon automatically to our online feature service, which can be accessed by our field crew. I'm using outlook so it probably won't work if you are not using outlook. 

First of all, I set up a trigger in outlook to send all my 811 emails into an "811locates" folder

I'm in SD so our email format are a bit different but here is the python example

As for adding a polygon, I used arcpy instead of arcgis API since I lived under a rock. Here is the link to a tutorial: https://developers.arcgis.com/python/latest/guide/tutorials/add-a-point-line-and-polygon/

I think I just copy-pasted a trigger script from online and modified a few things and that was it. Here it is:

I also set up fields for the field crew to track their progress. Once they are done with the locates, they can change the status to done and the box will disappear from their locate map, but I still have access to all the finished locates.

It's probably not the best setup but it has got us through 2 summers so I am going to keep using it before it's completely broken.