Estou me divertindo tentando escolher uma configuração decente de índice espacial do SQL Server 2008 para um conjunto de dados com o qual estou lidando.

O conjunto de dados é polígonos, representando contornos em todo o mundo. Existem 106.000 linhas na tabela, os polígonos são armazenados em um campo de geometria.

O problema que tenho é que muitos dos polígonos cobrem uma grande parte do globo. Isso parece dificultar a obtenção de um índice espacial que elimine muitas linhas no filtro primário. Por exemplo, observe a seguinte consulta:

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

Isso está consultando uma área que se cruza com apenas dois dos polígonos na tabela. Não importa qual combinação de configurações de índice espacial eu escolhi, esse Filter () sempre retorna cerca de 60.000 linhas.

Substituir Filter () por STIntersects () retorna apenas os dois polígonos que eu quero, mas é claro que leva muito mais tempo (Filter () é de 6 segundos, STIntersects () é de 12 segundos).

Alguém pode me dar alguma dica sobre se existe uma configuração de índice espacial com probabilidade de melhorar em 60.000 linhas ou se meu conjunto de dados não é uma boa correspondência para a indexação espacial do SQL Server?

Mais informações:

Como sugerido, eu dividi os polígonos, usando uma grade 4x4 em todo o mundo. Não consegui ver uma maneira de fazer isso com o QGIS, então escrevi minha própria consulta para fazê-lo. Primeiro eu defini 16 caixas delimitadoras, a primeira ficou assim:

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

Depois, usei cada caixa delimitadora para selecionar e truncar os polígonos que cruzavam essa caixa:

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

Obviamente, eu fiz isso para todas as 16 caixas delimitadoras da grade 4x4. O resultado final é que eu tenho uma nova tabela com ~ 107.000 linhas (o que confirma que eu não tinha muitos polígonos enormes).

Adicionei um índice espacial com 1024 células por objeto e baixo, baixo, baixo, baixo para as células por nível.

No entanto, muito estranhamente, essa nova tabela com os polígonos divididos ainda tem o mesmo desempenho da tabela anterior. Executando o .Filter listado acimaainda retorna ~ 60.000 linhas. Realmente não entendo isso, claramente não entendo como o índice espacial realmente funciona.

Paradoxalmente, enquanto .Filter () ainda retorna ~ 60.000 linhas, ele melhorou o desempenho. O .Filter () agora leva cerca de 2 segundos em vez de 6 e o .STIntersects () agora leva 6 segundos em vez de 12.

Conforme solicitado, aqui está um exemplo do SQL para o índice:

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]

Embora lembre-se, tentei uma variedade de configurações diferentes para as grades e células por objeto, com os mesmos resultados a cada vez.

Aqui estão os resultados da execução de sp_help_spatial_geometry_index, este é no meu conjunto de dados dividido em que nenhum polígono ocupa mais de 1/16 do mundo:

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_ObjectCells_In_Level2_In_Index 2935 Total_Number_Of_ObjectCells_In_Level3_In_Index 32420 281978 Total_Number_Of_ObjectCells_In_Level4_In_Index Total_Number_Of_Interior_ObjectCells_In_Level2_In_Index 1 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 251517 Total_Number_Of_Intersecting_ObjectCells_In_Level4_In_Index 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 405,7282349 Average_Cells_Per_Object_Normalized_To_Leaf_Grid 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 60956 Number_Of_Rows_Selected_By_Primary_Filter 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" não faz muito sentido para mim, existem 107.000 linhas na tabela, não 215.000

Quando renderizado, o conjunto de dados fica assim:texto alternativo http://norman.cx/photos/links/wms.png

Mais pesquisa:

Continuo intrigado com o fraco desempenho do filtro primário com esses dados. Então fiz um teste para ver exatamente como meus dados se dividem. Com meus recursos não divididos originais, adicionei uma coluna "células" à tabela. Em seguida, executei 16 consultas para contar quantas células em uma grade 4x4 o recurso se estendeu. Então, eu executei uma consulta como esta para cada célula:

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

Se eu olhar para a coluna "células" na tabela, existem apenas 672 recursos em todo o meu conjunto de dados que se cruzam com mais de uma célula na grade 4x4. Então, como na Terra, literalmente, o filtro primário pode retornar 60.000 recursos para uma consulta olhando para um pequeno retângulo de 200 milhas de largura?

Neste ponto, parece que eu poderia escrever meu próprio esquema de indexação que funcionaria melhor do que o desempenho do SQL Server para esses recursos.