Elementary SQL STDistance Implementation?

Question

I know that with SELECT @source.STDistance(@target)I can derive the distance between two spatial objects, and more complex with something like this, where I can wrap up nearest neighbor in a trigger and get values from nearest neighbor. I'm afraid I may have over-complicated this to the point where I can't figure out something as simple as getting not only the "name" of nearest neighbor, but how far it is to it? How would I extract that distance as part of this and write that value along with the name?

ALTER TRIGGER [dbo].[IMPORT_RAW_WILD_BEAR_GPS_COLLAR]
ON [dbo].[WILD_BEAR_GPS_COLLAR]
after INSERT,UPDATE NOT FOR REPLICATION
AS
BEGIN
   SET NOCOUNT ON;
  UPDATE p SET 
         SHAPE = CASE WHEN i.SHAPE IS NOT NULL  
        THEN p.SHAPE ELSE Geography::STPointFromText('POINT(' 
          + CAST(p.LON AS VARCHAR(20)) + ' '  
          + CAST(p.LAT AS VARCHAR(20)) + ')', 4269) END, 
      LON = CASE WHEN p.SHAPE IS NULL THEN p.LON ELSE p.SHAPE.Long END, 
      LAT = CASE WHEN p.SHAPE IS NULL THEN p.LAT ELSE p.SHAPE.Lat END,
    QuadName = COALESCE(b.name, p.QuadName),
    Watershed = COALESCE(c.HUC_12_Name, p.Watershed),
    County = COALESCE(d.Name, p.County),
    State= COALESCE(e.Name, p.State),
    NEAR_ROAD = COALESCE(k.FULLNAME, p.NEAR_ROAD),
    NEAR_TRAIL = COALESCE(j.NAME, p.NEAR_TRAIL),
                UNITCODE = 'WILD',
                RESTRICTION = 'UNR',
                UNITNAME = 'WILD',
                DATUM = 'NAD83',
                COORD_SYSTEM = 'GCS',
                COORD_UNITS = 'dd',
                UTM_ZONE = '17',
                NEAR_STREAM = COALESCE(g.GNIS_Name, p.NEAR_STREAM)
                ELEVATION = (SELECT pdata.getValueByLoc(1,p.SHAPE.Long,p.SHAPE.Lat)  FROM [dbo].[DEM10MP])
  FROM  WILD_BEAR_GPS_COLLAR
     AS p

  INNER JOIN 
    inserted AS i
    ON i.OBJECTID = p.OBJECTID
  LEFT OUTER JOIN USGS_24K_TOPOMAP_BOUNDARIES AS b
    ON b.Shape.STIntersects(i.Shape) = 1
    LEFT OUTER JOIN WATERSHEDS AS c
    ON c.Shape.STIntersects(i.Shape) = 1
    LEFT OUTER JOIN WILD_COUNTIES AS d
    ON d.Shape.STIntersects(i.Shape) = 1
    LEFT OUTER JOIN WILD_States AS e
    ON e.Shape.STIntersects(i.Shape) = 1

CROSS APPLY (SELECT TOP 1 GNIS_Name, shape                  
FROM dbo.NHDFLOWLINE WITH(index ([NHD_idx]))                 
WHERE NHDFLOWLINE.Shape.STDistance(i.Shape) IS NOT NULL
ORDER BY NHDFLOWLINE.Shape.STDistance(i.Shape) ASC) as g
CROSS APPLY (SELECT TOP 1 RiverOrder, shape                  
FROM dbo.NHDFLOWLINE WITH(index ([NHD_idx]))                 
WHERE NHDFLOWLINE.Shape.STDistance(i.Shape) IS NOT NULL
ORDER BY NHDFLOWLINE.Shape.STDistance(i.Shape) ASC) as h
CROSS APPLY (SELECT TOP 1 FULLNAME, shape                    
FROM dbo.WILD_ROADS 
/****** force spatial index hint ******/ 
WITH(index ([WILD_ROADS_idx]))                   
WHERE WILD_ROADS.Shape.STDistance(i.Shape) IS NOT NULL 
ORDER BY WILD_ROADS.Shape.STDistance(i.Shape) ASC) as k
CROSS APPLY (SELECT TOP 1 NAME, shape
FROM dbo.WILD_TRAILS
/****** force spatial index hint ******/ 
WITH(index ([WILD_TRAILS_idx]))                   
WHERE WILD_TRAILS.Shape.STDistance(i.Shape) IS NOT NULL 
ORDER BY WILD_TRAILS.Shape.STDistance(i.Shape) ASC) as j

END;

Your question isnt clear. You say already know `SELECT @source.STDistance(@target)` — Juan Carlos Oropeza, Aug 18 '15 at 02:48

score 0 · Answer 1 · answered Aug 18 '15 at 02:53

0

Query Spatial Data for Nearest Neighbor

USE AdventureWorks2012
GO
DECLARE @g geography = 'POINT(-121.626 47.8315)';
SELECT TOP(7) SpatialLocation.ToString(), City, SpatialLocation.STDistance(@g)
FROM Person.Address
WHERE SpatialLocation.STDistance(@g) IS NOT NULL
ORDER BY SpatialLocation.STDistance(@g);

answered Aug 18 '15 at 02:53

Juan Carlos Oropeza

47,252
12
78
118

The question is how to implement it in a trigger as the one posted, and repeat the search for (n) objects. I'm quite aware of the example provided by Microsoft. – tpcolson Aug 18 '15 at 10:28
You have to be specific. What part do you have problem? What have you done? Show us sample data, show us your work. What is wrong with the query you already have?. What is your desire output? What mean repeat (n) ? Do you know triggers? or your problem is changing data before save or searching the neighbor? – Juan Carlos Oropeza Aug 18 '15 at 14:41

score 0 · Accepted Answer · answered Aug 18 '15 at 12:41

Answer dug up from an old post I remember when trying to solve another nearest neighbor problem:

update s 
set 
[NEAR_TRAIL] = fname,
[DIST_TRAIL] = Shape.STDistance(fshape)
from(
Select
[dbo].[GRSM_BEAR_GPS_COLLAR].*,
fnc.Name as fname,
fnc.Shape as fShape
from
[dbo].[GRSM_BEAR_GPS_COLLAR]
CROSS APPLY (SELECT TOP 1 Name, shape                   
FROM [dbo].[GRSM_TRAILS] WITH(index ([GRSM_TRAILS_idx]))                
WHERE [GRSM_TRAILS].Shape.STDistance([dbo].[GRSM_BEAR_GPS_COLLAR].Shape) IS NOT NULL
                  ORDER BY GRSM_Trails.Shape.STDistance([dbo].[GRSM_BEAR_GPS_COLLAR].Shape) ASC) fnc)s;

Elementary SQL STDistance Implementation?

2 Answers2