Welcome to MapForums!

Register, sign in, or use Facebook Connect above to join in and participate in the forum.

When you are signed in, this message, the ads in this row, and the red-underlined link ads all go away.

Subscribe to receive our newsletter.
Subscribe Unsubscribe
Results 1 to 1 of 1

Virtual Earth & SQL Server 2008 - Part 2: Spatial Data Management in SQL Server 2008 (1/3)

This is a discussion on Virtual Earth & SQL Server 2008 - Part 2: Spatial Data Management in SQL Server 2008 (1/3) within the Bing Maps Blogs & Tweets forums, part of the Blogs category; Spatial Data Management in SQL Server 2008 Spatial Data Types Since the Community Technology Preview (CTP) 5 which was released ...

  1. #1
    Johannes Kebeck's Blog is offline Senior Member Green Belt
    Join Date
    Sep 2007
    Posts
    154

    Virtual Earth & SQL Server 2008 - Part 2: Spatial Data Management in SQL Server 2008 (1/3)

    Spatial Data Management in SQL Server 2008

    Spatial Data Types

    Since the Community Technology Preview (CTP) 5 which was released in November 2007, SQL Server 2008 supports two spatial data types: geometry and geography. Both data types are implemented as .NET Common Language Runtime (CLR) data types. The geometry data type is compliant with the Simple Features for SQL Specification, Version 1.1.0 according to the Open Geospatial Consortium (OGC) and supports planar or Euclidean (flat-earth) data. It is best used if you describe positions in coordinate systems which have the same scale in X- and Y-direction, such as the OSGB36 coordinate system mentioned before. The geography data type, on the other side, stores ellipsoidal (round-earth) data, such as latitude and longitude coordinates; it is thus a natural fit for the requirements of Virtual Earth.

    The difference becomes immediately clear if we have a look at the map. In the example below we see the different effects of a calling the STBuffer-function on a geometry…

    SELECT Geometry.STAsText() 
    FROM Test01
    WHERE id=1
    UNION
    SELECT
    Geometry.STBuffer(0.0001).STAsText()
    FROM Test01
    WHERE id=1

    …and a geography…

    SELECT Geography.STAsText() 
    FROM Test01
    WHERE id=1
    UNION
    SELECT
    Geography.STBuffer(20).STAsText()
    FROM Test01
    WHERE id=1


    ...data type. The first thing you notice is, that we have to use the same units as used in the spatial reference system for the geometry data type, i.e. we have to enter the size of the buffer in decimal degrees. That is something that doesn’t make sense to most users. On the other side the geography data type takes the size of the buffer in meters. Since the scale for WGS84 coordinate systems is different in X- and Y-directions and furthermore even different depending on the latitude, you see a distortion when using ellipsoidal data such as WGS84 with the geometry data type:



    Coming back to the units of our data, we see that a result in degrees is most of the times not reasonable when it comes to measuring distances and areas. If we use the geometry data type to measure the area of a building like this:

    DECLARE @g geometry;
    SET @g = geometry::STGeomFromText('POLYGON((
    51.461408935417026 -0.9266281127929573,
    51.46091427889742 -0.9263437986373904,
    51.46108473546569 -0.9255471825599749,
    51.46157939013779 -0.9258341789245446,
    51.461408935417026 -0.9266281127929573))'
    , 4326);
    SELECT @g.STArea();

    We get an unusable result of 4.420787637913E-07 “square degrees”. However, if we use the geography data type:

    DECLARE @g geography;
    SET @g = geography::STGeomFromText('POLYGON((
    51.461408935417026 -0.9266281127929573,
    51.46091427889742 -0.9263437986373904,
    51.46108473546569 -0.9255471825599749,
    51.46157939013779 -0.9258341789245446,
    51.461408935417026 -0.9266281127929573))'
    , 4326);
    SELECT @g.STArea();

    e get a perfectly reasonable result of 3418.3 square meters.

    The bottom line: It makes more sense, in most cases, to use the geography data type if you use Virtual Earth.

    Please note: not all spatial functions are supported for both geography and geometry data types. For further reference have a look at the documentation.

    Supported Geometries


    SQL Server supports all geometries as defined in the OGC Simple Feature Specification:

    image

    On the other side Virtual Earth only supports Points, Lines and Polygons. Thus we need to split all multi-geometries into their components. Fortunately SQL Server 2008 supports us here with the necessary spatial-functions, e.g. if we have a MULTIPOLYGON we can retrieve the individual POLYGONs which are part of it:

    SELECT GeomCol1.STAsText() FROM FeatureDemo WHERE ID=6;
    SELECT GeomCol1.STGeometryN(1).STAsText() FROM FeatureDemo WHERE ID=6

    Here is the result of these sample queries:

    MULTIPOLYGON (((...)), (()))
    POLYGON ((...))




    Click here to view the article.
    Last edited by Eric Frost; 03-03-2008 at 08:21 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Virtual Earth & SQL Server 2008 - Part 2: Spatial Data Management in SQL Server 2008 (3/3)
    By Johannes Kebeck's Blog in forum Bing Maps Blogs & Tweets
    Replies: 0
    Last Post: 03-02-2008, 01:40 PM
  2. Virtual Earth & SQL Server 2008 - Part 2: Spatial Data Management in SQL Server 2008 (2/3)
    By Johannes Kebeck's Blog in forum Bing Maps Blogs & Tweets
    Replies: 0
    Last Post: 03-02-2008, 01:40 PM
  3. Virtual Earth & SQL Server 2008 - Part 1: Introduction (2/2)
    By Johannes Kebeck's Blog in forum Bing Maps Blogs & Tweets
    Replies: 0
    Last Post: 03-02-2008, 07:30 AM
  4. Virtual Earth & SQL Server 2008 - Part 1: Introduction (1/2)
    By Johannes Kebeck's Blog in forum Bing Maps Blogs & Tweets
    Replies: 0
    Last Post: 03-02-2008, 07:30 AM
  5. MapDotNet Server 6.5 Supports SQL Server 2008 and New Virtual Earth Features
    By VE For Government in forum Bing Maps Blogs & Tweets
    Replies: 0
    Last Post: 11-12-2007, 05:42 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132