Answered by:
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](
CREATE SPATIAL INDEX [S19_idx] ON [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
(
[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]
GOThank you in advance
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
- Edited by Cathy JiMicrosoft contingent staff Friday, June 19, 2020 7:04 AM
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 sahooSaturday, 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]
GOIs 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