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 (3/3)

This is a discussion on Virtual Earth & SQL Server 2008 - Part 2: Spatial Data Management in SQL Server 2008 (3/3) within the Bing Maps Blogs & Tweets forums, part of the Blogs category; Spatial Indexing When to use a Spatial Index? Spatial indexes improve significantly the performance of certain type of spatial relationship ...

  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 (3/3)

    Spatial Indexing

    When to use a Spatial Index?

    Spatial indexes improve significantly the performance of certain type of spatial relationship queries but they don’t have an impact on all spatial functions so it is certainly a good idea to choose wisely when and what to index. Geography data types benefit for the following queries from a spatial index:

    • geography1.STIntersects(geography2)
    • geography1.STEquals(geography2)
    • geography1.STDistance(geography2)
    • geography1.STDistance(geography2)

    Geometry data types on the other side can make use of spatial indexes for the following functions:

    • geometry1.STContains(geometry2)
    • geometry1.STDistance(geometry2)
    • geometry1.STDistance(geometry2)
    • geometry1.STEquals(geometry2)
    • geometry1.STIntersects(geometry2)
    • geometry1. STOverlaps (geometry2)
    • geometry1.STTouches(geometry2)
    • geometry1.STWithin(geometry2)

    What exactly is a Spatial Index?

    The spatial index in SQL Server 2008 is organized as a B-tree index which means the spatial data are represented in the linear order of a B-tree. The number of cells in X- and Y-direction is always the same and can be 4 x 4 for LOW grid density, 8 x 8 for MEDIUM grid density or 16 x 16 for a HIGH grid density. There are 4 indexing levels and a grid density can be set for each individual level.





    The SQL Server uses a tessellation algorithm to dissect the spatial objects into cells. During this process it uses the following rules:

    • Cells are numbered from within a level from left to right and from top to bottom
    • As soon as a cell is entirely covered by the spatial objects for this cell the tessellation will abort for this particular cell. In the example below we have a hexagon in cell 4 on level 1. In level 2 this hexagon completely covers cell 7. Further tessellation for this cell, which is then globally addressed as 4.7 is not necessary. The other cells, which are partly covered by the spatial object will be further tessellated in the subsequent levels.



    • While creating the spatial index we define how many cells per object we want to have as a maximum. As soon as this is limit is reached or exceeded no further levels will be tessellated.
    • The index only contains the deepest cell. In the example below we have an object which is amongst others covered by the cell 4.4.4.7. Form this information we can deduct that it is also covered by the cells 4.4.4 in level 3, 4.4 in level 2 and 4 in level 1. Thus it will not be necessary to store the information for lower levels in the index.




    The tessellation itself works different for geography and geometry data types. Geometries are projected to the “flat-earth” and thus we can further restrict the indexing area by defining the minimum bounding rectangle which is basically an envelope around all our geometries.





    For geography data types this is not necessary. Here we have a projection from the “round earth” to a flat object as described below. Basically we project the “round earth” on 2 pyramids, flatten these pyramids and then start the tessellation. As a result we have a restriction that a spatial object must be fully located in a single, logical hemisphere.





    How to Create a Spatial Index

    To create a spatial index we need to have a clustered primary key on a non-spatial column in the database table (such as on an “id” field). The spatial index itself is created with the following SQL-statement for a geometry:

    CREATE SPATIAL INDEX SI_AddressPoint
    ON AddressPoint(Geom)
    USING GEOMETRY_GRID
    WITH (
    BOUNDING_BOX = (xmin=-4.2, ymin=50.3, xmax=-4.1, ymax=50.4),
    GRIDS = (LEVEL_1 = LOW,
    LEVEL_2 = LOW,
    LEVEL_3 = HIGH,
    LEVEL_4 = HIGH),
    CELLS_PER_OBJECT = 16)

    For a geography the syntax is slightly different and as mentioned above we do not need a BOUNDING_BOX:

    CREATE SPATIAL INDEX SI_AddressPoint
    ON AddressPoint(Geom)
    USING GEOGRAPHY_GRID
    WITH (
    GRIDS = (LEVEL_1 = LOW,
    LEVEL_2 = LOW,
    LEVEL_3 = HIGH,
    LEVEL_4 = HIGH),
    CELLS_PER_OBJECT = 16)

    The SQL-statement can also be declared with graphical support through the SQL Server Management Studio. When you are in the design view of a table, right-click on the row for the spatial column and select “Spatial Indexes” from the context menu.





    You will now find a dialogue which allows you to configure the index.






    *



    Click here to view the article.
    Last edited by Eric Frost; 03-03-2008 at 08:38 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 3: Getting Started with Virtual Earth
    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 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
  3. 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
  4. SQL Server 2008 with Spatial Now Available for Download
    By VE Team in forum Bing Maps Blogs & Tweets
    Replies: 1
    Last Post: 11-20-2007, 08:34 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