Spatial Index for geometry columns

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 

Answer 1

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 [e-mail]

Answer 2

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

 

Answer 3

hi

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

Thanks and Regrds
Laxmidhar sahoo

Answer 4

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

Answer 5

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