Я получаю удовольствие, пытаясь выбрать подходящую настройку пространственного индекса SQL Server 2008 для набора данных, с которым я имею дело.

Набор данных - это полигоны, представляющие контуры по всему земному шару. В таблице 106 000 строк, многоугольники хранятся в геометрическом поле.

У меня проблема в том, что многие полигоны покрывают большую часть земного шара. По-видимому, это очень затрудняет получение пространственного индекса, который устранит множество строк в основном фильтре. Например, посмотрите на следующий запрос:

SELECT "ID","CODE","geom".STAsBinary() as "geom" FROM "dbo"."ContA"
WHERE "geom".Filter(
  geometry::STGeomFromText('POLYGON ((-142.03193662573682 59.53396984952896,
    -142.03193662573682 59.88928136451884,
    -141.32743833481925 59.88928136451884,
    -141.32743833481925 59.53396984952896,
    -142.03193662573682 59.53396984952896))', 4326)
) = 1

Это запрос области, которая пересекается только с двумя полигонами в таблице. Независимо от того, какую комбинацию настроек пространственного индекса я выбрал, функция Filter () всегда возвращает около 60 000 строк.

Замена Filter () на STIntersects (), конечно, возвращает только те два полигона, которые мне нужны, но, конечно, это занимает гораздо больше времени (Filter () - 6 секунд, STIntersects () - 12 секунд).

Может кто-нибудь подсказать, есть ли настройка пространственного индекса, которая может улучшиться на 60000 строк, или мой набор данных просто не очень подходит для пространственной индексации SQL Server?

Больше информации:

Как и предполагалось, я разделил полигоны, используя сетку 4x4 по всему миру. Я не мог найти способ сделать это с QGIS, поэтому я написал свой собственный запрос, чтобы сделать это. Сначала я определил 16 ограничивающих рамок, первая выглядела так:

declare  @box1 geometry = geometry::STGeomFromText('POLYGON ((
-180 90,
-90 90,
-90 45,
-180 45,
-180 90))', 4326)

Затем я использовал каждую ограничивающую рамку, чтобы выбрать и обрезать полигоны, которые пересекали эту рамку:

insert ContASplit
select CODE, geom.STIntersection(@box1), CODE_DESC from ContA
where geom.STIntersects(@box1) = 1

Я, очевидно, сделал это для всех 16 ограничительных рамок в сетке 4х4. Конечным результатом является то, что у меня есть новая таблица с ~ 107 000 строк (что подтверждает, что у меня на самом деле не было много огромных полигонов).

Я добавил пространственный индекс с 1024 ячейками на объект и низко, низко, низко, низко для ячеек на уровень.

Однако, как ни странно, эта новая таблица с разделенными полигонами по-прежнему работает так же, как и старая. Делать вышеупомянутый фильтр.еще возвращает ~ 60000 строк. Я действительно не понимаю этого вообще, ясно, что я не понимаю, как на самом деле работает пространственный индекс.

Как это ни парадоксально, хотя .Filter () по-прежнему возвращает ~ 60000 строк, но производительность повысилась. .Filter () теперь занимает около 2 секунд, а не 6, а .STIntersects () теперь занимает 6 секунд, а не 12.

В соответствии с запросом приведен пример SQL для индекса:

