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 colum开发者_如何学运维n and want to index it. However, there are many options and MSDN doesn't indicate best practices. I have the following questions:
- 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?
- Since I am recording only points, I assume CELLS_PER_OBJECT = 1 is correct?
- What is the min to max range for x & y of GEOGRAPHY_GRID? See #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?
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.
I found the answer to 3, 4: SRID 4326 is (-180.0000, -90.0000, 180.0000, 90.0000)
精彩评论