Back Up Geospatial Indexes

When you upgrade, the geospatial indexes are invalid. You must back up spatial indexes that contain polygon geometry data into a temporary table and rebuild them in the newer Place version using the temporary table as the set of input polygons.

The following steps backup your spatial indexes:

  1. Build an index with STV_Create_Index:

    => SELECT STV_Create_Index(123, ST_GeomFromText('POLYGON((1 2, 2 3, 3 1, 1 2))')
       USING PARAMETERS index='pol_idx') OVER();
       type   | polygons | SRID | min_x | min_y | max_x | max_y | info
    ----------+----------+------+-------+-------+-------+-------+------
     GEOMETRY | 1        | 0    | 1     | 1     | 3     | 3     |
    (1 row)
  2. Save the index polygons in a temporary table. Use STV_Describe_Index with the list_polygons option to get the polygons from the index. The \d command describes temp-table:

    => CREATE TABLE temp-table
       AS SELECT STV_Describe_Index(USING PARAMETERS index='pol_idx', list_polygons=true) OVER();
    
    => \d temp-table
                                           List of Fields by Tables
     Schema |  Table     |  Column  |     Type      | Size | Default | Not Null | Primary Key | Foreign Key
    --------+------------+----------+---------------+------+---------+----------+-------------+-------------
     public | temp-table | gid      | int           | 8    |         | f        | f           |
     public | temp-table | state    | varchar(20)   | 20   |         | f        | f           |
     public | temp-table | geometry | geometry(141) | 141  |         | f        | f           |
    (3 rows)
  3. The termporary table contains a copy of all polygons and identifiers in the indexes. The following command shows the contents of the temporary table:

    => SELECT gid, state, ST_AsText(geometry) FROM temp-table;
     gid | state   | ST_AsText
    -----+---------+--------------------------------
     123 | INDEXED | POLYGON ((1 2, 2 3, 3 1, 1 2))
    (1 row)

After you upgrade, use the polygons table as input to rebuild the index. For details, see Rebuild Geospatial Indexes.