GeoEvent database views/spatial views for SQL

1860
5
09-10-2019 03:12 PM
EricSpangenberg
New Contributor III

We are new to using ArcGIS Enterprise, due to the install of GeoEvent server and upgrading to 10.6.1 we moved to ArcGIS Enterprise and Portal.  We are still wrapping our heads around and trying to understand all the moving pieces.

 

All of our enterprise GIS features are managed / maintained in our SQL database, and we still publish map services to our hosting ArcGIS Server referencing that data.

 

With GeoEvent we are looking to publish a web application that will allow users to see where snow plows / sweepers have been.  In this case, a linear feature that will be symbolized by when a road was last treated (within the last hour, 1-6 hours ago, >6 hours…)

We have a write up workflow from a working example provided by a community in Ohio, in their workflow they have a Geoevent feature service that writes to their SQL server which allows them to build a series of database view / spatial views using the street line and the updated feature table from GeoEvent.

 

Now, with our federated / ArcGIS Enterpirse and with the managed database for our ArcGIS Enterprise setup on the hosting ArcGIS Server, how can we work with that?  Ideally, it would be nice to write to a table in our SQL server that would allow us to create views / spatial views between event feature and the street centerline, and then publish a street service symbolized by treatment time.

 

Are we missing an interface to the data store that would allow us to create views? Maybe the option to write to CSV, then set up a script to copy and append to a SQL table?

 

Or is there a way to register our SQL database so that GeoEvent can have that location as an option to write too?

0 Kudos
5 Replies
GregoryChristakos
Esri Contributor

There's some details about your environment that we would likely want to clarify, but generally speaking you will want to use GeoEvent Server to apply real-time adds or updates to a feature service whose underlying data source references tables in your SQL database (more or less). GeoEvent Server itself doesn't make a direct connection to any sort of databases. Rather, it uses user-configured server connections (ArcGIS Online, ArcGIS Enterprise, ArcGIS Server), and the map/feature services available from those connections, to facilitate adds/updates to the underlying databases registered with those 'servers' (e.g. the ArcGIS Data Store as a managed database, SQL Server as a database, Oracle as a database, hosted in ArcGIS Online, etc). In brief, adding, removing, or updating data is handled through the feature services which expose the underlying data in a database.

I am not familiar with the ins and outs of your environment, but generally speaking I could see a hypothetical workflow here where you create a registered server connection in GeoEvent that points to your ArcGIS Enterprise. From there, you can publish feature services (or use existing feature services) which reference some sort of enterprise geodatabase, like SQL in your case, that has already been configured with your ArcGIS Enterprise's hosting server. As real-time information comes into GeoEvent Server about your snow plows, you can set up GeoEvent Services to perform analysis on that data and then use that new information to apply updates to the feature service you published (which is essentially pointing back to a table in SQL).

One thing you mentioned was creating spatial views between the event features and the street line in SQL to then publish a street service symbolized by treatment time. While this is certainly a possibility, it is worth pointing out that this type of problem can be solved directly within a GeoEvent Service using its analytic capabilities. For example, it wouldn't be a far fetched idea to regularly poll buffered street data (lets say every 5 seconds) as the event features. Snow plows could be brought into the same GeoEvent Service as dynamic point geofences. Using a spatial filter, the buffered street segments could be evaluated every 5 seconds to see if they "contain" any snow plow. Assuming a street segment "contains" a snowplow, we can configure the next process of our GeoEvent Service to update the status of that street to "recently cleared in the last..." since the intersection occurred with the snow plow. The output of this hypothetical service would be your streets data with an updated treatment status attribute in real-time. If you were using this output to update a feature service, and that feature service was already in a web map, you could visualize the real-time treatment status of your roads based on a symbology that uses the changing field. We can fine tune other aspects of our analysis to account for time, road segmentation, whether or not the plow has its arm down, etc, but I wanted to throw this out there to demonstrate one of many potential approaches to this problem.

Some of this might sound like jargon if you're new to GeoEvent Server and that's okay. If you are new to GeoEvent Server, I would recommend working through the introductory tutorial series found here. It'll give you a better idea of how GeoEvent Server works and how it fits into the broader ArcGIS Enterprise.

0 Kudos
EricSpangenberg
New Contributor III

Greg,

Thank you very much for your timely and detailed response.

On the first part, for some reason your explanation pulled the blinders off and I suddenly realized - yes, create the feature service from our SQL side and then consume in the GeoEvent!  It was like a light bulb moment that I hadn't thought of!  

We have been successful now in creating a web app that shows linear street elements symbolized by time treated.

The GeoEvent feature service gets geotagged from a street buffer we published and this allows for the spatial view between the event feature and our streets.

Thank you  very much, this takes some stress off us/me.  The Public works folks will review and hopefully provide feed back!

As for your second method - with the first one working, I have time to look more at GeoEvent capabilities.  We have the street buffer - if I'm reading it right, are you saying we  update a field in our street linear feature on the fly from GeoEvent?  That is intriguing, like I said, with a working application, now I can play/explore some more.

Thank you again.

0 Kudos
GregoryChristakos
Esri Contributor

Hello Eric,

You're welcome. I am glad that I was able to help pull the blinders off. Feel free to reach out if you have any further questions about this particular problem. I am happy to be of help.

0 Kudos
MortenGrude
Esri Contributor

Have you considered to use Arcade in Portal Web map viewer? I hav done som similar projects for lightning strokes – presenting the data in a timeframe 0-1 min, 1-5 min etc,

Morten Grude

Senior GIS-konsulent

A: Schweigaardsgate 28 | PB 9036 Grønland, 0133 Oslo

Sentralbord: +47 23 24 90 00 | Mobil: +47 90962908 |www.geodata.no<http://www.geodata.no/>

Fra: Eric Spangenberg <geonet@esri.com>

Sendt: onsdag 11. september 2019 00:13

Til: Morten Grude <Morten.Grude@geodata.no>

Emne: - GeoEvent database views/spatial views for SQL

GeoNet, The Esri Community | GIS and Geospatial Professional Community <https://community.esri.com/?et=watches.email.thread>

GeoEvent database views/spatial views for SQL

created by Eric Spangenberg<https://community.esri.com/people/marshgis?et=watches.email.thread> in GeoEvent - View the full discussion<https://community.esri.com/message/877032-geoevent-database-viewsspatial-views-for-sql?et=watches.email.thread>

0 Kudos
EricSpangenberg
New Contributor III

Sadly, I'm not much of a script writer.

But after your comment I did go and look into Arcade and if we could do something.

There may be some value in our looking at this!


Thank you for that suggestion!

0 Kudos