5

I'm working on a proof of concept.

We are building a web map application where the data is split across two different database servers. The geometry is on ArcGIS server and the attributes or business data are on a remote SQL Server instance

Does anyone know to link query the data across two database servers?

One idea is to use REST webservices to communicate with the two separate datastores.

Improve this question
6
  • 2
    I've only worked with a dozen or two of these kinds of solutions over the years, but none of them ever was successful. Modern databases all support geometry datatypes; geometry should never be split from the other core business data.
    – Vince
    Dec 8 '14 at 21:36
  • @mnavidad I will need more information to answer your question. Are both your data stores SQL Server? Is one a GDB/SHP or something like that? This is possible but implemented differently depending on what you have so please add as much information about the environment as possible.
    – user1567453
    Dec 8 '14 at 21:41
  • you would be better off with the data mirrored and use one DNS (url) to be the query entry point and let IIS/Tomcat load balance the requests.
    – Mapperz ♦
    Dec 8 '14 at 21:45
  • I'm with @Vince on this one. One tiny hiccup and the whole thing comes crashing down. That is not to say that a table join can't be done but relying on two servers to respond in a timely fashion (before time-out) is just asking for trouble. Conors answer has merit as it uses smoke-and-mirrors to seem like one server. Consider copying your tables on ArcGis server with your geometries and updating from the parent at intervals (week, day, hour or minute depending on how live you need the data to be).
    – Michael Stimson
    Dec 9 '14 at 0:48
  • @user1567453 I was under the impression that ArcGIS 10.1 server includes a geodatabase? or is this not correct?
    – mnavidad
    Dec 9 '14 at 4:00
4

I see you have the SQL Server tag so I will answer based on the assumption that both of your database servers are SQL Server.

You can used SQL Server's Linked Servers to do this. In SSMS, log into the instance that has ArcSDE on it and do the following the following to set up a linked server:

Instance > Server Objects > Right-click Linked Servers > New Linked Server...

  1. Under the "General" page, type in the name of your server instance in "Linked Server".
  2. Under "Security", I have had best luck with using the "Be made using this security context" using database credentials that were identical on both servers. I would not use Windows authentication as this opens you up to discrepancies on both ends and all sorts of other issues with Kerberos authentication.
  3. Under "Server Options", you can leave the defaults.

Now in SSMS you will see the linked server under the "Linked Servers" folder. You should be able to browse the objects. You can access the nonspatial linked server data by running the following example query on your ArcSDE SQL Server instance:

SELECT * FROM [LINKED-SERVER-NAME].[INSTANCE-NAME].[SCHEMA-NAME].[TABLE-NAME]

Now all you have to do is build up a spatial view in your ArcSDE SQL Server instance that joins up your data using the SQL paradigm above. You can then publish that resultant view to the web. I would recommend caching if you are working with large tables because linked servers can be slow.

Improve this answer
5
  • 2
    Remote server join performance is usually poor, especially with large numbers of features. You often have to spend a great deal of effort to tune common queries to achieve "not too awful". The fastest solutions usually involve replication.
    – Vince
    Dec 9 '14 at 1:48
  • Yes, I agree with Vince. This will work, but there will often be pain. Replication or combining the tables into one db would be a much better long-term strategy.
    – John Powell
    Dec 9 '14 at 7:09
  • @conor, thank you for the suggestion but we have been instructed to use rest web services, given that we are working on two remote servers and we have firewall issues. Have you implemented this using rest web services?
    – mnavidad
    Dec 9 '14 at 15:45
  • Thought it was worth noting that from memory ESRI does not allow editing through a spatial view. The question doesn't mention it but mnavidad is actually trying to create a feature service to edit data.
    – user1567453
    Dec 18 '14 at 6:46
  • According to this link it is possible (albeit unofficially) to edit a spatial view through a feature service for at least 10.1: geonet.esri.com/thread/74975
    – Conor
    Dec 18 '14 at 14:12
2

You can join and display datasets on a map using an ArcGIS endpoint if you join them in ArcMap. The process is described HERE. Once you make the connection in your MXD just publish it like you normally would and it will be your REST endpoint. This will allow a connection to two databases through an ArcGIS REST web service without you having to write a single line of code or alter the database. The data formats don't even have to match.

The solutions below could also be used, but are advised against.

Obviously we can't give you a project and say "here this works" but the resources you may find helpful are:

Create basic RESTful web service

Create a basic web service(not RESTful)

Working with ADO.NET and SQL server

Connection strings to an SQL database

The approaches are:

  1. Get the web service to do the join of the data for you and then return the dataset to the application <-- this one has a specific purpose and is not reusable by other applications

  2. Get a REST web service to return the two datasets (with whatever SQL filters) and then have the application do the join logic. <-- this on is reusable by other applications ( funny thing is that this is strangely similar to an ArcGIS endpoint ;) This can also do a join in ArcMap to be published See OPTION 3)

If your chosen solution proves to be troublesome then I highly recommend a database link like @conor suggests since the database does everything faster and should be where data is joined/modelled.

I have done this before for integration of non-spatial asset systems and spatial systems and I spoken to other GIS professionals who have done the same. The general approach is to do this at the database level. The last time I did it I used a view across a database link and the performance was as good as those without database links.

FYI documentation for setting up and using a database link

Improve this answer
9
  • I'm bit discombabulated, the web map application will be Esri javascript API, html, and Css. Once the rest service recreates the data relation, how do I build a featureservice with the new recreated data?
    – mnavidad
    Dec 11 '14 at 18:49
  • right now I have a rest service that returns a dataset in this format [WebInvoke(Method = "GET", ResponseFormat = WebMessageFormat.Json, BodyStyle = WebMessageBodyStyle.WrappedResponse, UriTemplate = "json/")] DataSet JSONData();
    – mnavidad
    Dec 11 '14 at 18:51
  • for example the concrete class has this logic:public DataSet GetShapeData(DataSet dataSet) { fnGetConnection(strSql); return dataSet; } public DataSet GetBusinessData(DataSet dataSet) { fnGetConnection(strSql2); return dataSet; }
    – mnavidad
    Dec 11 '14 at 18:53
  • private void CreateRelation() { // Get the DataColumn objects from two DataTable objects DataColumn ShapeColumn = dataSet.Tables["Shape"].Columns["OBJECTID"]; DataColumn BusinessColumn = dataSet.Tables["Business"].Columns["OBJECTID"]; // Create DataRelation. DataRelation relShapeToBusiness; relShapeToBusiness = new DataRelation("ShapeToBusiness", ShapeColumn, BusinessColumn); // Add the relation to the DataSet. dataSet.Relations.Add(relShapeToBusiness); }
    – mnavidad
    Dec 11 '14 at 18:59
  • I'm going to revise my answer to be very clear about my suggested solution. I'm saying that the relation between the tables can be done in the MXD as a layer so you should not have to write your own code. ArcGIS Server can be your REST servive instead.
    – user1567453
    Dec 12 '14 at 0:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.