I have a table named input_points in SQL Server that contains a collection of points represented in geography data type, with two attributes: id (which allows NULL) and Location (which is in hex format). I want to use K-Means clustering algorithm to group these points into N clusters. I have tried to convert the hex format to latitude and longitude, but I'm getting an error when using the ST_ClusterKMeans function. Here's my current query:
DECLARE @k int = 5; -- number of clusters
DECLARE @tolerance float = 1.0E-4; -- tolerance
DECLARE @max_iter int = 100; -- maximum number of iterations
-- Cluster the points using K-Means algorithm and the cluster id is an incremental number starting from 0
SELECT id, shape, cluster_id
INTO clustered_points -- create a new table to store the clustered points
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS id, shape
FROM input_points
) AS points
CROSS APPLY dbo.ST_ClusterKMeans(points.shape, @k, @tolerance, @max_iter) AS cluster_id;
However, I'm getting an error message "Invalid object name 'dbo.ST_ClusterKMeans'". I have heard that I need to install the Spatial Tools for SQL Server library, but I'm not sure how to use it or if it's the right solution for my problem. Can anyone help me to use K-Means clustering with geography data in SQL Server and point me in the right direction? Thank you.
