locked
Spatial Index for geometry columns RRS feed

  • Question

  • Hi 

    SQL server :2016R2 STD

    I am looking to  create Spatial Index. There are  things to clarify before create Spatial Index . I have few questions to follow on best practise 
    1. how to determine  Spatial Index under what category  
    GEOMETRY_GRID
    GEOMETRY_AUTO_GRID
    GEOGRAPHY_GRID
    GEOGRAPHY_AUTO_GRID

    What information do I need to select this?

    2. how to determine BOUNDING_BOX values ?What information do I need to reside this parameters?

    3. how to determine GRIDS, CELLS_PER_OBJECT  values ?What information do I need to decide this parameters?

    ++++++++++++++

    CREATE TABLE [dbo].[FIREPRONELD](
    [OBJECTID] [int] NOT NULL,
    [StartDate] [datetime2](7) NULL,
    [EndDate] [datetime2](7) NULL,
    [LastUpdate] [datetime2](7) NULL,
    [Category] [int] NULL,
    [Shape] [geometry] NULL,
    [GDBGEO_DT] [varbinary](max) NULL,
     CONSTRAINT [R_pk] PRIMARY KEY CLUSTERED 
    (
    [OBJECTID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    CREATE SPATIAL INDEX [S19_idx] ON [dbo].[FIREPRONELD]
    (
    [Shape]
    )USING  GEOMETRY_GRID 
    WITH (BOUNDING_BOX =(aa,bb,cc, dd), 
    GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM), 
    CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

    Thank you in advance 


    • Edited by ashwan Thursday, June 18, 2020 7:47 AM
    • Moved by Naomi N Thursday, June 18, 2020 3:00 PM Better answer can be here
    Thursday, June 18, 2020 7:10 AM

Answers

  • hi

    I think you are correct .the syntax is bellow

    CREATE SPATIAL INDEX SIndx_SpatialTable_geometry_col2  
       ON SpatialTable(geometry_col)  
       USING GEOMETRY_GRID  
       WITH (  
        BOUNDING_BOX = ( xmin=0, ymin=0, xmax=500, ymax=200 ),  
        GRIDS = (LOW, LOW, MEDIUM, HIGH),  
        CELLS_PER_OBJECT = 64,  
        PAD_INDEX  = ON );  

    your  BOUNDING_BOX looks correct .That is map extend pair of lat long values those the two points of a diagonal of rectangle  imagine it
    • Marked as answer by ashwan Sunday, June 28, 2020 10:51 PM
    Thursday, June 25, 2020 4:05 PM

All replies

  • Hi ashwan,

    >> SQL server :2016R2 STD

    This is no SQL server 2016 R2. The latest version is SQL server 2016 SP2 CU13, suggest you apply CU13 for SQL server 2016 SP2.

    >> 1. how to determine  Spatial Index under what category  

    GEOMETRY_GRID
    Specifies the geometry grid tessellation scheme that you are using. GEOMETRY_GRID can be specified only on a column of the geometry data type. GEOMETRY_GRID allows for manual adjusting of the tessellation scheme.

    GEOMETRY_AUTO_GRID
    Applies to: SQL Server (SQL Server 2012 (11.x) and later) and Azure SQL Database
    Can be specified only on a column of the geometry data type. This is the default for this data type and does not need to be specified.

    GEOGRAPHY_GRID
    Specifies the geography grid tessellation scheme. GEOGRAPHY_GRID can be specified only on a column of the geography data type.

    GEOGRAPHY_AUTO_GRID
    Applies to: SQL Server (SQL Server 2012 (11.x) and later) and Azure SQL Database
    Can be specified only on a column of the geography data type. This is the default for this data type and does not need to be specified.

    Please refer to CREATE SPATIAL INDEX USING options.

    >> 2.how to determine BOUNDING_BOX values ?What information do I need to reside this parameters?

    BOUNDING_BOX
    Specifies a numeric four-tuple that defines the four coordinates of the bounding box: the x-min and y-min coordinates of the lower-left corner, and the x-max and y-max coordinates of the upper-right corner.

    Please refer to CREATE SPATIAL INDEX WITH options and The Bounding Box to get more information.


    >>3. how to determine GRIDS, CELLS_PER_OBJECT  values ?What information do I need to decide this parameters?

    GRIDS
    Defines the density of the grid at each level of a tessellation scheme. When GEOMETRY_AUTO_GRID and GEOGRAPHY_AUTO_GRID are selected, this option is disabled. Please refer to Decomposing Indexed Space into a Grid Hierarchy.


    The CELLS_PER_OBJECT value is used by the cells-per-object tessellation rule. Please refer to Tessellation Rules.

    Please also check if this blog could help you.

    Best regards,
    Cathy 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com





    Friday, June 19, 2020 6:53 AM
  • Hi Cathy Thank you for that. 

    My question is, I have been asked to create table and indexes which has Spatial Index for geometry columns. So to find out the way I need, first I need to know what is the business expectation. So I am not sure what questions to be asked  from the business  create those indexes and the table?

    Thank you

     



    • Edited by ashwan Friday, June 19, 2020 8:26 PM
    Friday, June 19, 2020 8:26 PM
  • hi

    BOUNDING_BOX inputs are the pair of lat long value that is map extents value

    Thanks and Regrds
    Laxmidhar sahoo

    Saturday, June 20, 2020 5:12 PM
  • Hi Laxmidhar ,Cathy

    through application Esri ArcCatalog program index has been created . I am not sure following attributes (BOUNDING_BOX)  correct performance  and configuration level . How this figures will decide when we do manual creation.  When Special SQL is query in the how deep index will dive to get the data. 

    CREATE SPATIAL INDEX [S13_idx] ON [[dbo].[FIREPRONELD]
    (
    [Shape]
    )USING  GEOMETRY_AUTO_GRID 
    WITH (BOUNDING_BOX =(149.631554964, -33.4024858389, 152.9763892089, -30.5898518857), 
    CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

    Is the possible to advise 

    many thanks


    • Edited by ashwan Tuesday, June 23, 2020 2:17 AM
    Tuesday, June 23, 2020 2:14 AM
  • hi

    I think you are correct .the syntax is bellow

    CREATE SPATIAL INDEX SIndx_SpatialTable_geometry_col2  
       ON SpatialTable(geometry_col)  
       USING GEOMETRY_GRID  
       WITH (  
        BOUNDING_BOX = ( xmin=0, ymin=0, xmax=500, ymax=200 ),  
        GRIDS = (LOW, LOW, MEDIUM, HIGH),  
        CELLS_PER_OBJECT = 64,  
        PAD_INDEX  = ON );  

    your  BOUNDING_BOX looks correct .That is map extend pair of lat long values those the two points of a diagonal of rectangle  imagine it
    • Marked as answer by ashwan Sunday, June 28, 2020 10:51 PM
    Thursday, June 25, 2020 4:05 PM