AnsweredAssumed Answered

Optimizing STIntersects queries

Question asked by jensdinformi-dk-esridist Employee on Mar 10, 2015
Latest reply on Mar 26, 2015 by ameskamp

A customer of ours (an electric utility company) wants to increase the use of the grid data registered in ArcGIS. Recently their BI department has asked us to help in establishing various reports / queries from outside ArcGIS.

 

An example of such query is to list substations per maintenance area / sub area. This query delivers the requested list:

 

Select      Byg.Navn,

            eft.Betegnelse,

            str(eft.Year)

From        ELDB.EL.BYGVAERK_EVW byg,

            ELDB.EL.Eftersynsomraader_evw eft

Where       Byg.BygvaerkArtKode IN (1,2) AND

            Byg.SHAPE.STIntersects(eft.SHAPE) = 1

Order by    eft.Betegnelse,

            str(eft.Year)

 

(BYGVAERK_EVW is the versioned view of substations and Eftersynsomraader_evw is the versioned view of maintenance areas).

 

Executing this query in MS SQL Server Management Studio takes 3 minutes.

 

If in ArcMap I do a spatial join between the two feature classes I am able to present the result in approximately 1½ minutes. If ArcMap is able to deliver the requested answer in 1½ minutes I don't see why asking for the same trough non-ArcGIS queries shouldn't be able to also deliver the answer in 1½ minutes - or even faster.

 

I have a feeling that the answer to optimizing my query lays in the use of spatial indexes. But how should I approach implementing this?

 

Any help would be appreciated.

Outcomes