[T-SQL]利用cursor將SQL中的geography line的point一一取出

對SQL Server的geography的型態資料又愛又恨
一方面是他在SQL Server裡真的很好用又方便
一方面是如果要把資料匯出的話其它系統完全認不得 又要另外處理
今天是因為希望BI工具接geography的資料 在地圖上畫出我要的點跟線 但BI工具是另外一家廠商的 完全不認geography的型態資料
而畫出點很容易 線就有點麻煩
於是利用cursor將line裡面的點一一取出並排序號 這樣子我們的BI工具才可以畫出一條連接的線
以下是我自已寫的小範例
就是利用cursor把一行一行的line取出來 取得這一條件共有幾個點 STNumPoints() 然後用迴圈把每一個點的經緯度取出STPointN(@linePointStart).Lat, STPointN(@linePointStart).Long

	DECLARE @linePointStart int
	DECLARE @linePoint int
	DECLARE @No int
	SET @linePointStart = 0
	DECLARE point CURSOR
	FOR SELECT [No], geogObject.STNumPoints() FROM yourdb.dbo.LocationGeo --Get the point number of each line
	OPEN point
	FETCH NEXT FROM point INTO @No, @linePoint
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
	SELECT @linePointStart = 1
	WHILE (@linePointStart <= @linePoint)
	BEGIN
	SELECT @linePointStart --just make sure which point it is now
	INSERT INTO yourdb.dbo.LocationGeoLatLong --save lat and long of each point to a new table
	SELECT *, @linePointStart as pointOrder, geogObject.STPointN(@linePointStart).Lat AS lat, GeoObject.STPointN(@linePointStart).Long AS long
	FROM yourdb.dbo.LocationGeo WHERE [No] = @No
	SELECT @linePointStart = @linePointStart + 1
	END
	FETCH NEXT FROM point INTO @No, @linePoint
	END
	CLOSE point
	DEALLOCATE point

view raw GeoLineToPoint.sql hosted with ❤ by GitHub