基本的SQL STDistance实现

Question

我知道，通过SELECT @source.STDistance(@target)，我可以推导出两个空间对象之间的距离，更复杂的是，我可以将最近邻封装在触发器中，并从最近邻获取值。我担心我可能把这个问题复杂化了，以至于我不能弄清楚一些简单的事情，比如不仅要知道最近邻居的"名字"，还要知道到它有多远?我该如何把这个距离作为这个的一部分并把这个值和名字一起写出来呢?

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;

Answer 1

查询最近邻空间数据

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);

Answer 2

答案是我记得在解决另一个最近邻问题时从一个旧帖子中挖出来的:

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;