分辨 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 後,效能提升數十倍

發表迴響

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

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 位部落客按了讚: