title: How to create a table that allows efficient queries to geospatial data by different criteria?
body: <h1>Description of the data:</h1>
  
  <p>I'm creating a simple geospatial database in SQL Server. It will contain regions (polygons) for all the countries in the world plus their administrative regions. The data will come from OpenStreetMap. For each polygon in the database I will only store a unique ID and a name. This database will be very rarely written to, if ever (I'll just load the data once and that's it).</p>
  
  <p>Even though I only get these general boundaries, there's still plenty of data - the full world is around <a href="https://mapzen.com/data/borders/" rel="nofollow">4GB as GeoJSON</a>. The database will probably be more efficient, yet with all the indexes and everything I still expect it to be overt 1GB in size.</p>
  
  <p>The polygons are organized by their "administrative levels". Level 0 contains all the countries; Level 1 contains largest administrative subdivisions, etc. This does however vary by country - very few countries use ALL levels, mostly they use some 3 to 4 levels, but which ones - depends on the country.</p>
  
  <p>I believe that a polygon in a "lower" level will always belong (fit completely inside) to a polygon in a "higher" level. However it might not be the immediately previous numeric level. For example, if a country uses levels 1, 2 and 5, then the polygons in level 5 will belong to polygons in level 2, which will belong to polygons in level 1. At the very least I expect every poygon in levels greater than 0 to fit inside a polygon in level 0 (countries). I haven't tested this, but if there are any conflicts (disputed territories, maybe?), then I'll figure out some way to resolve them in order to establish the parent-child relationship.</p>
  
  <h1>Description of the requirements:</h1>
  
  <p>I need to query this data in the following ways:</p>
  
  <ol>
  <li>By specifying a level and a bounding box, all the polygons that are in this administrative level and inside the specific bounding box should be returned (along with ID and name).</li>
  <li>By specifying a parent polygon ID, I want to fetch all direct child polygons, no matter to which level they belong (but only direct children, not grandchildren).</li>
  </ol>
  
  <h1>Where I'm stuck:</h1>
  
  <p>How do I create a data structure that can query this efficiently?</p>
  
  <p>Querying by bounding box can be efficiently done only with a geospatial index, but that then does not filter by administrative levels. I don't want to select all the shapes by the geospatial index and then do a full scan over them - this will fail miserably when I request the whole world in level 0.</p>
  
  <p>I could split each level in their own table, which would work nicely for the level+bbox criteria, but how then do I select all children of a parent, if they are potentially smeared out over several tables? I can't even create an indexed view because that does not support UNIONs.</p>
  
  <p>I could store everything in two copies - split-table for first query; single-table for second. But that would double the amount of data in the database, which is already fairly large.</p>
  
  <p>Any ideas?</p>