GeoEvent process Oracle/SQL connector

[arunepuri1]

Hi

My requirement is to display Oracle/SQL Server  Lat/Long real time data on map. The displayed data need to be stored in destination table (Feature Layer) as historical/Temporal. So that user can see,at what time what incident happen.

Is there any connector for this or any helpful document/tutorial available.

Please pass the required information.

Thanks,

Arun

[RJSunderman]

The GeoEvent extension includes an inbound connector which is able to poll an existing Esri feature service, for example, to retrieve data from a feature layer. The feature service in this case acts as a data broker which the GeoEvent extension can work through to access the underlying feature data in the database. The product team has no plans to provide ODBC / JDBC connectors to retrieve data directly from RDBMS tables. Connectors such as these would be database specific and we are working to keep GeoEvent database agnostic.

You do have a few options available, however, without relying on an ODBC / JDBC connector:

1. Since ArcMap will not allow you to publish a map document as a feature service if the MXD has no feature layers, you cannot create an MXD with *only* a non-spatial table and publish a feature service. ArcMap will require that you include at least one feature layer whose "features" come from a "feature class" … a spatial table containing geometry.
   
   However, if you add your non-spatial table to a map document and then add any feature layer, even one pointing to an empty (dummy) feature class which has no features, you should be able to publish a feature service. An out-of-the-box GeoEvent input can then poll the non-spatial table for "features" through the feature service. When configuring the Poll an ArcGIS Server for Featuresinbound connector, you simply select the non-spatial table as the "layer" rather than the actual (dummy) feature layer which will be forever empty (no features).
   
   
2. Another option is to create a script which would periodically retrieve the non-spatial event data from the database's table, and then HTTP/POST the data directly to an endpoint associated with a GeoEvent Extension input. The out-of-the-box inbound connectors expect either Generic JSON or Esri Feature JSON. Later releases of GeoEvent also support XML and geoJSON.
   
   
3. You might also look into creating a wrapper around the RDBMS using an open API such as the OData protocol (http://www.odata.org/). The OData protocol provides rapid solution development incorporating database access through web services as a front facet to the backend database.
   
   We are only interested in retrieving row data as "events" … and the OData protocol provides an REST endpoint through which the database records can be retrieved as generic JSON. This approach fits well with the GeoEvent extension which provides a generic JSON adapter out-of-the-box. You can then configure a Poll an external website for JSON inbound connector to poll the OData endpoint for data – or configure a Receive JSON on a REST endpoint inbound connector to receive JSON data from an OData HTTP POST (though I'm not sure if OData is capable of retrieving the database records and POSTing them to an external client).

An architecture for the OData approach might look something like the following:

Hope this information is helpful -

RJ

[BrunoMendes]

Sunderman,

We have two RDMS which we would like to integrate GeoEvent with.

I've done step 1 successfully with our view on SQL Server.

When I tried the same thing with an Oracle view, the tool says the feature service cannot be published due to one or more tables that are not supported.

In both cases our views have only textual data.

[VinceAngelo]

Feature services generally require a rowid column (32-bit integer, NOT NULL, with unique values) in addition to a geometry column.  If the views contain only string columns, they cannot be Feature Service ready.  You should probably ask a new question in an appropriate location (Managing Data, Geodatabase, ArcGIS Server), because branching this now would be too confusing.

- V

[RJSunderman]

You do have a few options available, however, without relying on an ODBC / JDBC connector:

Here's another trick that a customer described to me. If you know an RDBMS wizard, ask them to help you create a spatial view of your data. ArcGIS can use a spatial view in the same way as it uses a feature class, so you can publish a spatial view as an ArcGIS feature service.

This can provide you a great deal of flexibility. You have complete control over your spatial view, such as joins you want to perform with other tables in your database or limiting the data exposed through the spatial view to the "most recent five minutes". The feature service will appear to be automatically updating to display the most recent data - but the back end database is doing all the work. And you now have the ability to query the feature service using endpoints exposed through the ArcGIS REST Services Directory.

I'm not an RDBMS wizard and thought this approach was absolutely brilliant.

- RJ

[DennisGeasan]

Arun,

Consider adding triggers in your coordinate tables that update a spatial column.  In ArcMap (10.2.2) add the spatially enabled table (or views on the table).  ArcMap will treat the table as a 'Query Feature Class' which is nearly the same as a geodatabase feature class.   Enable 'Time' on the ArcMap layer.  Then publish the map to your ArcGIS server instance as a map service.  You can utilize the time slider in web maps you create in ArcGIS Online or your ArcGIS Portal.  You can also use the time slider in an ArcMap session that utilizes the AGS map service.

This approach doesn't require storing the historic data in a geodatabase feature class.  You probably already have that in your source Oracle/SQL DB.  Geoevent Extension also not required.   I've done this using DB views on a constantly growing SQL Server table currently with ~55 million records.  Performance is better than you might expect.  Comparable to using a geodatabase feature class.

Check it out.

DG