У меня есть некоторая забава, пытающаяся выбрать достойный 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
Это запрашивает область, которая пересекается с только двумя из полигонов в таблице. Какую комбинацию пространственных индексных настроек я выбрал, тот Фильтр () всегда возвращает приблизительно 60 000 строк.
Замена Фильтра () с STIntersects (), конечно, возвращает просто эти два полигона, которые я хочу, но конечно берет намного дольше (Фильтр () составляет 6 секунд, STIntersects () составляет 12 секунд).
Кто-либо может дать мне какие-либо подсказки, существует ли пространственная индексная установка, которая, вероятно, изменит к лучшему 60 000 строк, или разве мой набор данных является просто не хорошим соответствием для пространственной индексации 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 ограничительных рамок в 4x4 сетка. Конечный результат состоит в том, что у меня есть новая таблица с ~107 000 строками (который подтверждает, что у меня на самом деле не было многих огромных полигонов).
Я добавил пространственный индекс с 1 024 ячейками на объект и низко, низко, низко, низко для ячеек на уровень.
Однако очень странно эта новая таблица с полигонами разделения все еще выполняет то же как старое. Выполнение.Filter, упомянутого выше все еще, возвращает ~60 000 строк. Я действительно не понимаю это вообще, ясно я не понимаю, как пространственный индекс на самом деле работает.
Как это ни парадоксально, в то время как.Filter () все еще возвращает ~60 000 строк, он улучшил производительность..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 Bounding_Box_ymin-180 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 1129 Average_Number_Of_Index_Rows_Per_Base_Row 1 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_Object Cells_In_Level2_In_Index 2935 Total_Number_Of_ObjectCells_In_Level3_In_Index 32420 Total_Number_Of_ObjectCells_In_Level4_In_In dex 281978 Total_Number_Of_Interior_ObjectCells_In_Level2_In_Index 1 Total_Number_Of_Interior_ObjectCells_In_Level3_In_Index 4 9 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 2 9680 Total_Number_Of_Intersecting_ObjectCells_In_Level4_In_Index 251517 Total_Number_Of_Border_ObjectCells_In_Level0_For_Query Sample 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_N umber_Of_Border_ObjectCells_In_Level4_In_Index 26225 Interior_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage 0.004852925 In tersecting_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage 0.288147586 Border_To_Total_Cells_Normalized_To_Leaf_Grid_Percent age 99.70699949 Average_Cells_Per_Object_Normalized_To_Leaf_Grid 405.7282349 Average_Objects_PerLeaf_GridCell 0.002464704 Numb er_Of_SRIDs_Found 1 Width_Of_Cell_In_Level1 2.8125 Width_Of_Cell_In_Level2 0.043945313 Width_Of_Cell_In_Level3 0.000686646 Wid th_Of_Cell_In_Level4 1.07E-05 Height_Of_Cell_In_Level1 5.625 Height_Of_Cell_In_Level2 0.087890625 Height_Of_Cell_In_Level3 0.0 01373291 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_Lev el3 0.247192383 Area_Of_Cell_In_Level4 0.003862381 CellArea_To_BoundingBoxArea_Percentage_In_Level1 1.5625 CellArea_To_Boundin gBoxArea_Percentage_In_Level2 0.024414063 CellArea_To_BoundingBoxArea_Percentage_In_Level3 0.00038147 CellArea_To_BoundingBoxA rea_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_Efficienc y 0.003281055
"Base_Table_Rows 215138" не имеет большого смысла мне, существует 107 000 строк в таблице, не 215,000
При рендеринге набор данных похож на это:
(источник: norman.cx)
Дальнейшее исследование:
Я продолжаю озадачиваться низкой производительностью основного фильтра с этими данными. Таким образом, я сделал тест для наблюдения точно, как мои данные распадаются. С моими исходными неразделенными функциями я добавил столбец "ячеек" к таблице. Я затем выполнил 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 ячейкой в 4x4 сетка. Таким образом, как же, вполне буквально, основное устройство может отфильтровать возвратить 60 000 функций запроса, смотрящего на маленький прямоугольник 200 миль шириной?
В этой точке похоже, что я мог записать свою собственную схему индексации, которая будет работать лучше это, как SQL Server работает для этих функций.
В вашем индексном запросе вы используете:
CREATE SPATIAL INDEX [contasplit_sidx] ON [dbo].[ContASplit]
(
[geom]
)USING GEOMETRY_GRID
WITH (
BOUNDING_BOX =(-90, -180, 90, 180),
...
BOUNDING_BOX, следовательно, сопоставляется с:
xmin = -90
ymin = -180
xmax = 90
ymax = 180
Итак, чтобы создать BOUNDING_BOX для мира, вы должны использовать:
CREATE SPATIAL INDEX [contasplit_sidx] ON [dbo].[ContASplit]
(
[geom]
)USING GEOMETRY_GRID
WITH (
BOUNDING_BOX =(-180, -90, 180, 90),
...
Это должно создать индекс, который соответствует вашим данным и означает, что все ваши особенности будут охвачены индексом.
Если запрос предназначен для отображения данных, то можно разделить большие полигоны с помощью сетки. Тогда их можно будет очень быстро получить с помощью индекса. Можно удалить контуры, чтобы объекты выглядели непрерывными.
В большинстве коммерческих ГИС-пакетов есть инструменты для разделения одного набора данных полигонов на другой. Ищите инструменты, которые делают пересечения.
Если вы используете OpenSource, посмотрите на QGIS и http://www.ftools.ca, которые "выполняют операции геообработки, включая пересечения, дифференцирование, объединение, растворение и вырезку".
Взгляните на: http://postgis.refractions.net/docs/ch04.html#id2790790 о том, почему крупные элементы плохи.
Подробнее о предложении Filter здесь - http://blogs.msdn.com/b/isaac/archive/2010/03/04/filter-one-odd-duck.aspx
Еще нужно проверить, действительно ли пространственный индекс используется в плане запроса. Возможно, вам придется заставить запрос использовать индекс с помощью условия WITH:
http://blogs.msdn.com/b/isaac/archive/2008/08/29/is-my-spatial-index-being-used.aspx
Подробнее об индексах ниже:
http://blogs.msdn.com/b/isaac/archive/2009/05/28/sql-server-spatial-indexing.aspx
Также попробуйте выполнить sp_help_spatial_geometry_index для ваших данных, чтобы узнать, какие настройки использовать для пространственного индекса
http://msdn.microsoft.com/en-us/library/cc627426.aspx
Запуск этого SP с некоторой тестовой геометрией дает всевозможную статистику, чтобы попытаться адаптировать индекс к вашим данным. Полный список свойств находится на http://msdn.microsoft.com/en-us/library/cc627425.aspx
Они включают такие значения, как:
Из результатов sp_help_spatial_geometry_index похоже, что у вас проблемы с самой геометрией, а не с пространственным индексом.
Счетчик Base_Table_Rows выглядит как ошибка - http://connect.microsoft.com/SQLServer/feedback/details/475838/number-of-rows-in-base-table-incorrect-in-sp-help-spatial-geography-index-xml. Возможно, стоит пересоздать таблицу / базу данных и попробовать индекс с нуля.
Total_Number_Of_ObjectCells_In_Level0_In_Index 60956 - много функций для возврата на уровне 0. Вероятно, они находятся за пределами пространственного индекса или являются нулевыми. Затем выполняется Intersect (Number_Of_Times_Secondary_Filter_Is_Called 60956) для всех этих характеристик, что объясняет, почему он работает медленно. Хотя в документации утверждается, что производительность не падает для нулевых функций, я полагаю, что все равно приходится искать записи, даже если не выполняется пересечение.
NULL и пустые экземпляры учитываются на уровне 0, но не влияют на производительность. На уровне 0 будет столько же ячеек, сколько NULL и пустых экземпляров в базовой таблице.
Эффективность Primary_Filter_Efficiency 0.003281055, я полагаю, указывает на 0.03% эффективности!
Несколько вещей, которые нужно попробовать:
Оператор MakeValid:
UPDATE MyTable SET GeomFieldName = GeomFieldName.MakeValid()
Сброс / двойная проверка SRID:
UPDATE MyTable SET GeomFieldName.STSrid = 4326
Добавьте некоторые поля, чтобы показать границы ваших особенностей. Это может выявить проблемы / NULL-геометрии.
ALTER TABLE MyTable ADD MinX AS (CONVERT(int,GeomFieldName.STEnvelope().STPointN((1)).STX,0)) PERSISTED ALTER TABLE MyTable ADD MinY AS (CONVERT(int,GeomFieldName.STEnvelope().STPointN((1)).STY,0)) PERSISTED ALTER TABLE MyTable ADD MaxX AS (CONVERT(int,GeomFieldName.STEnvelope().STPointN((3)).STX,0)) PERSISTED ALTER TABLE MyTable ADD MaxY AS (CONVERT(int,GeomFieldName.STEnvelope().STPointN((3)).STY,0)) PERSISTED