asked
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)?
No. What you can do is create multiple different indexes with varying density and layers then you can try query hints to point the query to the appropriate index. That's really your only option.
Thanks for your reply Grant. Yes that is an option but would be very fiddly to make it workable. My layers are roughly the same size and the contents have similar densities, so I'd have to "fake something up" but e.g. putting in a fake coordinate offset to make different layers spatially separate.... but 4000 of them? I have a couple of ideas of what I can do but didn't want to miss an easy win if there is one :-) Am wondering if MSSQL 2012 has better support for what I need.
Not that I'm aware of. Spatial indexes work really well when they work, but they're very persnickety beasts at the best of time. I found that for an identical data set I needed two different indexes, one for exclusions and one for inclusions because the different indexes worked better depending on if you were excluding more data or including more. What a pain.
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.
11 People are following this question.
