17

As I realized once again connected with this answer to Joining attributes by location using QGIS, creating a spatial index can result in huge performance improvements.

However, I was wondering why spatial indexes are not created as a default in GIS software.

Are there any reasons why one should NOT create a spatial index?

I use QGIS 3.16 on Win10 and I am looking for answers for this setting, but I guess the answer will be not software-specific.

The only possible reason I could imagine not to create a spatial index is again connected with performance: creating a spatial index takes some time/processing power and storage capacity. For smaller datasets, these indexes do not bring such a huge improvement. And if you only want to visualize data (map making), spatial indexes are of no use (though maybe even that is not the case: https://gis.stackexchange.com/a/62302/88814).

Still, considering the huge performance improvement related to the modest additional requirements for processing and storing, I see no reason why one should not always create a spatial index.

Are there reasons/scenarios not to do so?

Improve this question
4
  • 7
    The When is also very important. One will want to create indexes when done preparing/inserting all rows, not necessarily at table creation time. Also you could be storing functional indexes (ex: casting to geography, transforming CRS, generating a polygon around a point etc) and have no or little use of an index on the original geometry. If ultimately the table will be indexed, it is very convenient to have full control on what/when/how it is indexed.
    – JGH
    Nov 9, 2020 at 18:21
  • 1
    I haven't paid close enough attention as to how or when this happens, but several times when QGIS has failed to draw all the features in a layer, I found that I needed to delete or update the spatial index. So I now tend to avoid creating one unless I plan on running some processing tools, and then I usually do that on a temporary layer made using the check validity.
    – John
    Nov 11, 2020 at 14:48
  • 1
    In this context of processing it is beneficial to have spatial index for the source layers and intermediate outputs. I have logged a feature request with QGIS for this github.com/qgis/QGIS/issues/38859
    – kartoza-geek
    Nov 14, 2020 at 18:06
  • 1
    In addition to the excellent comments above, another reason might be that some data sources change so frequently (features are added/deleted/updated) that the maintenance of a spatial index might be a net loss.
    – Jan Šimbera
    Dec 25, 2021 at 14:24

2 Answers 2

Reset to default
7

Spatial index is crucial for spatial querying (within distance, intersection etc.) when it comes to large datasets. But it does take some time to calculate it, the index takes some storage, and must be updated with edits done to spatial data.

When not to have spatial index:

  1. When it is not expected to search/query based on geometry. Typically database primarily focused on other attributes/data where geometry is just complimentary value to display together with search result based on other values.

  2. When expected to do large/bulk insert/update/delete. Drop spatial index if exist before such action and (re)create after if you wish.

  3. When expected to do high frequency updates. Better to do spatial index and search/query on copy.

  4. When dataset is small (especially point features), or contain large percentage of multi/overlapping (multi-polygon) complex features when spatial index would not add much to performance.

  5. When dataset contains multiple geometry columns, and only some is used for search/query.

  6. When saving data storage is of crucial importance over benefits of index.

As one of the comments says it is good to have full control over when/how to create spatial index even though majority of use cases might fall into must have spatial index category.

Improve this answer
0

I would say that one doesn't necessarily need to exist for small datasets or when fast parsing is not necessary.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.