SQL Serverでスパイダーグラフを作成


イメージ
sql-server-nearest-line

乱数テーブルだが確認用、要はポイントデータ格納があるテーブルが二つある場合最後のセレクト文で最近接ラインを引ける。
WGS1984をGeometry型で距離計算に使うべきではないが、例示用。

ShortestLineTo
http://technet.microsoft.com/ja-jp/library/ff929252.aspx

DECLARE @CUSTOMER TABLE 
(
 ID INT PRIMARY KEY,
 Shape Geometry NOT NULL
);

DECLARE @loopCount int = 1000;

DECLARE @minX float = 120;
DECLARE @minY float = 20;
DECLARE @maxX float = 150;
DECLARE @maxY float = 50;
DECLARE @srid int = 4326; -- 4326 = WGS1984

-----上記変数を設定-----

DECLARE @cnt int = 0;
DECLARE @width float = @maxx - @minx;
DECLARE @heigth float = @maxy - @miny;

DECLARE @rnd1 float;
DECLARE @rnd2 float;

WHILE @cnt < @loopCount
 BEGIN
   -- 1Byte = 0 ~ 255  / 2Byte = -2^15 ~ 2^15-1 smallint
   SET @rnd1 = CONVERT(tinyint, CONVERT(varbinary(1), newid())) / 255.0;
   SET @rnd2 = CONVERT(tinyint, CONVERT(varbinary(1), newid())) / 255.0;
   INSERT INTO
    @CUSTOMER (ID, Shape)
   VALUES
    (@cnt, Geometry::Point( @minx + (@width * @rnd1) , @minY + (@heigth * @rnd2) , @srid) )

  SET @cnt = @cnt+1
 END
----- 顧客点 -----

DECLARE @SHOP TABLE
(
 ID INT PRIMARY KEY,
 Shape Geometry NOT NULL
);
SET @loopCount = 100;
SET @cnt = 0;

WHILE @cnt < @loopCount
 BEGIN
   -- 1Byte = 0 ~ 255  / 2Byte = -2^15 ~ 2^15-1 smallint
   SET @rnd1 = CONVERT(tinyint, CONVERT(varbinary(1), newid())) / 255.0;
   SET @rnd2 = CONVERT(tinyint, CONVERT(varbinary(1), newid())) / 255.0;
   INSERT INTO
    @SHOP (ID, Shape)
   VALUES
    (@cnt, Geometry::Point( @minx + (@width * @rnd1) , @minY + (@heigth * @rnd2) , @srid) )

  SET @cnt = @cnt+1
 END

----- 店舗点 -----

/*
SELECT * FROM @CUSTOMER;
SELECT * FROM @SHOP;
*/

SELECT
 CS.ID,
 CS.SHOP_ID,
 CS.Shape.ShortestLineTo(SP.Shape) LINE
FROM
(
 SELECT
  *,
  (SELECT TOP 1 ID FROM @SHOP AS SHOP ORDER BY SHOP.Shape.STDistance(CUSTOMER.Shape)) SHOP_ID
 FROM
  @CUSTOMER AS CUSTOMER
) CS
LEFT OUTER JOIN
 @SHOP SP
ON
 SP.ID = CS.SHOP_ID
カテゴリー: 開発 タグ: パーマリンク