topic Re: Possible to link Geodatabase to SQL Server to view and or edit real time data in ArcGIS Enterprise Questions

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

KristenJones1 — Tue, 06 Nov 2012 17:53:03 GMT

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!

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

TanuHoque — Wed, 07 Nov 2012 16:32:46 GMT

Jason,
can you please send me the schema of both tables - more specifically the fields you are using to join and the field (including field type) that you need to determine which record is the current for each well?

Thanks.
Tanu

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

KristenJones1 — Wed, 07 Nov 2012 17:18:22 GMT

Thanks for the reply, however it really does not matter, because I would create all new tables if it was possible to connect. The problem is that no matter what I do in any scenario, if I try to join anything outside of the actual Geodatabase, the publish fails. I was just hoping of some work around or a way to do this.

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

GeomatiqueTI — Thu, 08 Nov 2012 15:04:53 GMT

Did you find a solution?
Thanks

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

KristenJones1 — Fri, 09 Nov 2012 09:46:39 GMT

No, I have been looking for a solution for years, personally I don't think there is one. That surprises me given the huge need for editing real time data and tying into existing database infrastructure.

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

ZacharyHart — Fri, 09 Nov 2012 10:23:57 GMT

No, I have been looking for a solution for years, personally I don't think there is one. That surprises me given the huge need for editing real time data and tying into existing database infrastructure.

I believe the Spatial Data Server is what facilitates this functionality.

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

KristenJones1 — Fri, 09 Nov 2012 11:02:59 GMT

It was always my understanding that Spatial Data Server was for connecting to and serving spatial databases, in my case the databases I need to access are not Spatial, they are just plan 'ole SQL databases. I would also still need to "link" the spatial data from the GeoDatabase features. If I'm wrong I'd love to see an example, case study, white paper or something that outlines how it works and the capabilities. I'm thinking that the only way to do this type of display and editing capabilities is on a post process outside of the actual map and feature services... like Silverlight or Flex?

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

ZacharyHart — Fri, 09 Nov 2012 12:13:29 GMT

Ah, i see what you mean now. What if you created an OLEDB connection to the non-spatial DB, I believe you can publish that way?

http://forums.arcgis.com/threads/24893-ArcGIS-Conecction-OLE-DB-Publishing-MapService-Problem

Search "ArcGIS Server OLEDB connection publish"

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

KristenJones1 — Fri, 09 Nov 2012 12:39:13 GMT

Thanks for the link, that describes what I want to do and the problem in a nutshell, however it is referring to ArcGIS vers 10 and under. I'm using 10.1 which is now completely different. You don't turn MXDs into services anymore.... and a lot of other core changes, like having to use arcGIS to publish a map rather than on your own server.

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

ZacharyHart — Fri, 09 Nov 2012 13:27:44 GMT

Thanks for the link, that describes what I want to do and the problem in a nutshell, however it is referring to ArcGIS vers 10 and under. I'm using 10.1 which is now completely different. You don't turn MXDs into services anymore.... and a lot of other core changes, like having to use arcGIS to publish a map rather than on your own server.

Did you register your nonspatial database with your GIS Server?

EDIT: tested and I can get related data to publish (even related data out in a nonspatial SQL table) but no editing.

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

KristenJones1 — Fri, 09 Nov 2012 17:04:06 GMT

.. 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.

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

DennisGeasan — Mon, 12 Nov 2012 20:50:16 GMT

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

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

KristenJones1 — Mon, 12 Nov 2012 23:04:39 GMT

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

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

DennisGeasan — Tue, 13 Nov 2012 00:19:21 GMT

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

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

KristenJones1 — Tue, 13 Nov 2012 10:38:44 GMT

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

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

AlisonKuemmel — Fri, 07 Mar 2014 13:38:51 GMT

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

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

KristenJones1 — Fri, 07 Mar 2014 16:58:27 GMT

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