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

Usage Tips

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)