4.3.3. Managing indexes

The Importer/Exporter allows the user to manually activate or deactivate indexes on predefined tables of the 3D City Database schema and to check their status.

../../_images/impexp_gui_managing_indexes_fig.png

Fig. 4.8 Managing spatial and normal indexes.

The operation dialog differentiates between spatial indexes on geometry columns and normal indexes on columns with any other datatype. The buttons Activate, Deactivate, and Status trigger a corresponding database process on spatial indexes only, normal indexes only or both index types depending on which checkboxes are selected.

The VACUUM button is only available for PostgreSQL databases and performs a VACUUM ANALYZE operation on the columns of the selected indexes. This maintenance operation gathers and updates statistics on the columns to be able to choose the most efficient query plans and optimize the speed of query processing. Note that for most PostgreSQL databases, VACUUM is already run automatically at regular intervals.

The index operations only affect the following subset of all indexes defined by the 3D City Database schema:

Table 4.4 Spatial and normal indexes affected by the index operation
Index type
Column(s)
Table
Spatial
ENVELOPE
CITYOBJECT
Spatial
GEOMETRY
SURFACE_GEOMETRY
Spatial
SOLID_GEOMETRY
SURFACE_GEOMETRY
Normal
GMLID, GMLID_CODESPACE
CITYOBJECT
Normal
LINEAGE
CITYOBJECT
Normal
CREATION_DATE
CITYOBJECT
Normal
TERMINATION_DATE
CITYOBJECT
Normal
LAST_MODIFICATION_DATE
CITYOBJECT
Normal
GMLID, GMLID_CODESPACE
SURFACE_GEOMETRY
Normal
GMLID, GMLID_CODESPACE
APPEARANCE
Normal
THEME
APPEARANCE
Normal
GMLID, GMLID_CODESPACE
SURFACE_DATA
Normal
GMLID, GMLID_CODESPACE
ADDRESS

The result of an index operation is reported in the console window. For instance, Fig. 4.9 shows the result of a status query on both spatial and normal indexes. The status ON means that the corresponding index is enabled.

../../_images/impexp_gui_indexes_status_report_fig.png

Fig. 4.9 Result of a status query on spatial and normal indexes.

Note

It is strongly recommended to deactivate the spatial indexes before running a CityGML/CityJSON import on a big amount of data and to reactive the spatial indexes afterwards. This way the import will typically be a lot faster than with spatial indexes enabled. The situation may be different when importing only a small dataset.

Caution

Activating and deactivating indexes can take a long time, especially if the database fill level is high. Note that the operation cannot be aborted by the user since this would result in an inconsistent database state.