ArcGIS Desktop

  • Documentation
  • Support

  • My Profile
  • Help
  • Sign Out
ArcGIS Desktop

ArcGIS Online

The mapping platform for your organization

ArcGIS Desktop

A complete professional GIS

ArcGIS Enterprise

GIS in your enterprise

ArcGIS for Developers

Tools to build location-aware apps

ArcGIS Solutions

Free template maps and apps for your industry

ArcGIS Marketplace

Get apps and data for your organization

  • Documentation
  • Support
Esri
  • Sign In
user
  • My Profile
  • Sign Out

Help

  • Home
  • Get Started
  • Map
  • Analyze
  • Manage Data
  • Tools
  • More...

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.

  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 hs_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');
      

Related topics

  • Spatial indexes and ST_Geometry

ArcGIS Desktop

  • Home
  • Documentation
  • Support

ArcGIS Platform

  • ArcGIS Online
  • ArcGIS Desktop
  • ArcGIS Enterprise
  • ArcGIS for Developers
  • ArcGIS Solutions
  • ArcGIS Marketplace

About Esri

  • About Us
  • Careers
  • Insiders Blog
  • User Conference
  • Developer Summit
Esri
Tell us what you think.
© Copyright 2016 Environmental Systems Research Institute, Inc. | Privacy | Legal