topic Re: Spatial join to get attributes of nearest neighbor in Data Management Questions

Spatial join to get attributes of nearest neighbor

TedChapin — Sat, 11 Dec 2021 13:37:04 GMT

I've got some data in an enterprise geodatabase (SQL Server 2012). I want to create a view that joins points in a feature class to the nearest line in another feature class. The join includes the shape field and attributes from the point feature class, plus some attributes from the nearest line, plus the distance to the nearest line. I wrote a simple but inefficient view that works for a small test dataset, but performance quickly degrades. 6 seconds for 2000 points against 20 lines. Looking at the execution plan, the spatial index is not being used at all. I manually created the spatial index in ArcCatalog for both the points and the lines with coordinates that bound all of the data with Medium grid granularity at all levels.

Here is the code, looking for tips on how to engage the spatial index or better, more scalable logic in general:

--POINTS is the point feature class
--LINES is the line feature class
select
  p.OBJECTID,
  p.SHAPE,
  p.PointAttribute,
  closestline.LineAttribute,
  p.shape.STDistance(closestline.shape)
from 
  POINTS as p 
  cross apply (
  select top 1 l.LineAttribute, l.Shape
  from LINES as l
  order by p.SHAPE.STDistance(l.SHAPE)
  ) as closestline

Re: Spatial join to get attributes of nearest neighbor

JoshuaBixby — Fri, 30 Jan 2015 17:30:58 GMT

You can force indexes through Query Hints (Transact - SQL). That said, forcing an index may not improve performance, sometimes it makes performance worse. Since it seems you are in a development/testing phase and not writing production code yet, it might not hurt to experiment with forcing the different indexes to see what happens. If forcing an index does help, especially dramatically, then the question becomes why isn't the query optimizer choosing to use either index. By the way, I believe it is still true that SQL Server can only use one spatial index in a single query, but that may have changed.

If you haven't already, you may want to review SQL Server 2012 - Spatial Indexes Overview. The more you understand how MS has implemented spatial indexes, the less of a black box it will all seem, which makes for better SQL troubleshooting. Also, there are some very specific syntax rules for maximizing a nearest neighbor type of query, which you are trying to do in some form.

Beyond reading MS's overview, there are some good blogs and MSDN discussions on tuning SQL Server spatial indexes.

Taking a quick glance at your specific code, you are missing some of the requirements for a spatial index to be used. As the overview documentation I linked to above states:

Geometry Methods Supported by Spatial Indexes
Spatial indexes support the following set-oriented geometry methods under certain conditions: STContains(), STDistance(), STEquals(), STIntersects(), STOverlaps(), STTouches(), and STWithin(). To be supported by a spatial index, these methods must be used within the WHERE or JOIN ON clause of a query, and they must occur within a predicate of the following general form:
....

Re: Spatial join to get attributes of nearest neighbor

ThomasColson — Tue, 07 Jul 2015 18:15:08 GMT

Witch Magic, Snake Oil Medicine, and Spatial Index Tuning might help you here.