Possible to link Geodatabase to SQL Server to view and or edit real time data

[KristenJones1]

This is something I have needed to do for years and it seems with each new release of Server software we get closer and closer. I have a perfectly working ArcGIS Server 10.1(SP1) installed and a ArcSDE geodatabase connected and working and no problems. I can create and edit feature classes, but I REALLY need to display a data value from another SQL server/Database. All I want to be able to do is create a join on a table from this other database to show the current values of a joined record. For example I have a list of Wells in a Geodatabase and in another SQL server I have the current levels that are updated daily. I would like to be able to create a ArcGIS.com map that will have a popup on the well and then pull the current level from the other SQL database.  I can do this all day long in ArcMAP but I need to make a real world map to do this.  I have been able to create a LinkedServer in the GeoDB to query the other database and I can also create a VIEW and do a join of the database between the GeoDatabase table and the other server, but I have not been able to create a map service that will not fail to publish.

Is there any ways, back door, work arounds that would not require me to upload data and republish maps so that we can view real time data?

Thanks!

[KristenJones1]

..  yeah that is a big "But" (no pun) Yes I did register it, but only as a Database connection (FYI to others reading this, you can not register a Database server, that is not a SDEGeodatabase), the problem is that I can't publish it as a Feature service in order to edit the data live. To publish a Feature service to Edit the data, you can only connect to one GeoDatabase. 


Other option is copies the data, which at that point is not "live" or real time. If I only publish as a map service, then yes I can join the data, and view but (so far, I can't see) no ability to do any advanced joins (left or right joins) and select statements and like you said, you can not edit the data.

[DennisGeasan]

Approaches to consider:

Use Add XY in ArcMap (this assumes all of your well data in the SQL table is the same as in your current feature class)
1) In your map doc add the non-spatial SQL table/view as an OLE connection.
2) In ArcMap use Add XY to spatialize the table on the fly.
3) Publish this as a map service.  Any changes in the source table will be presented in the AGS 10.1 map service.

ESRI recommends that this approach not be used for server but it seems to work ok for small data sets.

If the attributes in your feature class are different from the SQL table then add the SQL table to your map doc as an OLE connection, then join it to your feature class using a key value common to the feature class and the table.

In either approach the ArcGIS Server service user ID has to be added to the SQL Server list of users and be granted read permissions on the table/view.  Do this using SQL Server Management studio.

If these don't do the trick I have a commercial app for just this situation that maintains feature classes in the back ground.

Dennis Geasan
GIS Technologies

[KristenJones1]

I appreciate the suggestions and I somewhat follow them, however I have tried creating a Feature Access service with another data source and it will not let you publish. I even opened a support ticket with ESRI and I was told that you can only have one data source being the single GeoDB and it must also be registered on the ArcGIS server. This is the case with a Feature Access service, which is the only service that allows you to edit the data.  Unless you are painting a larger picture that I am not seeing, this won't work through the standard ESRI software.

Thanks
Jason

[DennisGeasan]

Hi Jason,
Obviously I didn't read your original post close enough.  Sorry about that. 

What I missed in your post is your desire to do edits via ArcGIS Online.  Sounds like it's not possible as yet for feature classes that have a join.  Are there attributes from the non-spatial table required to be viewed while editing?  Have you tried doing the join and serve it as a regular map service?  I would think a feature class with a join should work as a regular non-editable map service.  I'm thinking you could wire up two map services that are both included in your Online Map, one for viewing the updated information from the join and the other being the editable feature service.

What is the origin of the data in the SDE feature class?  Is it derived from a non-spatial database also or is it maintained strictly via a Geodatabase edit session?  If maintained only via Geodatabase edits then consider building an SDE View.  In the SDE View try to join the non-spatial table to the feature class.  If that's successful then you should see it as another feature class but I'm not sure if you will be able to edit that. Something to try anyhow.

The Add XY method should work and carry thru to ArcGIS Online as a "read  only" regular map service.  At least it did pre SP1 for 10.1.    This idea only applies though if all of your attributes are being derived from the non-spatial data source.

Dennis Geasan

[KristenJones1]

Hi Dennis,  lots of questions, so I'll address each one...


Are there attributes from the non-spatial table required to be viewed while editing?
Yes...  hypothetical scenario.  I have someone in the field that is recording the well level and are using the GIS/GPS capability of the Spatial map. They need to record the pipe diameter and make some notes in the well log table. So I have a Join between the Well ID of the GeoDatabase to the SQL server that houses all of the Well info that is also served to unrelated Web applications, devices etc...  In this case I need to update the SQL server data but can't since the Join is not editable



Have you tried doing the join and serve it as a regular map service?
Yes,  works just fine.... providing the table/field follow the ESRI requirements (Field name, length etc...)


I would think a feature class with a join should work as a regular non-editable map service. I'm thinking you could wire up two map services that are both included in your Online Map, one for viewing the updated information from the join and the other being the editable feature service.
Yes, but again the Editable layer in my example,  the Point locations of the wells in the GeoDatabase, I can not use ArcGIS Feature Access to connect/join that spatial layer to the SQL server in order to edit. 


What is the origin of the data in the SDE feature class? Is it derived from a non-spatial database also or is it maintained strictly via a Geodatabase edit session?
Well the GeoDB has to at least have the Lat/Long and an ID,  so you could say it was derived from non-spatial data.  Nearly all of the data join and edits I would be using is a simple point class..  Using that layer to identify and find a location that then links to another database to edit data from. 


If maintained only via Geodatabase edits then consider building an SDE View. In the SDE View try to join the non-spatial table to the feature class. If that's successful then you should see it as another feature class but I'm not sure if you will be able to edit that. Something to try anyhow.
Tried that,  the internal ESRI "elves" saw that and didn't like it I got errors like unknown DBMS error or similar even though I was able to open it up in studio w/o any issues.



The Add XY method should work and carry thru to ArcGIS Online as a "read only" regular map service. At least it did pre SP1 for 10.1. This idea only applies though if all of your attributes are being derived from the non-spatial data source.
This suggestion/idea/comment, a bit unclear to me

Thanks
Jason

[AlisonKuemmel]

Hi Jason,

I have been reading over your forum (that is now a bit old) and am having similar questions. I have a feature class that contains a polygon with a UniqueID for neighborhood associations, and an Access database where the data is stored and maintained by a different department (including some non-ArcGIS users.)

I hoped to have an ArcGIS online map (or even our own hosted service) that would allow for the access database to be edited by different users, and the feature class/service with the UniqueID only edited for boundary changes. Ideally, these changes would automatically be shown online for everyone that uses the map to reference the most current data.

Did you ever find a solution to displaying oil well levels in the field real time?

Thanks for any advice you may have,
Alison

[KristenJones1]

Yeah it is a bit old, in computer years that is like what 15 or 20 years at least 🙂
Well here we are with another revamp of Ver 10 and now ArcGIS.com and no there is not a way to do what we want.  However what I ended up doing is using the Javascript API and developing some custom applications that created the joins outside of the SQL/SDE connections. In a nutshell, the data is pulled into an array and joined to the SQL database. Updating the Feature class goes through the SDE side and updating our SQL goes through another path, but to the user it is seamless. Basically we had to have two separate editing capabilities and use them seperatly but show them together to the user.

If you want the contact info of the developer I hired to help with this (Great give freelancer and super smart and very low rates), here is my LinkedIn
www.linkedin.com/in/jasonsjones/

Does that help?

Jason