CREATE SPATIAL INDEX [contasplit_sidx] ON [dbo].[ContASplit] 
(
    [geom]
)USING  GEOMETRY_GRID 
WITH (
BOUNDING_BOX =(-90, -180, 90, 180),
GRIDS =(LEVEL_1 = LOW,LEVEL_2 = LOW,LEVEL_3 = LOW,LEVEL_4 = LOW), 
CELLS_PER_OBJECT = 1024,
PAD_INDEX  = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF,
ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Хотя помните, я пробовал целый ряд различных настроек для сеток и ячеек на объект, каждый раз с одинаковыми результатами.

Вот результаты запуска sp_help_spatial_geometry_index, это в моем наборе разделенных данных, где ни один полигон не занимает более 1/16 земного шара:

Base_Table_Rows 215138 Bounding_Box_xmin -90 -180 Bounding_Box_ymin Bounding_Box_xmax 90 Bounding_Box_ymax 180 Grid_Size_Level_1 64 Grid_Size_Level_2 64 Grid_Size_Level_3 64 Grid_Size_Level_4 64 Cells_Per_Object 16 Total_Primary_Index_Rows 378650 Total_Primary_Index_Pages Average_Number_Of_Index_Rows_Per_Base_Row 1 1 129 Total_Number_Of_ObjectCells_In_Level0_For_QuerySample 1 Total_Number_Of_ObjectCells_In_Level0_In_Index 60956 Total_Number_Of_ObjectCells_In_Level1_In_Index 361 Total_Number_Of_ObjectCells_In_Level2_In_Index 2935 Total_Number_Of_ObjectCells_In_Level3_In_Index 32420 Total_Number_Of_ObjectCells_In_Level4_In_Index Total_Number_Of_Interior_ObjectCells_In_Level2_In_Index 1 281 978 Total_Number_Of_Interior_ObjectCells_In_Level3_In_Index 49 Total_Number_Of_Interior_ObjectCells_In_Level4_In_Index 4236 Total_Number_Of_Intersecting_ObjectCells_In_Level1_In_Index 29 Total_Number_Of_Intersecting_ObjectCells_In_Level2_In_Index 1294 Total_Number_Of_Intersecting_ObjectCells_In_Level3_In _Index 29680 Total_Number_Of_Intersecting_ObjectCells_In_Level4_In_Index 251517 Total_Number_Of_Border_ObjectCells_In_Level0_For_QuerySample 1 Total_Number_Of_Border_ObjectCells_In_Level0_In_Index 60956 Total_Number_Of_Border_ObjectCells_In_Level1_In_Index 332 Total_Number_Of_Border_ObjectCells_In_Level2_In_Index 1640 Total_Number_Of_Border_ObjectCells_In_Level3_In_Index 2691 Total_Number_Of_Border_ObjectCells_In_Level4_In_Index 26225 Interior_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage 0,004852925 0,288147586 Intersecting_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage Border_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage +99,70699949 Average_Cells_Per_Object_Normalized_To_Leaf_Grid +405,7282349 Average_Objects_PerLeaf_GridCell 0,002464704 Number_Of_SRIDs_Found 1 Width_Of_Cell_In_Level1 2,8125 Width_Of_Cell_In_Level2 0,043945313 0,000686646 Width_Of_Cell_In_Level3 Width_Of_Cell_In_Level4 1.07E-05 Height_Of_Cell_In_Level1 5,625 Height_Of_Cell_In_Level2 0,087890625 Height_Of_Ce ll_In_Level3 0,001373291 Height_Of_Cell_In_Level4 2.15E-05 Area_Of_Cell_In_Level1 1012,5 Area_Of_Cell_In_Level2 15,8203125 Area_Of_Cell_In_Level3 0,247192383 0,003862381 Area_Of_Cell_In_Level4 CellArea_To_BoundingBoxArea_Percentage_In_Level1 1,5625 CellArea_To_BoundingBoxArea_Percentage_In_Level2 0,024414063 0,00038147 CellArea_To_BoundingBoxArea_Percentage_In_Level3 CellArea_To_BoundingBoxArea_Percentage_In_Level4 5.96E-06 Number_Of_Rows_Selected_By_Primary_Filter 60956 Number_Of_Rows_Selected_By_Internal_Filter 0 Number_Of_Times_Secondary_Filter_Is_Called 60956 Number_Of_Rows_Output 2 Percentage_Of_Rows_NotSelected_By_Primary_Filter 71.66655821 Percentage_Of_Primary_Filter_Rows_Selected_By_Internal_Filter 0 Internal_Filter_Efficiency 0 Primary_Filter_Efficiency 0,003281055

«Base_Table_Rows 215138» не имеет для меня особого смысла, в таблице 107 000 строк, а не 215 000

При рендеринге набор данных выглядит так:альтернативный текст http://norman.cx/photos/links/wms.png

Дальнейшие исследования:

Я по-прежнему озадачен низкой производительностью первичного фильтра с этими данными. Итак, я сделал тест, чтобы увидеть, как именно мои данные распадаются. С моими оригинальными нерасщепленными функциями я добавил в таблицу столбец «ячейки». Затем я выполнил 16 запросов, чтобы подсчитать, сколько ячеек в сетке 4x4 охватывает объект. Поэтому я запустил такой запрос для каждой ячейки:

declare  @box1 geometry = geometry::STGeomFromText('POLYGON ((
-180 90,
-90 90,
-90 45,
-180 45,
-180 90))', 4326)
update ContA set cells = cells + 1 where
geom.STIntersects(@box1) = 1

Если я тогда посмотрю на столбец «Ячейки» в таблице, то во всем моем наборе данных будет только 672 объекта, которые пересекаются с более чем 1 ячейкой в ​​сетке 4х4. Так как же, на самом деле, буквально, основной фильтр может возвращать 60 000 объектов для запроса, смотрящего на маленький прямоугольник шириной 200 миль?

На данный момент, похоже, я мог бы написать свою собственную схему индексации, которая работала бы лучше, чем работа SQL Server для этих функций.