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.

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.

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.