I am currently working in ArcMap 10.2.2 but would like the SQL code to create the same output as that provided by the Spatial Join tool.

Target feature = point, Join Feature = polyline, Option CLOSEST and a Distance field generated and shown in new shapefile.

I have been struggling with the STDistance function but this is only for "a" distance between a single pair of geometries.

  • do you have your data loaded into SQL Spatial? or...SDE? – DPSSpatial Sep 19 '14 at 21:40
  • The data is spatial and loaded via ArcMap 10.2.2 SDE, I also have SQL 2012. Thank you. – user36197 Sep 21 '14 at 11:46

Here's a link to MSDN on Nearest Neighbour queries. Luckily you are using 2012 so the indexes will be used.

A query like his should be close to what you want.

SELECT pl.ID polyID, pt.ID pointID, pt.Distance
FROM Polygon pl
        SELECT TOP 1 p.ID, p.SHAPE.STDistance(pl.SHAPE) Distance
        FROM Point p
        WHERE p.SHAPE is not null 
        ORDER BY p.SHAPE.STDistance(pl.SHAPE)
        ) pt;

This query will find the closest point to each polygon

  • Dear MickyT: I used the code and generated the join and distance. However, when I compare the results to a the spatial join "CLOSEST" completed in arcmap the distances do not match (not close). Both the point and polyline files are projected in the same system (state plane). Any suggestions? Thank you. – user36197 Sep 22 '14 at 20:38
  • @user36197 Just reread your comment ... can you provide and example of a point and polyline and the distance/units you want – MickyT Sep 22 '14 at 21:05

Many thanks for the quick reply - I have however been able to reconcile the distance values between the two methods (ArcMap/SQL spatial tool/Cross Apply). I apologize for the distraction and appreciate the code and link. I know I am squarely on my way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.