Using a MSSQL Spatial Index within a composite index
We store national highways data in an SQL 2008 R2 (SP2) dbase in a table called MAPITEMS. This has columns LAYERID int, ITEMID int, GEOMWM geometry The geometry represents many kinds of geometry types of different sizes, but the vast majority are small 10m lines. The table is indexed on (LAYERID, ITEMID) and also spatially on GEOMWM with a "LOW" density spatial index, with a bounding box set to cover the UK ("SIDX"). Our data is split by "layers", so for any given geographic area, we have items from different layers "overlapped" in space. We have about 4000 layers and each layer around 100,000 items. Not all the layers overlap but many do. Our queries into this table always filter by layer_id and by an STIntersects with a query envelope. Queries with small envelopes benefit from the SIDX but obviously large ones do not, as the query engine has to filter out items from the layer we want from all the geom results from all the other layers. Is there a way to create a composite spatial index that would look up first by LAYERID and then by our SIDX? Something that works like (LAYERID, GEOMWM)?
The best permanent solution I can think of is to abandon the MSSQL SIDX and use home brewed cell IDs per item. Because some (very few) items will span multiple cells, I need an extra table like: table MAPITEMLOOKUP LAYERID int CELLID int ITEMID int with (LAYERID, CELLID, ITEMID) as my PK I can then use this in a join to my MAPITEM table. To generate and use the CELLIDs I'd use a single (small) reference table with geometry polygons for each cell covering the whole map, with a SIDX on those. Then I can get a quick result returning the cells covering any item, or indeed get a cell list that covers my query envelope. When I get my mapitem results back, I could then use STIntersect to refine the results, although in practice I don't care about a few false positives, as long as there are no false negatives. But.... why should I work as hard this... ? :-) If only the MSSQL SIDX would have a nice handy int or bigint baked in to use as a filter in its cell IDs... I was kind of hoping I could hijack the SRID in the geometry of each item & match with the query envelope SRID to do this, but no such luck.