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

Question

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.

GregorMoehler · Answer

In this question i did not wanted to focus on indexes so much - i realize the example was not perfect.

Let's suppose the task is to show the number of tornados that hit a county in a particular year or later. The year should be configurable by a filter in the map. If i had to write an SQL with a fixed year, say 2010, I would probably do it this way:

SELECT COUNTY.OBJECTID AS COUNTY_ID,

COUNT(COUNTY.OBJECTID) AS TORNADO_COUNT

FROM SAMPLES.GEO_COUNTY AS COUNTY,

SAMPLES.GEO_TORNADO AS TORNADO

WHERE TORNADO.YR >= 2010 AND

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

GROUP BY COUNTY.OBJECTID;

But how would i do this with a configurable year parameter?

In the SQL I could group by year, and then in the map filter years >= 2010, using such an SQL:

SELECT COUNTY.OBJECTID AS COUNTY_ID,

COUNT(COUNTY.OBJECTID) AS TORNADO_COUNT,

TORNADO.YR AS TORNADO_YR

FROM SAMPLES.GEO_COUNTY AS COUNTY,

SAMPLES.GEO_TORNADO AS TORNADO

WHERE

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

GROUP BY COUNTY.OBJECTID, TORNADO.YR;

But ultimately this won't work, because I would get one record for each year > 2010, each with a shape and a tornado count just for that year.

But what i want is one shape with the sum of all counts. And I cannot get it if i cannot put the parameter into the SQL at the appropriate place.

I was thinking of using the join tool, but then the join is probably made in ArcGIS instead of in the database.

Any ideas?

ThomasColson · Answer

Hmmm....the exact syntax of how-to escapes me at the moment, but you'll want to include some sort of distance or predicate filter that prevents searching of every county for every tornado, or vice-versa...maybe throw a join in there?

Eg. in MS SQL:

PARKDISTRICT = COALESCE(f.District, p.PARKDISTRICT)
    LEFT OUTER JOIN DISTRICTS AS f
  ON f.Shape.STIntersects(i.Shape) = 1

or maybe a cross-apply where TOP 1 stops your search as soon as it gets a result

CROSS APPLY (SELECT TOP 1 FULLNAME, shape                     
FROM dbo.GRSM_ROADS  
/****** force spatial index hint ******/  
WITH(index ([GRSM_ROADS_idx]))                    
WHERE GRSM_ROADS.Shape.STDistance(i.Shape) IS NOT NULL  
                  ORDER BY GRSM_ROADS.Shape.STDistance(i.Shape) ASC) as J

Again, unsure of how Oracle does it, but world of difference in MS when you force a spatial index hint.

GregorMoehler · Answer

I do have spatial indexes, which probably explains why it is not horribly slow...

However, f you can filter by a non-spatial column before doing the spatial join (and have the appropriate index for it) it would certainly speed up things much more, because in general spatial joins should be more expensive than other secondary indexes.

Given that you focus on indexes: Does this mean there is no other way to achieve what i plan to achieve?