I should start by saying that I do not have much experience with GIS so this question may be foolish. What I do have is a database for performing a lookup from a set of coordinates to find the bounding box or LSD in which they reside. At the moment the database contains several thousand tables and we run some client side code to decide on which table to query, load that entire table back to the client and filter it client side. That seems dramatically inefficient to me as we are passing back quite a lot of data. Each table defines a large area at which to look and each record defines a smaller area. The areas are defined by 4 corner points. I suppose the original design was based on a quadtree but instead of there being four children and many levels it was decided to have only two levels and many children at each level. I am looking to perform some optimization of this structure.
The database is SQL server 2005 so there is no help available from native geospatial datatypes. What I would like to know is
- Is this sort of thousand+ table database common for GIS work?
- Would it not be more efficient to keep the data in a single table and take advantage of some index magic? (Is it possible to index efficiently on the decimals which define each point of the bounding box such that you can find intermediate values?)
- How could this structure be improved to allow for rapid queries?