首先，我们创建一个计算的geography列，用于存储位置坐标。我们将使用此列让SQL Server为我们计算距离：

ALTER TABLE #temp
ADD Point_Geolocation AS geography::STPointFromText('POINT(' + CAST(LONGITUDE AS VARCHAR(100))+ ' ' + CAST(LATITUDE AS VARCHAR(100)) +')', 4326) PERSISTED

其次，创建一个包含所有邻近位置的表：

IF OBJECT_ID('tempdb..#Nearby_Points') IS NOT NULL DROP TABLE #Nearby_Points
CREATE TABLE #Nearby_Points (
        ID_1 VARCHAR(10) NOT NULL,
        ID_2 VARCHAR(10) NOT NULL,
        PRIMARY KEY (ID_1, ID_2)
)

INSERT INTO #Nearby_Points
(
    ID_1,
    ID_2
)
SELECT   t1.ID AS p1_ID
        ,t2.ID AS p2_ID
FROM #temp t1
    INNER JOIN #temp t2
        ON t1.ID < t2.ID
WHERE t1.Point_Geolocation.STDistance(t2.Point_Geolocation) < 40 -- Specify distance criteria here

-- SELECT * FROM #Nearby_Points

**注：**对于100k+坐标，我们需要进行大约50亿次计算：(100,000 ^ 2) / 2。执行上述查询可能需要一段时间。

第三，让我们创建一个表来存储我们的集群列表：

IF OBJECT_ID('tempdb..#Clusters') IS NOT NULL DROP TABLE #Clusters
CREATE TABLE #Clusters(
    Cluster_ID INT NOT NULL,
    Point_ID VARCHAR(10) NOT NULL,
    PRIMARY KEY(Cluster_ID, Point_ID)
);

-- This index may improve performance a little
CREATE NONCLUSTERED INDEX IX_Point_ID ON #Clusters(Point_ID);

最后，下面的代码将：
1.为第一个不在簇中的点创建新簇。
1.重复地重新扫描聚类表，并向现有聚类添加附加点，直到每个聚类包含应当属于它的所有点。
1.转到上面的步骤1并重复，直到没有创建新的群集。

DECLARE @Rowcount INT

INSERT INTO #Clusters
(
    Cluster_ID,
    Point_ID
)
SELECT   COALESCE((SELECT MAX(Cluster_ID) FROM #Clusters),0) + 1
        ,MIN(np.ID_1)
FROM #Nearby_Points np
WHERE np.ID_1 NOT IN (SELECT Point_ID FROM #Clusters)
HAVING MIN(np.ID_1) IS NOT NULL

SET @Rowcount = @@ROWCOUNT

WHILE @Rowcount > 0
BEGIN

    WHILE @Rowcount > 0
    BEGIN

            INSERT INTO #Clusters
            (
                Cluster_ID,
                Point_ID
            )
            SELECT   Cluster_ID
                    ,Point_ID
            FROM (
                    SELECT   np.ID_2 AS Point_ID
                            ,c.Cluster_ID
                    FROM #Nearby_Points np
                        INNER JOIN #Clusters c
                            ON np.ID_1 = c.Point_ID

                    UNION

                    SELECT   np.ID_1
                            ,c.Cluster_ID
                    FROM #Nearby_Points np
                        INNER JOIN #Clusters c
                            ON np.ID_2 = c.Point_ID
            ) vals
            WHERE NOT EXISTS (
                    SELECT 1
                    FROM #Clusters
                    WHERE Cluster_ID = vals.Cluster_ID
                    AND Point_ID = vals.Point_ID
            )

            SET @Rowcount = @@ROWCOUNT
    END

    INSERT INTO #Clusters
    (
        Cluster_ID,
        Point_ID
    )
    SELECT   COALESCE((SELECT MAX(Cluster_ID) FROM #Clusters),0) + 1
            ,MIN(np.ID_1)
    FROM #Nearby_Points np
    WHERE np.ID_1 NOT IN (SELECT Point_ID FROM #Clusters)
    HAVING MIN(np.ID_1) IS NOT NULL

    SET @Rowcount = @@ROWCOUNT
END

瞧

SELECT *
FROM #Clusters c


|Cluster_ID | Point_ID|
|-----------|---------|
|         1 | ID1     |
|         1 | ID2     |
|         1 | ID3     |
|         1 | ID4     |
|         1 | ID5     |
|         1 | ID6     |
|         2 | ID7     |
|         2 | ID8     |
|         2 | ID9     |