AnsweredAssumed Answered

Spatial join to get attributes of nearest neighbor

Question asked by tchapin on Jan 30, 2015
Latest reply on Jul 7, 2015 by tpcolson

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

Outcomes