空間線條資料加入新點

粗略幫朋友解法

DECLARE @g geography;
DECLARE @h geography;
SET @g = ‘LINESTRING(121.7392008 25.1324142,121.7386871 25.1316420,121.7377320 25.1300646)’;
SET @h = ‘POINT(121.7581735 25.1308697)’;
DECLARE @i int=1
CREATE TABLE #t(PK INT,Lon FLOAT,Lat FLOAT,Distance FLOAT)
WHILE @i< @g.STNumPoints()+1
BEGIN
    INSERT #t VALUES(@i*10,@g.STPointN(@i).Long,@g.STPointN(@i).Lat,@g.STDistance(@h))
    SET @i+=1
END
DECLARE @pk INT=5
SELECT TOP 1 @pk=PK+@pk FROM (SELECT TOP 2 * FROM #t ORDER BY Distance) t ORDER BY PK
INSERT #t VALUES(@pk,@h.Long,@h.Lat,NULL)
DECLARE @str VARCHAR(1000)=’LINESTRING(‘
SELECT @str=@str + CONVERT(VARCHAR(50),Lon,2) + ‘ ‘ + CONVERT(VARCHAR(50),Lat,2) + ‘,’ FROM
#t ORDER BY PK
SET @str=LEFT(@str,LEN(@str)-1) + ‘)’
PRINT CONVERT(Geography,@str).ToString()
SELECT CONVERT(Geography,@str)
SELECT * FROM #t

DROP TABLE #t

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: