[GIS] How to perform relational queries against an ArcSDE geodatabase using the ArcGIS API for JavaScript

arcgis-desktoparcgis-javascript-apiarcmapesri-geodatabasefeature-layer

I have a geospatial database which contains both spatial and not-spatial tables
I would like to publish my data on ArcGis for server and perform both simple and spatial queries.
Also, foreign key relationships should be used so as to perform more complicated queries.

To be more specific:
There is a need to be able to execute application specific queries over multiple relational tables and some of them should contain geometries. So, for examle through the arcgis I have to be able to ask a question like : Give all the stations (stations Table) that are connected to ports(ports table) and have color blue (color table). The criteria should be added dynamically so, for example one could select through the jS api, the stations to have an additional characteristic from another table

I know that you could build the the Query object dynamically, but it's only for attributes of a single table (or joined table). If you could not know in advance which exactly tables you would join (for example implementing a filter) or how many of them you would join (table A with B , A with B and C , A C , A B C E, A E etc).I suppose you dont have to precompute all the possible join combinations.

Should I publish the entire geodatabase in some way?
Would relates help in that case?
Since queries should be constructed dynamically, I suppose it is not possible to be based on precomputed joined tables.

I need to do this maybe through the JS APi
I am using arcgis for Deskotop 10.2 and oracle DB 11g

Best Answer

If you are using ArcGIS for Server, there are a couple of steps you need to take.

  1. Register your ArcSDE Database
  2. Create Map/Feature Service with layers and setup the Related Tables to the layers
  3. Once published and accessible, you will need to setup your JavaScript to query the service AND the related records.

The links I provided for you are for the ArcGIS REST Resources. Understanding how the REST Endpoint works goes a long way when developing your application.

Now - having said all of that, it may be best to also restructure your data. Querying multiple attributes would probably work best if you used Joins rather than relates. Here is a good post that talks about how querying joined fields worked.

As a very simple example, with joined fields in the feature service, your query would look something similar to:

queryTask = new QueryTask("http://yourserver/arcgis/rest/services/yourstationservice/FeatureServer/0");
query = new Query();
query.where = "PORTS.ID IS NOT NULL AND COLOR.NAME = 'Blue'";
queryTask.execute(query,showResults);

The Joined fields will have a syntax like: TableName.FieldName, so my example is looking for all stations that have a PORTS.ID and the COLOR.NAME is blue.

I know it's a lot of information but it should get you started in the right direction. Good Luck!

EDIT

So I made this very ugly JSFIDDLE to show how the values in your where statement can be dynamically made. Hope that sheds some light on how joins can be a better alternative in your data schema. Hope this helps!

Related Question