微调地理空间指数 [英] Fine-tuning a geospatial index

问题描述

我刚刚将SQL Server 2008数据库从使用纬度/经度对转换为使用新的地理类型.我使用STDistance函数在距geo :: Point 30英里以内的企业上运行查询，如下所示:

I just converted my SQL Server 2008 database from using a lat/long pair to using the new geography type. I run queries on businesses that are within 30 miles of a geography::Point, using the STDistance function, like so:

WHERE this_.GeoLocation.STDistance(geography::Point(42.738963, -84.5522, 4326)) <= 48280.32

这是我在地理位置"列上的索引:

Here is the index that I have on the geography column:

CREATE SPATIAL INDEX IDX_Business_GeoLocation
  ON Business (GeoLocation)
  USING GEOGRAPHY_GRID
  WITH (
    GRIDS = (
      LEVEL_1 = LOW, 
      LEVEL_2 = LOW, 
      LEVEL_3 = LOW, 
      LEVEL_4 = LOW),
    CELLS_PER_OBJECT = 64
  )

我不太了解网格级别或每个对象的单元格的含义，但是我正在寻找的是适合我的方案的最佳设置，在这种情况下，我正在寻找距离某点30英里以内的企业(经/纬度).

I don't really understand what the grid levels or the cells per object mean, but what I'm looking for are the best settings for my scenario, where I'm searching for businesses that are within 30 miles of a point (lat/long).

有什么提示吗?

推荐答案

这是一个鲜为人知的秘密(或者至少是我发现之前的秘密！)

This is a little known secret (or at least it was to me before i discovered it!)

EXEC sp_help_spatial_geography_index
@tabname = '[TABLE_NAME]',
@indexname = '[SPATIAL_INDEX_NAME]',
@verboseoutput = 1,
@query_sample = 'POLYGON((xy,xy,xy,xy))'

填充明显的位，并将@verboseoutput保留为1.它使您了解主滤波器和内部滤波器的效率(当然，越高越好).有关空间索引的快速概述，包括尝试使用哪些网格和单元等同于此处.

Fill in the obvious bits, and keep @verboseoutput to 1. It gives you an idea of the efficency of the primary and internal filters (higher the better of course). For a quick overview of spatial indexing, including what grids and cells equate to try here.

此外，我发现这视频非常有趣.

Also, i found this video pretty interesting.

这篇关于微调地理空间指数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！