Create spatial indexes on tables with an ST_Geometry column using SQL

When you create a spatial index, you need to provide the following information:

• A name for the index
• The name of the table that contains the spatial column on which the index is to be defined
• The name of the spatial column on which the index is to be defined
• The grid sizes (only necessary for databases that use a grid index, such as Oracle or DB2)

The following are examples of creating a spatial index on an ST_Geometry column in each of the supported databases.

Steps:

1. Open an SQL editor and connect to your database.
2. For Oracle, PostgreSQL, DB2, and Informix, use a CREATE INDEX statement to create the spatial index. For SQLite, use the CreateSpatialIndex function.
   • Oracle

     CREATE INDEX sa_idx
      ON sensitive_areas(zone)
      INDEXTYPE IS sde.st_spatial_index
      PARAMETERS('st_grids=1,3,0 st_srid=4326');
     
     CREATE INDEX hs_idx
      ON hazardous_sites(location)
      INDEXTYPE IS sde.st_spatial_index
      PARAMETERS('st_grids=1,0,0 st_srid=4326');
     

   • PostgreSQL

     CREATE INDEX sa_idx
      ON sensitive_areas 
      USING gist(zone st_geometry_ops);
     
     CREATE INDEX sa_idx
      ON hazardous_sites
      USING gist(location st_geometry_ops);
     

   • DB2

     CREATE INDEX sa_idx ON sensitive_areas(zone) 
      EXTEND USING db2gse.spatial_index (1.0, 3.0, 0.0)
     
     CREATE INDEX hs_idx ON hazardous_sites(location) 
      EXTEND USING db2gse.spatial_index (1.0, 0.0, 0.0)
     

   • Informix

     CREATE INDEX sa_ix
      ON sensitive_areas (zone ST_Geometry_ops)
      USING RTREE;
     
     CREATE INDEX hs_ix
      ON hazardous_sites (location ST_Geometry_ops)
      USING RTREE;
     

   • SQLite

     SELECT CreateSpatialIndex('mydatabase','sensitive_areas','zone','rtreexy');
     
     SELECT CreateSpatialIndex('mydatabase','hazardous_sites','location','rtreexy');