CODEWITHSUNDEEP
sql-serverindexinggeospatialsqlgeography
IamIC
IamIC

Reputation: 18269

SQL Server GEOGRAPHY_GRID x & y range

I have a table that needs to record geographical points (long, lat) for the whole world. The input data is traditional longitudinal & latitudinal (-180, -90, 180, 90).

I created a geography column and want to index it. However, there are many options and MSDN doesn't indicate best practices. I have the following questions:

  1. I assume GRIDS = ( LEVEL_1 = HIGH, LEVEL_2 = HIGH, LEVEL_3 = HIGH, LEVEL_4 = HIGH) is best for grids. This would create the max possible resolution at latitude ≈ 611.5m. I have seen example with other options. What is best?
  2. Since I am recording only points, I assume CELLS_PER_OBJECT = 1 is correct?
  3. What is the min to max range for x & y of GEOGRAPHY_GRID? See #4.
  4. With reference to #3 above, would I need to convert the traditional longitudinal & latitudinal (-180, -90, 180, 90) data to whatever range GEOGRAPHY_GRID uses so as to properly use the grids?

Upvotes: 1

Views: 921

Answers (2)

IamIC
IamIC

Reputation: 18269

I found the answer to 3, 4: SRID 4326 is (-180.0000, -90.0000, 180.0000, 90.0000)

Upvotes: 0

Alastair Aitchison
Alastair Aitchison

Reputation: 3532

1.) and 2.) The important thing to bear in mind is that the same grid is used not only for tessellating the data in the column on which the index is created, but also for whatever the query parameter you're using to test that data against. Consider the following query:

SELECT * FROM Table WHERE GeomColumn.STIntersects(@MyPoly) = 1

Assuming that you've created a spatial index on GeomColumn, then the same grid will be applied to @MyPoly in order to perform a primary filter of the results. So, you don't just choose a grid setting based on what's in your table, but also the sort of query sample that you'll be running against that data. In practice, what is "best" is very subjective based on your data. I'd always recommend you start at MEDIUM, MEDIUM, MEDIUM, MEDIUM, and then try adjusting it from there to see if you get better performance based on empirical tests.

3.) and 4.) You don't set a bounding box for the geography datatype - all geography indexes are implicitly assumed to cover the entire globe. That's one of the reasons that geometry is generally a faster-performing datatype than geography, because the cells of a geometry index can provide higher resolution over a limited geographic area.

Upvotes: 2

Related Questions