Getting ready

You'll need a SpatiaLite and a Postgis database. For ease, import a vector layer from the provided sample data and do not select the Create spatial index option when importing. (Not sure how to import data? Refer to Chapter 1, Data Input and Output, for how to do this.)

How to do it…

Using the DB Manager plugin (in the Database menu), perform the following steps:

How it works…

When you create a spatial index, the database stores a bounding box rectangle for every spatial object in the geometry column. These boxes are also sorted so that boxes near each other in coordinate space are also near each other in the index.

When queries are run involving a location, a comparison is made against the boxes, which is a simple math comparison. Rows with boxes that match the area in question are then selected to be tested in depth for a precise match, based on their real geometries. This method of searching for intersection is faster than testing complex geometries one by one because it quickly eliminates items that are clearly not near the area of interest.

There's more…

Spatial indexes are really important to speed up the loading time of database spatial layers in QGIS. They also play a critical role in the speed of spatial queries (such as intersects). Note that PostGIS will automatically use a spatial index if one is present. SpatiaLite requires that you write queries that intentionally call a particular spatial index (Refer to Haute Cuisine examples from the SpatiaLite Cookbook)

Also, keep in mind that only one spatial index per table can be used in a single query. This really comes into play if you happen to have more than one spatial column or create a spatial index in a different projection than the geometry (check out the PostGIS Cookbook by Packt Publishing for more information).

Do you want to check lots of tables at once? You can list all GIST indexes in PostGIS at once:

SELECT i.relname as indexname, idx.indrelid::regclass as tablename,
  am.amname as typename,
ARRAY(SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)
  FROM generate_subscripts(idx.indkey, 1) as k
  ORDER BY k
  ) as indkey_names
FROM pg_index as idx
JOIN pg_class as i  ON  i.oid = idx.indexrelid
JOIN pg_am as am  ON  i.relam = am.oid
JOIN pg_namespace as ns  ON  ns.oid = i.relnamespace
AND ns.nspname = ANY(current_schemas(false))
Where am.amname Like 'gist';

To do something similar in SpatiaLite, use the following:

SELECT * FROM geometry_columns WHERE spatial_index_enabled = 1;

See also