locked
Will the "real" Nearest Neighbor please stand up! RRS feed

  • Question

  • I have a table of 16,000 points and a table of 51 polygons. Simply I need to join a value from each polygon nearest each point to that point. I'm running SQL 2012. I can't set a max search distance, so it seems my only option is to brute force it by distancing each polygon from each point and ranking. It takes a while for obvious reasons.

    Is there actually a nearest neighbor method, or just hairy queries that result in the same?

    Is there a better way than what I have done?

    SELECT RISKID, RETURN_VAL 
    FROM 
    	(
    	SELECT RISKID, RETURN_VAL, RANK() OVER(PARTITION BY RISKID ORDER BY DISTANCE ASC) AS [RANK]
    	FROM 
    		(
    		SELECT A.RISKID, B.RETURN_VAL, A.FEATURE_SHAPE.STDistance(B.FEATURE_SHAPE) AS DISTANCE, A.P_KEY
    		FROM [AD_HOC].[DBO].[POINTS] A
    		CROSS JOIN [SPATIAL_DATA].[DBO].[POLYGONS] B
    		WHERE A.RESULT_CODE <> 'N' and A.WIND_TERRITORY IS NULL
    		) X
    	) Y
    WHERE [RANK] = 1

    Thanks

    Thursday, January 8, 2015 9:18 PM

Answers