Creating spatial indexes_QGIS 2 Cookbook-QQ阅读现言女生网

上QQ阅读APP看书，第一时间看更新

Creating spatial indexes

Spatial indexes are methods to speed up queries of geometries. This includes speeding up the display of database layers in QGIS when you zoom in close (it has no effect on viewing entire layers).

This recipe applies to SpatiaLite and PostGIS databases. In the event that you've made a new table or you have imported some data and didn't create a spatial index, it's usually a good idea to add this.

Tip

You can also create a spatial index for shapefile layers. Take a look at Layer Properties | General for the Create Spatial Index button. This will create a .qix file that works with QGIS, Mapserver, GDAL/OGR, and other open source applications. Refer to https://en.wikipedia.org/wiki/Shapefile.

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:

Check whether the index does not exist. In DB Manager, open the database and then open the table that you want to check. Looking at the properties on the right, you should see a message just above Fields that looks like this:
However, what if no index was listed for the geom column? Then, we can make one just by clicking the create it link. Or you can do this in a SQL window, as follows:
- For SpatiaLite, use the following:
```
SELECT CreateSpatialIndex('schools_wake', 'geom');
```
- For PostGIS, use the following:
```
CREATE INDEX sidx_census_wake2000_geom
  ON public.census_wake2000 USING gist(geom);
```
Verify that the index exists, as follows:
- For PostGIS (the left-hand side of the following screenshot), on the right-hand side, scroll to the bottom looking for the Indexes section
- For SpatiaLite (the right-hand side of the following screenshot), you can see the idx_nameoftable_geomcolumn listed as a table:

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).

Tip

If you plan to insert many records into a table with an existing spatial index, you may want to disable or drop the index and recreate it after the import is done. Otherwise, the index will be recalculated after each row is inserted. This applies to nonspatial indexes too.

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;