STV_Refresh_Index

Appends newly added or updated polygons and removes deleted polygons from an existing spatial index.

The OVER() clause must be empty.

Behavior type

Mutable

Syntax

STV_Refresh_Index( gid, g
                   USING PARAMETERS index='index_name'
                    [, skip_nonindexable_polygons={ true | false } ] )

                 OVER()
                    [ AS (type, polygons, srid, min_x, min_y, max_x, max_y, info,
               indexed, appended, updated, deleted) ]

Arguments

gid: Name of an integer column that uniquely identifies the polygon. The gid cannot be NULL.
g: Name of a geometry or geography (WGS84) column or expression that contains polygons and multipolygons. Only polygon and multipolygon can be indexed. Other shape types are excluded from the index.

Parameters

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.

skip_nonindexable_polygons = { true | false }

(Optional) BOOLEAN

In rare cases, intricate polygons (for instance, with too high resolution or anomalous spikes) cannot be indexed. These polygons are considered non-indexable. When set to False, non-indexable polygons cause the index creation to fail. When set to True, index creation can succeed by excluding non-indexable polygons from the index.

To review the polygons that were not able to be indexed, use STV_Describe_Index with the parameter list_polygon.

Default: False

Returns

type: Spatial object type of the index.
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.
info: Lists the number of excluded spatial objects as well as their type that were excluded from the index.
indexed: Number of polygons indexed during the operation.
appended: Number of appended polygons.
updated: Number of updated polygons.
deleted: Number of deleted polygons.

Supported data types

Data Type	GEOMETRY	GEOGRAPHY (WGS84)
Point	No	No
Multipoint	No	No
Linestring	No	No
Multilinestring	No	No
Polygon	Yes	Yes
Multipolygon	Yes	No
GeometryCollection	No	No

Privileges

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

Limitations

In rare cases, intricate polygons (such as those with too-high a resolution or anomalous spikes) cannot be indexed. See the parameter skip_nonindexable_polygons.
If you replace a valid polygon in the source table with an invalid polygon, STV_Refresh_Index ignores the invalid polygon. As a result, the polygon originally indexed persists in the index.
The following geometries cannot be indexed:
- Non-polygons
- NULL gid
- NULL (multi) polygon
- EMPTY (multi) polygon
- Invalid (multi) polygon
The following geographies are excluded from the index:
- Polygons with holes
- Polygons crossing the International Date Line
- Polygons covering the north or south pole
- Antipodal polygons

Usage tips

To cancel an STV_Refresh_Index run, use Ctrl + C.
If you use source data not previously associated with the index, then the index will be overwritten.
If STV_Refresh_Index has insufficient memory to process the query, then rebuild the index using STV_Create_Index.
If there are no valid polygons in the geom column, STV_Refresh_Index reports an error in vertica.log and stops the index refresh.
Ensure that all of the polygons you plan to index are valid polygons. STV_Create_Index and STV_Refresh_Index do not check polygon validity when building an index.

For more information, see Ensuring polygon validity before creating or refreshing an index.

Examples

The following examples show how to use STV_Refresh_Index.

Refresh an index with a single literal argument:

=> SELECT STV_Create_Index(1, ST_GeomFromText('POLYGON((0 0,0 15.2,3.9 15.2,3.9 0,0 0))')
     USING PARAMETERS index='my_polygon') OVER();
   type   | polygons | SRID | min_x | min_y | max_x | max_y | info
----------+----------+------+-------+-------+-------+-------+------
 GEOMETRY |        1 |    0 |     0 |     0 |   3.9 |  15.2 |
(1 row)

=> SELECT STV_Refresh_Index(2, ST_GeomFromText('POLYGON((0 0,0 13.2,3.9 18.2,3.9 0,0 0))')
     USING PARAMETERS index='my_polygon') OVER();
   type   | polygons | SRID | min_x | min_y | max_x | max_y | info | indexed | appended | updated | deleted
----------+----------+------+-------+-------+-------+-------+------+---------+----------+---------+---------
 GEOMETRY |        1 |    0 |     0 |     0 |   3.9 |  18.2 |      |       1 |        1 |       0 |       1
(1 row)

Refresh an index from a table:

=> CREATE TABLE pols (gid INT, geom GEOMETRY);
CREATE TABLE
=> COPY pols(gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1|POLYGON((-31 74,8 70,8 50,-36 53,-31 74))
>> 2|POLYGON((5 20,9 30,20 45,36 35,5 20))
>> 3|POLYGON((12 23,9 30,20 45,36 35,37 67,45 80,50 20,12 23))
>> \.
=> SELECT STV_Create_Index(gid, geom USING PARAMETERS index='my_polygons_1', overwrite=true)
     OVER() FROM pols;
   type   | polygons | SRID | min_x | min_y | max_x | max_y | info
----------+----------+------+-------+-------+-------+-------+------
 GEOMETRY |        3 |    0 |   -36 |    20 |    50 |    80 |
(1 row)

=> COPY pols(gid, gx filler LONG VARCHAR, geom AS ST_GeomFromText(gx)) FROM stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 6|POLYGON((-32 74,8 70,8 50,-36 53,-32 74))
>> \.
=> SELECT STV_Refresh_Index(gid, geom USING PARAMETERS index='my_polygons_1') OVER() FROM pols;
   type   | polygons | SRID | min_x | min_y | max_x | max_y | info | indexed | appended | updated | deleted
----------+----------+------+-------+-------+-------+-------+------+---------+----------+---------+---------
 GEOMETRY |        4 |    0 |   -36 |    20 |    50 |    80 |      |       1 |        1 |       0 |       0
(1 row)