10

I was asking on a forum if it could be a good idea to create spatial indexes on subsets of a table when your application display on map only those subsets and never the entire table.

I was asking this question because I though that as the subsets do not have the same extents of the whole table, maybe it would be faster to display the subsets with their own spatial indexes.

The answer I received was that spatial indexes do not affect display time but only used for spatial queries such union or intersect. Is it true??? My experience with GIS and database is when a table has no spatial index, the display on map is much slower. I always thought that on display the table was queried to show the features that intersect the current map window extent, so the features outside are not loaded for nothing. Is it really how it works? This is a kind of spatial query no?

What is the truth? Is it a good idea to create spatial indexes on subsets?

Improve this question

2 Answers 2

Reset to default
15

For displaying purposes it is always good to use a spatial index. It will improve speed of both rendering and spatial queries. However, if you plan to update large quantities of objects, it might be wise to remove the spatial index during the update. Otherwise the update process will become significantly slower, because with every update the spatial index needs to be updated as well. After updating you can add a spatial index again.

Improve this answer
1
  • 1
    To add to Marks comments an ESRI Shapefile is often created without a spatial index and adding one can significantly improve display in ArcMap. If your data is in a personal or file geodatabase these automatically have a spatial index. How can you tell? Well in ArcMap the Shape field will have a little star next to it when you view the data in table mode.
    – Hornbydd
    May 30, 2013 at 20:46
5

On large databases or a database with may changes it can be very important to have spatial indexes in place and updated regularly.

(Keeping it simple here)

For example for Oracle Spatial indexing capabilities into the Oracle database engine is a key feature of the Spatial product. A spatial index, like any other index, provides a mechanism to limit searches, but in this case based on spatial criteria such as intersection and containment. A spatial index is required to:

  • Find objects within an indexed data space that overlap a given point or area-of-interest (window query)
  • Find pairs of objects from within two indexed data spaces that spatially interact with each other (spatial join)

Spatial Indexing with Oracle 11.2g

http://docs.oracle.com/cd/E11882_01/appdev.112/e11830/sdo_index_query.htm

Improve this answer
3
  • +1 For SQL Server discussion see Isaac Kunen's blog post on Basic Multi-Level Grids.
    – Kirk Kuykendall
    May 31, 2013 at 15:31
  • But, if you are making a significant number of edits it is possible that the spatial index will reduce access speed. That is, with each edit, if the dbase is recomputing the index you can incur a large performance hit. The timing of updates in a high use environment is important.
    – Jay Laura
    May 31, 2013 at 15:51
  • 1
    Jay depends on if the database is setup for transactional (quick read/writes) or Data Warehousing (data long loads and Data read only) ss64.com/ora/syntax-architecture.html
    – Mapperz ♦
    May 31, 2013 at 16:26

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.