3

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

  • What's the nature of the geodatabase? File? Personal? Or ArcSDE? – GIS-Jonathan Oct 13 '14 at 14:34
  • ArcSDE. I am sorry for not referring that – Smalis Oct 13 '14 at 14:38
  • To be more clear 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 : – Smalis Oct 13 '14 at 14:52
  • I attempted to clarify your question title -- am I correct that it now reflects the meat of the issue you are asking about? – blah238 Oct 13 '14 at 19:31
  • I found out this interesting option here. Option 4 refers as an option the use of "Custom Geoprocessing service (ArcPy) that returns a FeatureSet made up of a custom FeatureClass (input schema?) generated by running the join,aggregation query server-side, creating the custom FeatureClass list and returning that to the client, the idea being that I would make the custom geoprocessing service look like a FeatureService or a Dynamic Map Service to the client". can it work? – Smalis Oct 16 '14 at 10:24
2

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!

  • Since the queries should be dynamically constructed, I dont think that predefined join tables would help, since in that case I would have to implement a join table for every possible combination I suppose. On the other hand, I am suspicious that that relates would be useful. However in that case I would like to ask: Can foreign keys replace relates ? I mean that having specified the FKs is the geodatabase able to realise it as a relate and perform queries over those connected tables, or I also have to create relates ? – Smalis Oct 14 '14 at 9:06
  • You can build the query.where statement dynamically. I was just showing you how it works. Let me put something together really fast for you to see. Also, you could use foreign keys, and then use multiple queries, but performance could become an issue. In order for the application to "realize" there is a relate, you would have to build logic into the app to check for relates. This is somewhat "reinventing the wheel" because the queryRelatedRecords function will only exist in services with related records. – evv_gis Oct 14 '14 at 13:19
  • Thanks for your example. You could build the the Query object dynamically, but it's only for attributes of a single table (or joined table) like in your example (ports.id and color.name). As I can understand the layer 0 of your query task url input is a joint table, that you have precomputed. But can you join and query tables dynamically? 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 B C , A C , A B C E, A E etc).I suppose you dont have t compute all the possible join combinations – Smalis Oct 14 '14 at 13:54
  • I am not familiar with any process in ArcGIS for Server that allows you to join/relate services post-publishing. It could be possible, I'm just not aware of anything. – evv_gis Oct 14 '14 at 14:15
1

Since you can't work with indexed views, your next best solution is to build a regular spatial view that joins up your tables. Only use necessary columns (NO SELECT *!) Publish as a service. Then use QueryTask, Query and FeatureSet from the JS API to call the results. If you know you will only need a subset of the joins for a certain situation (i.e. there's a situation that only needs stations and ports but never colors) then you can create a specific view to call for that situation for performance. Depending on the size of your dataset this can make either a miniscule or large difference.

All other normal database performance rules apply, of course.

If your data doesn't have to be 100% live up to the second, I would really reconsider the option to build that indexed view. It really helps for performance if you are working with big data + big joins. You could have it take 5-10 seconds once every 30 minutes to build the flat table and cut your query time by a noticeable amount for everyone that dings your server in that 29 minute 50 second nonquery window.

1

Since this is an ArcSDE geodatabase, consider using database views for queries that do not include spatial columns, or spatial views for those that do, to join related tables via their FKs as needed to flatten (denormalize) them into single logical 'tables', then you can add them to a map document as tables (views) or layers (spatial views), and publish them from the map document via ArcGIS Server. Database views are created in plain SQL (CREATE VIEW blah blah), and spatial views are created using ESRI ArcSDE tools. For more on the creation of spatial views, see the ArcSDE Administration Command Reference, specifically the sdetable -o create_view data management command, which is part of the ArcSDE command-line toolset.

It may also be possible to create spatial views from new ArcCatalog at 10.1 or 10.2, but at least at 10.1, the Oracle-based installations I work with, only the command-line tools are reliable. YMMV.

0

You're going to need some sort of middleware which can expose the geodatabase to the JS application. There are a number of options.

  • I already use arcgis for server enterprise edition where I could publish my stuff. To be more clear 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. – Smalis Oct 13 '14 at 15:01
  • This is the sort of query where implementing an SOE (Server Object Extension) will allow you to customize the out-of-the box query environment. It will mean coding a servlet in Java or C# to support the UI. – Vince Oct 13 '14 at 17:48

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.