SQL Server 2008 的空間資料

以 .NET 實做的空間(Spatial)資料格式:提供兩種資料型態。可在資料庫內結合地理資訊,並搭配空間索引(Spatial Index)的特殊階層式索引結構,以有效存取資料。

  • Geometry 用在 2 維描述平面地球,符合如 Open Geospatial Consortium(OGC)對空間資料的定義標準,存放多邊形(polygon)、線(line)和點(point)的資訊。
  • Geography 則以 3 維立體的描述圓地球,存放緯度(latitude)和經度(longitude)的座標資料,符合產業標準 WGS84,提供 GPS 對地球表面定位的資訊。

存放的幾何形狀有:POINT, MULTIPOINT, LINESTRING, MULTILINESTRING, POLYGON, MULTIPOLYGON, GEOMETRYCOLLECTION

image

雖結構有 11 ,但可成為執行個體的只有 7 種,雖然圖中的根為 Geometry,但 Geography 亦是如此

而表現資料的格式有三種:

  • Well Know Text(WKT):易讀,但不容易用程式產生,因為要注意格式。
    • 以文字描述幾何圖形,不分大小寫
    • 點是以空白相隔的兩個數字,如:(X Y)
      • 若是 Geography,則 X 代表經度,Y 代表緯度
    • 可以在 X Y 之後添加 Z 和 M 屬性,Z 和 M 可以不填,但 X Y 不行
    • 多個點間以小括號括起,並以逗號相隔
    • 若是 POLYGON, MULTIPOLYGON, GEOMETRYCOLLECTION,再以小括號括起多個點所成集合
    • 一組資料可以有多個多邊形,但邊界不能重疊
    • 範例
      • declare @point geometry = ‘POINT(100 10)’
      • declare @line geometry = ‘LINESTRING(0 0,10 10)’
      • declare @square geometry = ‘POLYGON((0 0,10 0,10 10,0 10,0 0))’
      • declare @donught geometry = ‘POLYGON((0 0,10 0,10 10,0 10,0 0),(4 4,4 6,6 6,6 4,4 4))’
      • declare @vs geometry = ‘MULTILINESTRING((0 10,5 0,10 10),(20 10,25 0,30 10))’
      • declare @rockets geometry = ‘MULTIPOLYGON(((0 0,5 10,5 30,15 40,25 30,25 10,30 0,20 0,15 5, 5 0,0 0)),((10 0,15 10,15 30,25 40,35 30,35 10,40 0,40 0,25 5, 15 0,10 0)))’
      • declare @glasses geometry = ‘GEOMETRYCOLLECTION(LINESTRING(0 20,5 10),POLYGON((5 5,5 15,10 20,20 20,25 15,25 5,20 0,10 0,5 5)),POLYGON((35 5,35 15,40 20,50 20,55 15,55 5,50 0,40 0,35 5)),LINESTRING(55 10,60 20),LINESTRING(25 15,35 15))’
  • Well Known Binary(WKB):以 2 進位的位元組表現,透過編碼解釋結構,加上數值資料,各種幾何結構定義如下

    Point = [Byte order][Type][X value][Y value]

    MultiPoint = [Byte order][Type][PointCount]<Point1><Point2>

    Line = [Byte order][Type][PointCount][X Value][YValue][X value][Y value]…..

    MultiLine = [Byte order][Type][LineCount]<Line1><Line2> …..

    Polygon = [Byte order][Type][Polygon Count][PointCount][X Value][YValue][X value][Y value]…..

    MultiPolygon = [Byte order][Type][Polygon Count]<Polygon1><Polygon2>….

    Geometry Collection = [Byte order][Type][Shape Count]<Shape1><Shape2>…..

    • 而 Type 的定義如下:

      • Unknown = 0
      • Point = 1
      • LineString = 2
      • Polygon = 3
      • MultiPoint = 4
      • MultiLineString = 5
      • MultiPolygon = 6
      • GeometryCollection = 7

      範例如下:

      declare @p geometry = geometry::STGeomFromWKB

            AABBBBBBBBCCCCCCCCDDDDDDDD
      (0x00000000030000000200000005

      X1X1X1X1X1X1X1X1Y1Y1Y1Y1Y1Y1Y1Y1X2X2X2X2X2X2X2X2Y2Y2Y2Y2Y2Y2Y2Y2
      0000000000000000000000000000000000000000000000004010000000000000

      X3X3X3X3X3X3X3X3Y3Y3Y3Y3Y3Y3Y3Y3X4X4X4X4X4X4X4X4Y4Y4Y4Y4Y4Y4Y4Y4
      4010000000000000401000000000000040100000000000000000000000000000

      X5X5X5X5X5X5X5X5Y5Y5Y5Y5Y5Y5Y5Y5
      00000000000000000000000000000000

      DDDDDDDD
      00000005

      X1….

      3FF00000000000003FF00000000000003FF00000000000004008000000000000

      4008000000000000400800000000000040080000000000003FF0000000000000

      3FF00000000000003FF0000000000000,0)

      print @p.ToString()

      上述資料經 SQL Server 2008 @p.ToString() 印出的 WKT 表示法為:
      POLYGON ((0 0, 0 4, 4 4, 4 0, 0 0), (1 1, 1 3, 3 3, 3 1, 1 1))

      範例中上方的 ABC 各代表意義如下:

      • A:位元組順序(byte order)
      • B:幾何形狀的格式(type),此處 3 代表多邊形(polygon)
      • C:子形狀(sub shape)的數量,此處 2 代表有兩個多邊形
      • D:子形狀的點數數量,此處 5 代表由五個點組成多邊形
      • X1 Y1…Xn Yn 分別代表 X Y 所標示的點
      • 5 個點過後,再度標示下一個多邊形的點數,然後就是各點的資料

      declare @p geometry = geometry::STGeomFromWKB
      (0x000000000300000002000000050000000000000000000000000000000000000000000000004010000000000000401000000000000040100000000000004010000000000000000000000000000000000000000000000000000000000000000000053FF00000000000003FF00000000000003FF000000000000040080000000000004008000000000000400800000000000040080000000000003FF00000000000003FF00000000000003FF0000000000000,0)

      Geography Markup Language(GML) 以 XML 格式描述:.

      declare @p geometry;

      set @p = geometry::GeomFromGml(‘<Point xmlns="http://www.opengis.net/gml"><pos>10 10</pos></Point>’,0);

       

      透過 T-SQL 組 WKB 的內容

      declare @i int = cast(cast(1 as binary(8)) as int)

      declare @OuterX1 float = 0, @OuterY1 float = 0
      declare @OuterX2 float = 400, @OuterY2 float = 0
      declare @OuterX3 float = 400, @OuterY3 float = 400
      declare @OuterX4 float = 0, @OuterY4 float = 400
      declare @OuterX5 float = 0, @OuterY5 float = 0

      declare @InnerX1 float = 100, @InnerY1 float = 100
      declare @InnerX2 float = 300, @InnerY2 float = 100
      declare @InnerX3 float = 300, @InnerY3 float = 300
      declare @InnerX4 float = 100, @InnerY4 float = 300
      declare @InnerX5 float = 100, @InnerY5 float = 100

      declare @p geometry


      print 0x000000000300000002
        + 0x00000005
        + cast(@OuterX1 as binary(8)) + cast(@OuterY1 as binary(8))
        + cast(@OuterX2 as binary(8)) + cast(@OuterY2 as binary(8))
        + cast(@OuterX3 as binary(8)) + cast(@OuterY3 as binary(8))
        + cast(@OuterX4 as binary(8)) + cast(@OuterY4 as binary(8))
        + cast(@OuterX5 as binary(8)) + cast(@OuterY5 as binary(8))
        + 0x00000005
        + cast(@InnerX1 as binary(8)) + cast(@InnerY1 as binary(8))
        + cast(@InnerX2 as binary(8)) + cast(@InnerY2 as binary(8))
        + cast(@InnerX3 as binary(8)) + cast(@InnerY3 as binary(8))
        + cast(@InnerX4 as binary(8)) + cast(@InnerY4 as binary(8))
        + cast(@InnerX5 as binary(8)) + cast(@InnerY5 as binary(8))
      /*
      0x00000000030000000200000005000000000000000000000000000000004079000000000000000000000000000040790000000000004079000000000000000000000000000040790000000000000000000000000000000000000000000000000005405900000000000040590000000000004072C0000000000040590000000000004072C000000000004072C0000000000040590000000000004072C0000000000040590000000000004059000000000000
      */
      set @p = geometry::STGeomFromWKB( 
          0x000000000300000002
        + 0x00000005
        + cast(@OuterX1 as binary(8)) + cast(@OuterY1 as binary(8))
        + cast(@OuterX2 as binary(8)) + cast(@OuterY2 as binary(8))
        + cast(@OuterX3 as binary(8)) + cast(@OuterY3 as binary(8))
        + cast(@OuterX4 as binary(8)) + cast(@OuterY4 as binary(8))
        + cast(@OuterX5 as binary(8)) + cast(@OuterY5 as binary(8))
        + 0x00000005
        + cast(@InnerX1 as binary(8)) + cast(@InnerY1 as binary(8))
        + cast(@InnerX2 as binary(8)) + cast(@InnerY2 as binary(8))
        + cast(@InnerX3 as binary(8)) + cast(@InnerY3 as binary(8))
        + cast(@InnerX4 as binary(8)) + cast(@InnerY4 as binary(8))
        + cast(@InnerX5 as binary(8)) + cast(@InnerY5 as binary(8)),0)

      print @p.ToString()
      /*
      POLYGON ((0 0, 400 0, 400 400, 0 400, 0 0), (100 100, 300 100, 300 300, 100 300, 100 100))
      */

      透過 Simon Sabin 提供的 spatialViewer 檢視該多邊形如下

      image

      Geography 資料型態是以經緯度來表示,但相關函數需要換算長、寬距離…等。例如從 http://mikeo.co.uk/demo/sqlspatial/default.aspx 取得台北與高雄兩點的緯度與經度:

      image

      直接透過 Geography 資料型態的函數換算距離:

      DECLARE @Taipei GEOGRAPHY=’POINT(25.015928763367846 121.58569335937504)’  — 宣告台北的緯度與經度
      DECLARE @Kaohsiung GEOGRAPHY=’POINT(22.614010874370265 120.33325195312501)’
      SELECT @Kaohsiung.STDistance(@Taipei) — 取回在地球上兩點間的距離
      回傳值:295047.011615369(單位:公尺)

      而非簡單的兩個座標點:

      DECLARE @x FLOAT=(25.015928763367846-22.614010874370265)
      DECLARE @y FLOAT=(121.58569335937504-120.33325195312501)
      SELECT sqrt(square(@x)+square(@y))
      回傳值:2.70884090001169

      若僅是簡單的兩個座標點,也就是一般的平面幾何

      DECLARE @Taipei GEOMETRY=’POINT(25.015928763367846 121.58569335937504)’
      DECLARE @Kaohsiung GEOMETRY=’POINT(22.614010874370265 120.33325195312501)’
      SELECT @Kaohsiung.STDistance(@Taipei)
      其回傳值就是上述的 2.70884090001169

      另外,在標示 Geometry 的座標點時,雖然可以標示 Z 座標和度量單位(M measure),但實際在運算時,並沒有作用,僅讓使用者可以透過屬性參考到

      DECLARE @T GEOMETRY=’POINT(0 0 0 10)’
      SELECT @T.STX
      SELECT @T.STY
      SELECT @T.Z  — 取出 Z 的資料
      SELECT @T.M  –取出 m 的資料

      DECLARE @K GEOMETRY=’POINT(10 10 10 10)’
      SELECT @K.STDistance(@T)
      結果依然是 200 開根號:14.142135623731

      3 Comments

      1. Unknown
        Posted 2008 年 09 月 07 日 at 05:13:35 | Permalink | 回覆

        Hi,Do you need advertising displays, digital signages, mp4 advertisement players, SD card players and advertisement LCD displays? Please go Here:www.amberdigital.com.hk(Amberdigital).we have explored and developed the international market with professionalism. We have built a widespread marketing network, and set up a capable management team dedicated to provide beyond-expectation services to our customers.
        amberdigital Contact Us
        E-mail:sstar@netvigator.com
        website:www.amberdigital.com.hk
        alibaba:amberdigital.en.alibaba.com[dc

      2. Unknown
        Posted 2008 年 09 月 27 日 at 13:39:28 | Permalink | 回覆

        Hi,Do you need digital signage, digital sign, ad players and ad displays? Please go Here:www.amberdigital.com.hk(Amberdigital).we have explored and developed the international market with professionalism. We have built a widespread marketing network, and set up a capable management team dedicated to provide beyond-expectation services to our customers.
        amberdigital Contact Us
        website:www.amberdigital.com.hk
        alibaba:amberdigital.en.alibaba.com[cabfcgibcihbid]

      3. Unknown
        Posted 2008 年 10 月 24 日 at 00:20:06 | Permalink | 回覆

        Hi,Do you need digital signages, advertising displays, digital sign, advertisement displays and advertising players? Please go Here:www.amberdigital.com.hk(Amberdigital).we have explored and developed the international market with professionalism. We have built a widespread marketing network, and set up a capable management team dedicated to provide beyond-expectation services to our customers.
        amberdigital Contact Us
        website:www.amberdigital.com.hk
        alibaba:amberdigital.en.alibaba.com[hheddjgeeghea]

      發表迴響

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

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