STV_Describe_Index

Retrieves information about an index that contains a set of polygons.

Retrieves information about an index that contains a set of polygons. If you do not pass any parameters, STV_Describe_Index returns all of the defined indexes.

The OVER() clause must be empty.

Behavior type

Immutable

Syntax

STV_Describe_Index ( [ USING PARAMETERS [index='index_name']
                                        [, list_polygons={true | false } ]] ) OVER ()

Arguments

index = 'index_name'
Name of the index, type VARCHAR. Index names cannot exceed 110 characters. The slash, backslash, and tab characters are not allowed in index names.
list_polygon
(Optional) BOOLEAN that specifies whether to list the polygons in the index. The index argument must be used with this argument.

Returns

polygons
Number of polygons indexed.
SRID
Spatial reference system identifier.
min_x, min_y, max_x, max_y
Coordinates of the minimum bounding rectangle (MBR) of the indexed geometries. (min_x, min_y) are the south-west coordinates, and (max_x, max_y) are the north-east coordinates.
name
The name of the spatial index(es).
gid
Name of an integer column that uniquely identifies the polygon. The gid cannot be NULL.
state
The spatial object's state in the index. Possible values are:

  • INDEXED - The spatial object was successfully indexed.

  • SELF_INTERSECT - (WGS84 Only) The spatial object was not indexed because one of its edges intersects with another of its edges.

  • EDGE_CROSS_IDL - (WGS84 Only) The spatial object was not indexed because one of its edges crosses the International Date Line.

  • EDGE_HALF_CIRCLE - (WGS84 Only) The spatial object was not indexed because it contains two adjacent vertices that are antipodal.

  • NON_INDEXABLE - The spatial object was not able to be indexed.

geography

The Well-Known Binary (WKB) representation of the spatial object.

geometry

The Well-Known Binary (WKB) representation of the spatial object.

Privileges

Any user with access to the STV_*_Index functions can describe, rename, or drop indexes created by any other user.

Limitations

Some functionality will require the index to be rebuilt if the index was created with 24.2.x or earlier.

Examples

The following examples show how to use STV_Describe_Index.

Retrieve information about the index:

=> SELECT STV_Describe_Index (USING PARAMETERS index='my_polygons') OVER ();
   type   | polygons | SRID | min_x | min_y | max_x | max_y
----------+----------+------+-------+-------+-------+-------
 GEOMETRY |        4 |    0 |    -1 |    -1 |    12 |    12

(1 row)

Return the names of all the defined indexes:

=> SELECT STV_Describe_Index() OVER ();
       name
------------------
MA_counties_index
my_polygons
NY_counties_index
US_States_Index
(4 rows)

Return the polygons included in an index:

=> SELECT STV_Describe_Index(USING PARAMETERS index='my_polygons', list_polygons=TRUE) OVER ();
 gid |     state     |            geometry
-----+---------------+----------------------------------
  12 | INDEXED       | \260\000\000\000\000\000\000\ ...
  14 | INDEXED       | \200\000\000\000\000\000\000\ ...
  10 | NON_INDEXABLE | \274\000\000\000\000\000\000\ ...
  11 | INDEXED       | \260\000\000\000\000\000\000\ ...
(4 rows)

See also