AnsweredAssumed Answered

How to apply spatial joins and publish them in ArcGIS online?

Question asked by moehler@de.ibm.com on Nov 10, 2015
Latest reply on Nov 11, 2015 by moehler@de.ibm.com

In ArcGIS online I want to show a map with data, which is the result of a spatial join of data that resides in a database, something like:

SELECT COUNTY.OBJECTID AS OBJECTID,

       COUNT(COUNTY.OBJECTID) AS TORNADO_COUNT

FROM SAMPLES.GEO_COUNTY AS COUNTY,

     SAMPLES.GEO_TORNADO AS TORNADO

WHERE  ST_INTERSECTS(TORNADO.SHAPE, COUNTY.SHAPE) = 1

GROUP BY COUNTY.OBJECTID;

 

Additionally, i want to keep some parameters (e.g. time) flexible to be modified (filtered) in the map, e.g.

SELECT COUNTY.OBJECTID AS OBJECTID,

       COUNT(COUNTY.OBJECTID) AS TORNADO_COUNT

FROM SAMPLES.GEO_COUNTY AS COUNTY,

     SAMPLES.GEO_TORNADO AS TORNADO

WHERE TORNADO.YR = ? AND

    ST_INTERSECTS(TORNADO.SHAPE, COUNTY.SHAPE) = 1

GROUP BY COUNTY.OBJECTID;

 

How would i do this? I would have loved to add a parameter to the query itself (as shown in the example above) and use it later in the filter option of an ArcGIS online map, but did not find a way to do this.

 

I kind of managed to do it this way: I create a view in the database (DB2) that does the spatial join and consume its result in a query layer. I share this layer with ArcGIS server (using capability FeatureAccess) and publish it to ArcGIS online. There I filter for some parameter (e.g. time). This works, but looks quite slow. Since we use a database view the additional filter parameter is only applied after the join. With a lot of data a lot of unnecessary computation is done.

 

Originally, I had tried to paste the join into the query of the query layer, but this is then considered to be a "complex query" which cannot be shared on an ArcGIS server. Anyway even this way I don't think the parameter could be applied before the join, since it would mean that ArcGIS would need to parse my query.

 

Any better way to attack this problem?

I have seen QueryTasks in the JavaScript library, but I am not sure i can use them in my context.

Outcomes