locked
Choosing Grid Level Density on Spatial Indexes RRS feed

  • Question

  • Hi, I don't really know where to begin. How good is the default setting (Medium at each level).


    My data set includes polygons of all countries in one table, polygons of provinces, major cities, custom areas in another table & and then points of about 10,000 locations around the world in another table.

    Is that a bad strategy having my Geography types in different tables?

    I would like to be able to perform ST functions across one table to another.

    Does anyone have a good blog post about how to go about choosing or tuning grid density?


    Jonathon
    Saturday, January 22, 2011 1:14 PM

Answers

  • G'Day Jon :)

    excellent question, but one I'm unable to help with. I have the same problem - i've been using the Sql Geospatial stuff since Sql 2008 Beta's and find this the hardest concept to understand -> how to best index my GEOGRAPHIES and (more importantly) how to use stats and query plans to see what's going on behind the scenes, to figure out what settings are best.

    From what I understand, there's not one best trick. It's all trial and error -> as in, make some settings, check the stats + queryplan for certain numbers (which is where I fail at / no idea what to look for) and re-tune until you get some good outcomes.

    As to schema's? I've got some similar data to you -> countires, states, blah blah blah .. all up .. around a few million GEOGRAPHY polygons. Some are really high STNumPoints() eg. states of america ... down to simple linestrings.

    I ended up making two tables based on the row numbers, how often the data will change AND what I wish to download from live to dev. So, for example, the states and countries aren't going to change. So they are in a table.. and i've got some other admin layers in that same table. I've then put that table on the PRIMARY filegroup. (if i was really hardcore, i might put that into it's OWN filegroup and make that filegroup read only ... *grin* ). Next i've got my massive table of *possibly* changeable data. Definately this table will increase in size as new polys can be added by users, etc. Now, this table is on it's own filegroup. And now I can ignore this table in a backup if required .. to reduce the backup file size when I need to grab dev copies of the live DB, for debugging or development purposes or even reporting purposes.

    So i'm not sure if that helps, but that's what I'm doing. Oh, i'm also using google maps (insert you fav map here.... :P ) so i've Reduced my polys to various levels to speed up stuff also <-- hint hint :) -- and save that .. so my table size is pretty big .. and i've still got to source more polys :(

    Now, if only countries like Australia and the UK can have free data :P Ok, Aus sorta does but it only goes down a few admin levels ... anyways.... i'm rambling now ...

    GL!

    EDIT: OH! Also agree with you Jon - Gareth Emery's Podcast is pretty hawt :) Done plenty of SqlServer Spatial + EF + MVC coding to his choons :)

    --

    @Tanoshimi - dude, we need a new book for Denali .. and in it have a massive chapter on perf tuning spatial queries and indexes and stuff :) and i'll love u even more!


    -Pure Krome-
    Sunday, January 23, 2011 11:29 PM

All replies

  • G'Day Jon :)

    excellent question, but one I'm unable to help with. I have the same problem - i've been using the Sql Geospatial stuff since Sql 2008 Beta's and find this the hardest concept to understand -> how to best index my GEOGRAPHIES and (more importantly) how to use stats and query plans to see what's going on behind the scenes, to figure out what settings are best.

    From what I understand, there's not one best trick. It's all trial and error -> as in, make some settings, check the stats + queryplan for certain numbers (which is where I fail at / no idea what to look for) and re-tune until you get some good outcomes.

    As to schema's? I've got some similar data to you -> countires, states, blah blah blah .. all up .. around a few million GEOGRAPHY polygons. Some are really high STNumPoints() eg. states of america ... down to simple linestrings.

    I ended up making two tables based on the row numbers, how often the data will change AND what I wish to download from live to dev. So, for example, the states and countries aren't going to change. So they are in a table.. and i've got some other admin layers in that same table. I've then put that table on the PRIMARY filegroup. (if i was really hardcore, i might put that into it's OWN filegroup and make that filegroup read only ... *grin* ). Next i've got my massive table of *possibly* changeable data. Definately this table will increase in size as new polys can be added by users, etc. Now, this table is on it's own filegroup. And now I can ignore this table in a backup if required .. to reduce the backup file size when I need to grab dev copies of the live DB, for debugging or development purposes or even reporting purposes.

    So i'm not sure if that helps, but that's what I'm doing. Oh, i'm also using google maps (insert you fav map here.... :P ) so i've Reduced my polys to various levels to speed up stuff also <-- hint hint :) -- and save that .. so my table size is pretty big .. and i've still got to source more polys :(

    Now, if only countries like Australia and the UK can have free data :P Ok, Aus sorta does but it only goes down a few admin levels ... anyways.... i'm rambling now ...

    GL!

    EDIT: OH! Also agree with you Jon - Gareth Emery's Podcast is pretty hawt :) Done plenty of SqlServer Spatial + EF + MVC coding to his choons :)

    --

    @Tanoshimi - dude, we need a new book for Denali .. and in it have a massive chapter on perf tuning spatial queries and indexes and stuff :) and i'll love u even more!


    -Pure Krome-
    Sunday, January 23, 2011 11:29 PM
  • You get bumped up just for mentioning Gareth Emery, EF, SQL & MVC all in the same sentence!

    Krome, where are you based? Drop me a line by email.

     

     


    Jonathon - www.kresner.com
    Monday, January 31, 2011 12:32 PM