分辨 Geography 內的子物件

 

–在 Liniked Server 間傳遞空間資料，需要先轉成 varbinary(max)

SELECT  … ,convert(geography,[LineMapGeog]) [LineMapGeog] 
      … into dbo.[table]
  FROM openquery([LinkedServer],’select …      ,convert(varbinary(max),[LineMapGeog]) [LineMapGeog] … from [db].[dbo].[table]’)
     
     
      select * from [table]
      
      drop table #tmp
     

–以 XML 表現 Geography 資料
      select ItemID,GeoColumn.AsGml() Gml into #tmp from [table] 

–以單筆紀錄，且是 Geography 資料類型呈現  MultiCurve\CurveMembers 或 MultiGeometry\geometryMembers … 等物件內的子物件 LineString   
      select top 5000 t.ItemID,geography::GeomFromGml(n.c.query(‘.’),4326) from #tmp t
      cross apply Gml.nodes(N’declare default element namespace "http://www.opengis.net/gml";
      //LineString’) n(c)
     

–不要 LineString，傳回其他的子物件
      select geography::GeomFromGml(n.c.query(‘.’),4326) from #tmp
      cross apply Gml.nodes(N’declare default element namespace "http://www.opengis.net/gml";
     //geometryMembers/*[local-name()!="LineString"]‘) n(c)
      where ItemID=8
     

–取回各種 Geography 各種物件類型
      select geography::GeomFromGml(n.c.query(‘.’),4326).STGeometryType() ‘NodeType’
,geography::GeomFromGml(n.c.query(‘.’),4326) ‘GeogCodi’ from #tmp
      cross apply Gml.nodes(N’declare default element namespace "http://www.opengis.net/gml";
      /*/*/*‘) n(c)
      where ItemID=8
      order by NodeType desc

 

 

附帶一提，SQLExpress 雖然可以處理空間資料，但由於受限 CPU 和 RAM，資料稍多，稍大，就效能奇差無比。換到 Standard 後，效能提升數十倍

空間線條資料加入新點

粗略幫朋友解法

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