locked
Geospatial index on Partitioned table RRS feed

  • General discussion

  • I have a large table that includes a geospatial field called "Position".  I want to partition this table since it grows by 2 million rows per day.  I have 1 clustered primary index (Id) and 2 non clustered indexes, all aligned properly with the partition scheme so I can quickly "switch" out the old data.  So far the partitioning tests have worked great and I'm getting the results I expected.

    I then defined the geospatial index and it doesn't seem to support partitioning.  Without an aligned index, I won't be able to "switch" out old data.  The online reference says...

    ON filegroup_name

    which implies that it is supported but I get a syntax error...

    create SPATIAL INDEX SPATIAL_Reports on Reports
    (
      Position
    )
    using GEOMETRY_GRID with
    (
      BOUNDING_BOX = (-180, -90, 180, 90),
      GRIDS = (LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM, LEVEL_3 = MEDIUM, LEVEL_4 = MEDIUM),
      CELLS_PER_OBJECT = 16,
      PAD_INDEX = OFF,
      SORT_IN_TEMPDB = OFF,
      DROP_EXISTING = OFF,
      ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS = ON
    )
    on psReports ([ReportDate]);

    ... near [ReportDate]. 

    This is the same syntax that I used to define the aligned non clustered index using the partition scheme psReports for the other 2 indexes.  Is my syntax really wrong or are partitioned geospatial indexes not supported in SQL Server 2008?

    Tuesday, October 29, 2013 2:54 PM

All replies

  • you can create a partitioned spatial index using the GUI (right click on index folder, new spatial index etc) but i dont think it's partitioned.

    if you run profiler to capture the tsql passed in by the gui it doesn't show any reference to a partition scheme

    and then if you look @ sys.partitions there does seem to be a difference between creating the index against the primary FG vs partition scheme.

    The only difference i saw in the captured sql was the unpartititioned index had an ON ([PRIMARY]) at the end, whereas the partitioned version didn't make any reference, so your index would be

    create SPATIAL INDEX SPATIAL_Reports on Reports
    (
      Position
    )
    using GEOMETRY_GRID with
    (
      BOUNDING_BOX = (-180, -90, 180, 90),
      GRIDS = (LEVEL_1 = MEDIUM, LEVEL_2 = MEDIUM, LEVEL_3 = MEDIUM, LEVEL_4 = MEDIUM),
      CELLS_PER_OBJECT = 16,
      PAD_INDEX = OFF,
      SORT_IN_TEMPDB = OFF,
      DROP_EXISTING = OFF,
      ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS = ON
    )


    Jakub @ Adelaide, Australia

    Thursday, October 31, 2013 6:28 AM
  •  
    Under Spatial Indexes and Partitioning on that page: �??By default, if a spatial index is created on a partitioned table, the index is partitioned according to the partition scheme of the table.�?�
     
    Hope this helps, Bob
    Friday, November 1, 2013 12:09 AM
  • Ya, I read that as well, which is saying that I don't have to specify the partition scheme in the "ON <filegroup>" part of the statement.   Using "ON [Primary]" works but I get the uneasy feeling that the index really isn't partitioned.

    I answered another thread that talks about my same situation and I learned a few things.  I ran some experiments and in the end, I've concluded that I can partition my table but I can't take advantage of the "switch" command to delete old data.  There is some indication that this may be fixed in SQL Server 2014.  The fact that I can't switch out a partition when that geospatial index exists means (to me) that the geospatial index is not aligned.

    DaveH

    Friday, November 1, 2013 6:03 PM