I’ve got a large Spatialite database with a table of lines (roads, rail and water). I created a spatial index of the geometry to speed things up.

This works amazingly well when I’m zoomed in close. However, when I’m zoomed out in QGIS 1.8, the display is slower than I’d hope. And so I created a separate Table-View of just the major roads, intending to speed things up. That didn’t work—I suppose because the display is still plowing through all the roads just to find the major roads.

Is it justified for me to create another spatial table of “just the major roads” to use when I’m zoomed out. Or, is there some way to index the queried column for major roads, or some other trick, and thus create a very speedy display of the major roads without creating a brand new spatial table? I’m trying to keep my Spatialite database as small and practical as possible, without filling it with a lot of redundant tables of data. I’m finding that Spatialite is extremely quick, but perhaps I’m expecting too much performance, though I’d still like to get as much as possible.

  • Have you tried creating a QGIS layer style which only renders major roads when zoomed out? The bottleneck in this case most certainly is rendering and not the database backend. – underdark May 16 '13 at 16:07
  • Yes, I've done that, and it works great as long as the layer is reading from a table with only major roads in it, which is exactly what I've created. I had discovered that a table-view of only major roads, queried from all roads, didn't work well because it the table-view is still looking through the entire table of all roads, for just the major roads. I hoped I could just have all lines in one table so I could easily query everything, and not have redundant data making the .sqlite file larger that it had to be. – user12711 May 16 '13 at 17:47
  • 2
    do you have an index on the classification field that defines major roads? – underdark May 16 '13 at 18:09
  • Thanks, That probably helped some--at extent it renders in 40 seconds, for the major_roads in my classification (a very small number of lines to render). But my test layer with both major and secondary roads, but not the bulk of very minor roads, within a spatial table by themselves rendered in about 8 seconds at same scale. It's probably fine-tuned as much as possible now. I'm working with a large set of data, so I'm happy. Spatialite is VERY QUICK overall. Thanks – user12711 May 16 '13 at 20:43
  • @underdark I suggest to turn your helpful comment to an answer. – AndreJ Aug 19 '16 at 19:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.