Database Functions

The VCDB v5 includes a set of database-side functions that are automatically installed during setup. These functions perform and expose various database operations, such as deleting or terminating city objects, computing their envelopes, and managing the Coordinate Reference System (CRS) of the VCDB instance. Additionally, they include utility and helper functions. The functions can be used to automate processes and workflows or be integrated into third-party tools for task automation.

For PostgreSQL, the functions are written in PL/pgSQL and reside in the dedicated citydb_pkg and vcdb_pkg schemas. Since both citydb_pkg and vcdb_pkg are automatically added to the database search_path during setup, they can be called without explicitly specifying the schema as a prefix.

Delete functions

The delete functions enable the removal of single or multiple city objects while automatically managing integrity constraints between database tables. They serve as low-level APIs, providing dedicated delete functions for various tables — from individual geometries in the GEOMETRY_DATA table (delete_geometry_data function) to entire features in the FEATURE table, alongside their properties, geometries, and appearances (delete_feature function). These functions enable users to develop more complex delete operations without reimplementing their core functionality.

The available delete functions are listed below.

Function Return type Description

Function	Return type	Description
`cleanup_schema` `(schema_name TEXT)`	`void`	Truncates all data tables
`delete_feature` `(pid bigint, schema_name TEXT)` or `(pid_array bigint[], schema_name TEXT)`	`BIGINT` or `SETOF BIGINT`	Deletes entries from `FEATURE` table based on an `id` or `id` array
`delete_property` `(pid bigint, schema_name TEXT)` or `(pid_array bigint[], schema_name TEXT)`	`BIGINT` or `SETOF BIGINT`	Deletes entries from `PROPERTY` table based on an `id` or `id` array
`delete_geometry_data` `(pid bigint, schema_name TEXT)` or `(pid_array bigint[], schema_name TEXT)`	`BIGINT` or `SETOF BIGINT`	Deletes entries from `GEOMETRY_DATA` table based on an `id` or `id` array
`delete_implicit_geometry` `(pid bigint, schema_name TEXT)` or `(pid_array bigint[], schema_name TEXT)`	`BIGINT` or `SETOF BIGINT`	Deletes entries from `IMPLICIT_GEOMETRY` table based on an `id` or `id` array
`delete_appearance` `(pid bigint, schema_name TEXT)` or `(pid_array bigint[], schema_name TEXT)`	`BIGINT` or `SETOF BIGINT`	Deletes entries from `APPEARANCE` table based on an `id` or `id` array
`delete_surface_data` `(pid bigint, schema_name TEXT)` or `(pid_array bigint[], schema_name TEXT)`	`BIGINT` or `SETOF BIGINT`	Deletes entries from `SURFACE_DATA` table based on an `id` or `id` array
`delete_tex_image` `(pid bigint, schema_name TEXT)` or `(pid_array bigint[], schema_name TEXT)`	`BIGINT` or `SETOF BIGINT`	Deletes entries from `TEX_IMAGE` table based on an `id` or `id` array
`delete_address` `(pid bigint, schema_name TEXT)` or `(pid_array bigint[], schema_name TEXT)`	`BIGINT` or `SETOF BIGINT`	Deletes entries from `ADDRESS` table based on an id or id array

cleanup_schema
(schema_name TEXT)

void

Truncates all data tables

delete_feature
(pid bigint, schema_name TEXT) or
(pid_array bigint[], schema_name TEXT)

BIGINT or
SETOF BIGINT

Deletes entries from FEATURE table based on an id or id array

delete_property
(pid bigint, schema_name TEXT) or
(pid_array bigint[], schema_name TEXT)

BIGINT or
SETOF BIGINT

Deletes entries from PROPERTY table based on an id or id array

delete_geometry_data
(pid bigint, schema_name TEXT) or
(pid_array bigint[], schema_name TEXT)

BIGINT or
SETOF BIGINT

Deletes entries from GEOMETRY_DATA table based on an id or id array

delete_implicit_geometry
(pid bigint, schema_name TEXT) or
(pid_array bigint[], schema_name TEXT)

BIGINT or
SETOF BIGINT

Deletes entries from IMPLICIT_GEOMETRY table based on an id or id array

delete_appearance
(pid bigint, schema_name TEXT) or
(pid_array bigint[], schema_name TEXT)

BIGINT or
SETOF BIGINT

Deletes entries from APPEARANCE table based on an id or id array

delete_surface_data
(pid bigint, schema_name TEXT) or
(pid_array bigint[], schema_name TEXT)

BIGINT or
SETOF BIGINT

Deletes entries from SURFACE_DATA table based on an id or id array

delete_tex_image
(pid bigint, schema_name TEXT) or
(pid_array bigint[], schema_name TEXT)

BIGINT or
SETOF BIGINT

Deletes entries from TEX_IMAGE table based on an id or id array

delete_address
(pid bigint, schema_name TEXT) or
(pid_array bigint[], schema_name TEXT)

BIGINT or
SETOF BIGINT

Deletes entries from ADDRESS table based on an id or id array

The delete functions are provided in two forms:

Deletion of single entries: One variant accepts the primary key id of a single entry to be deleted and returns the id value if the deletion is successful. If NULL is returned, it indicates that the entry has either already been deleted or an error occurred during the deletion process.
Deletion of multiple entries: The other variant accepts an array of id values, returning the id values of the successfully deleted entries as a SETOF BIGINT, allowing multiple entries to be deleted in a single operation.

All functions offer an optional schema_name parameter, allowing you to apply them to different database schemas within your PostgreSQL database. The provided target schema must contain a VCDB v5 instance. For more information, see below.

The following example demonstrates how to easily delete features based on a query result:

-- delete a single feature by id
SELECT delete_feature(id) FROM feature WHERE ... ;

-- delete multiple features by passing an array of ids
SELECT delete_feature(array_agg(id)) FROM feature WHERE ... ;

The id-array based delete functions require fewer DELETE statements and may therefore be faster than deleting the same number of entries by invoking the delete function for each individual id. However, this is not always the case and depends on the ratio between the number of entries to be deleted and the total number of objects in the database. For example, if the id array is very large and covers a significant portion of the table, it may be more efficient to use the single-id version or delete entries in smaller batches.

The example below demonstrates how to create a custom function to delete all buildings from the VCDB using the single-id version of delete_feature:

-- example function for deleting all building features
DO $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN SELECT * FROM feature where objectclass_id = 901 LOOP
    -- call the delete_feature function for each id
    PERFORM delete_feature(rec.id);
  END LOOP;
END $$;

When deleting a feature, all its "contained" subfeatures, which are considered a part of the feature, are deleted as well. However, features that are only "related" but not considered a part of the feature are not deleted. The distinction between "contained" and "related" features is determined by evaluating the val_relation_type column of the PROPERTY table, as described here.

The cleanup_schema function serves a specific purpose: it truncates all database tables with a single function call. This is the most convenient and fastest way to delete all content from your VCDB v5. However, be cautious when using this function, as it cannot be rolled back.

Terminate functions

The delete functions physically remove city objects from the VCDB instance, helping keep the database streamlined and focused on the most recent versions of features. However, in some cases, it may be preferable to retain the feature history and avoid deleting outdated versions. For such use cases, the VCDB provides additional terminate functions. These functions do not physically delete features but instead mark them as terminated by setting their terminate_date property to the timestamp of the operation. Alongside the creation_date timestamp, the lifespan of the feature in the database can be tracked, allowing multiple historical versions of the same feature to be stored alongside its most recent version.

Function Return type Description

Function	Return type	Description
`terminate_feature` `(pid bigint, schema_name TEXT, metadata JSON, cascade BOOLEAN)` or `(pid_array bigint[], schema_name TEXT, metadata JSON, cascade BOOLEAN)`	`BIGINT` or `SETOF BIGINT`	Terminates features in the `FEATURE` table based on an `id` or `id` array

terminate_feature
(pid bigint, schema_name TEXT, metadata JSON, cascade BOOLEAN) or
(pid_array bigint[], schema_name TEXT, metadata JSON, cascade BOOLEAN)

BIGINT or
SETOF BIGINT

Terminates features in the FEATURE table based on an id or id array

Since the creation_date and termination_date columns are exclusive to the FEATURE table, only the terminate_feature function is available for terminating features. Like the delete functions, this function accepts either a single id or an array of id values and returns the id values of successfully terminated features, as described above.

The FEATURE table provides additional metadata columns for features, including last_modification_date, lineage, reason_for_update, and updating_person (see here for more details). The terminate functions allow you to update these values by passing a JSON object as metadata parameter, where each property represents the column name and its corresponding value is the updated data. When omitting single columns in the JSON object or the entire JSON object, the values currently stored in these columns remain unchanged, with the only exception that last_modification_date will be set to the same timestamp as termination_date.

The last parameter, cascade, is used to specify whether "contained" subfeatures should also be terminated (default: true). Terminating all subfeatures can take significantly longer than just terminating the feature itself, so it is important to evaluate whether cascading termination is necessary based on your use cases and scenarios.

The following example demonstrates how to terminate a single feature based on its database id.

SELECT terminate_feature(
    2060316,
    '{
        "reason_for_update": "test reason",
        "updating_peron": "test person",
        "lineage": "test lineage"
    }'::json,
    FALSE
);

When terminating features, make sure the tools you are using correctly evaluate the termination_date timestamp. This is essential when exporting or processing features to ensure that the tools are working with the correct version of the feature. The vcdb-tool included in VCDB v5 fully supports feature histories based on the creation_date and termination_date properties.

Envelope functions

The citydb_pkg package offers functions for calculating the 3D bounding box of features and implicit geometries, as well as additional utility functions to support these operations.

Function Return type Description

Function	Return type	Description
`get_feature_envelope` `(fid BIGINT, schema_name TEXT, compute_envelope INTEGER, set_envelope INTEGER)`	`GEOMETRY`	Returns the envelope geometry of a given feature
`get_implicit_geometry_envelope` `(gid BIGINT, ref_pt GEOMETRY, matrix JSON, schema_name TEXT)`	`GEOMETRY`	Returns the envelope geometry of a given implicit geometry
`get_envelope` `(geom GEOMETRY, schema_name TEXT)`	`GEOMETRY`	Computes the envelope geometry for a given geometry

get_feature_envelope
(fid BIGINT, schema_name TEXT, compute_envelope INTEGER, set_envelope INTEGER)

GEOMETRY

Returns the envelope geometry of a given feature

get_implicit_geometry_envelope
(gid BIGINT, ref_pt GEOMETRY, matrix JSON, schema_name TEXT)

GEOMETRY

Returns the envelope geometry of a given implicit geometry

get_envelope
(geom GEOMETRY, schema_name TEXT)

GEOMETRY

Computes the envelope geometry for a given geometry

The get_feature_envelope function returns the envelope of a feature. The feature’s primary key id must be provided as input. When the compute_envelope parameter is set to 0 (default), the envelope currently stored in the FEATURE table is returned. When set to 1 – or in case the value in the FEATURE table is NULL – the envelope is computed by evaluating all the geometries of the feature and its "contained" subfeatures across all LoDs, including implicit geometries. The returned geometry is the minimal 3D rectangle that encloses the feature, and it can be directly used as the value for the envelope column of the FEATURE table.

The get_feature_envelope function offers two more optional parameters: The set_envelope parameter specifies whether the computed envelopes should be used to update the envelope columns of the feature and its subfeatures (1 for true, 0 for false; default: 0). The schema_name parameter defines the target database schema to operate in, as explained below.

The envelope of implicit geometries can be calculated using the get_implicit_geometry_envelope function. It requires the following inputs: the primary key id of the template geometry from the GEOMETRY_DATA table, a PostGIS POINT geometry specifying the real-world coordinates where the template should be placed (ref_pt), and a 3x4 row-major matrix (JSON double array) defining the rotation, scaling, and translation for the template (matrix).

The reference point and transformation matrix follow the format used for storing them in the PROPERTY table (see here). Therefore, the values from the PROPERTY table can be directly used as input parameters.

The remaining get_envelope function is primarily used internally by the functions mentioned above. However, it can also be called directly to compute the envelope of a single geometry. The result is also returned in the format required by the envelope column of the FEATURE table.

Footprint functions

The vcdb_pkg package offers functions for calculating the 2D projected footprint of a feature and its recursively contained subfeatures, including explicit and implicit geometries. An optional skip-list can be used to exclude subfeatures of selected object classes from the recursion.

Function Return type Description

Function	Return type	Description
`get_feature_footprint` `(fid BIGINT, schema_name TEXT, lod INTEGER, skip_objectclass_ids INTEGER[])` or `(fid BIGINT, schema_name TEXT, lod TEXT, skip_objectclass_ids INTEGER[])`	`GEOMETRY`	Returns the 2D projected footprint of a feature and its contained subfeatures

get_feature_footprint
(fid BIGINT, schema_name TEXT, lod INTEGER, skip_objectclass_ids INTEGER[]) or
(fid BIGINT, schema_name TEXT, lod TEXT, skip_objectclass_ids INTEGER[])

GEOMETRY

Returns the 2D projected footprint of a feature and its contained subfeatures

The function requires the primary key id of the feature for which the footprint should be computed. Contained subfeatures are identified through the PROPERTY table and are included recursively.

The optional lod parameter restricts the calculation to geometries of the specified level of detail. If lod is omitted or set to NULL, geometries from all levels of detail are considered. The lod parameter supports both CityGML LoDs provided as integer values such as 0 to 4 and text values for custom LoD definitions.

The optional skip_objectclass_ids parameter is an array of objectclass_id values. A subfeature whose object class matches one of these values is excluded from the recursion, along with all of its subfeatures. The root feature is always included. If the parameter is NULL or an empty array, no subfeatures are skipped. For example, the following call excludes subfeatures with object class IDs 712 and 713:

SELECT vcdb_pkg.get_feature_footprint(123, NULL::TEXT, ARRAY[712, 713]);

When using positional parameters, NULL must be supplied for lod if a skip-list is provided without restricting the level of detail. Named parameters can be used to omit lod instead:

SELECT vcdb_pkg.get_feature_footprint(
  fid => 123,
  skip_objectclass_ids => ARRAY[712, 713]
);

The function considers both explicit and implicit geometries. Implicit geometries are transformed using their reference point and transformation matrix before being included in the projected footprint. Surface geometries are projected onto the XY plane and combined using a spatial union, while point and line geometries are ignored.

Because all surface geometries of the feature and its subfeatures are considered, roof overhangs and other installations such as balconies are also included in the calculation unless skipped explicitly. The result therefore does not necessarily represent the footprint of the feature on the ground.

The footprint calculation follows a best-effort approach. The input geometries are converted to 2D and decomposed into their individual components. Empty geometries and invalid individual components are excluded. This allows valid components to contribute to the footprint even if the original input geometry as a whole is invalid, such as valid polygons from an invalid MultiPolygon. If no valid geometry remains, the function returns NULL.

The best-effort approach allows a result to be calculated even if some of the input geometries are invalid. However, the calculated result may be incomplete. If a complete footprint is required, the input geometries should be checked for validity separately and repaired if needed before calling the function.

The schema_name parameter defines the target database schema to operate in, as explained below. If it is omitted, the function uses the schema currently set in the search_path.

CRS functions

The citydb_pkg package provides functions for performing CRS operations on a VCDB instance.

Function Return type Description

Function	Return type	Description
`change_schema_srid` `(target_srid INTEGER, target_srs_name TEXT, schema_name TEXT, transform INTEGER)`	`SETOF VOID`	Updates the coordinate system for a database schema
`change_column_srid` `(table_name TEXT, column_name TEXT, target_srid INTEGER, schema_name TEXT, transform INTEGER)`	`SETOF VOID`	Updates the coordinate system for a geometry column
`check_srid` `(srid INTEGER)`	`INTEGER`	Checks if a given `SRID` is valid
`get_coord_ref_sys_info` `(srid INTEGER)`	`TABLE`	Returns the name, type and WKT representation of the specified CRS
`is_coord_ref_sys_3d` `(srid INTEGER)`	`INTEGER`	Checks if a CRS is a true 3D system
`is_db_coord_ref_sys_3d` `(schema_name TEXT)`	`INTEGER`	Checks if the CRS of the VCDB is true 3D system

change_schema_srid
(target_srid INTEGER, target_srs_name TEXT, schema_name TEXT, transform INTEGER)

SETOF VOID

Updates the coordinate system for a database schema

change_column_srid
(table_name TEXT, column_name TEXT, target_srid INTEGER, schema_name TEXT, transform INTEGER)

SETOF VOID

Updates the coordinate system for a geometry column

check_srid
(srid INTEGER)

INTEGER

Checks if a given SRID is valid

get_coord_ref_sys_info
(srid INTEGER)

TABLE

Returns the name, type and WKT representation of the specified CRS

is_coord_ref_sys_3d
(srid INTEGER)

INTEGER

Checks if a CRS is a true 3D system

is_db_coord_ref_sys_3d
(schema_name TEXT)

INTEGER

Checks if the CRS of the VCDB is true 3D system

The primary function is change_schema_srid, which changes the CRS for all geometry columns within a VCDB schema (default: citydb). It takes the database-specifc SRID (Spatial Reference ID) of the new CRS and its OGC-compliant name as inputs.

The function operates in two modes, determined by the value of the transform parameter:

Update metadata only: Changes the geometry SRID in the database metadata without transforming coordinates (transform = 0, default).
Transform coordinates: Additionally transforms the coordinates of geometries already stored in the database to the new SRID (transform = 1).

Both modes serve different purposes. For example, if you accidentally set up your VCDB v5 with an incorrect SRID that does not match the CRS of the imported geometries, updating only the metadata is sufficient since the coordinates are already in the correct SRID. However, if the geometries are stored in the current SRID of the VCDB but need to be converted to another CRS, the second option is required to transform the coordinates accordingly.

As the final step, change_schema_srid automatically updates the metadata in the DATABASE_SRS table with the new values.

Regardless of the selected operation mode, changing the CRS of a VCDB v5 always involves dropping and re-creating spatial indexes on the geometry columns to maintain consistency with the new CRS. As a result, the process can be time-consuming depending on the table size.

Utility functions

The citydb_pkg and vcdb_pkg package also provide various utility functions as shown below.

Function Return type Description

Function	Return type	Description
`citydb_version` `(OUT version TEXT, OUT major_version INTEGER, OUT minor_version INTEGER, OUT minor_revision INTEGER)`	`TABLE`	Returns the version of the 3DCityDB instance
`vcdb_version` `(OUT version TEXT, OUT major_version INTEGER, OUT minor_version INTEGER, OUT minor_revision INTEGER)`	`TABLE`	Returns the version of the VCDB instance
`transform_implicit_geometry` `(gid BIGINT, ref_pt GEOMETRY, matrix JSONB)`	`GEOMETRY`	Returns the transformed geometry of an implicit geometry
`db_metadata` `(schema_name TEXT, OUT srid INTEGER, OUT srs_name TEXT, OUT coord_ref_sys_name TEXT, OUT coord_ref_sys_kind TEXT, OUT wktext TEXT, OUT versioning TEXT)`	`TABLE`	Returns meta information about the VCDB instance
`db_properties`()	`TABLE` `(id TEXT, name TEXT, value TEXT)`	Lists database properties such as enabled extensions or options relevant for the VCDB as name-value pairs.
`get_seq_values` `(seq_name TEXT, seq_count BIGINT)`	`SETOF BIGINT`	Returns `n` sequence values from the given sequence
`get_child_objectclass_ids` `(class_id INTEGER, schema_name TEXT, skip_abstract INTEGER)`	`SETOF INTEGER`	Returns the `id` values of all transitive subclasses of the given object class
`get_current_schema`()	`TEXT`	Returns the name of the active VCDB schema as determined by the current `search_path`
`set_current_schema` `(schema_name TEXT, local BOOLEAN)`	`SETOF VOID`	Sets the active VCDB schema to the specified name by adjusting the `search_path`
`schema_exists` `(schema_name TEXT)`	`INTEGER`	Checks whether the specified schema is a VCDB schema

citydb_version
(OUT version TEXT, OUT major_version INTEGER, OUT minor_version INTEGER, OUT minor_revision INTEGER)

TABLE

Returns the version of the 3DCityDB instance

vcdb_version
(OUT version TEXT, OUT major_version INTEGER, OUT minor_version INTEGER, OUT minor_revision INTEGER)

TABLE

Returns the version of the VCDB instance

transform_implicit_geometry
(gid BIGINT, ref_pt GEOMETRY, matrix JSONB)

GEOMETRY

Returns the transformed geometry of an implicit geometry

db_metadata
(schema_name TEXT, OUT srid INTEGER, OUT srs_name TEXT, OUT coord_ref_sys_name TEXT, OUT coord_ref_sys_kind TEXT, OUT wktext TEXT, OUT versioning TEXT)

TABLE

Returns meta information about the VCDB instance

db_properties()

TABLE
(id TEXT, name TEXT, value TEXT)

Lists database properties such as enabled extensions or options relevant for the VCDB as name-value pairs.

get_seq_values
(seq_name TEXT, seq_count BIGINT)

SETOF BIGINT

Returns n sequence values from the given sequence

get_child_objectclass_ids
(class_id INTEGER, schema_name TEXT, skip_abstract INTEGER)

SETOF INTEGER

Returns the id values of all transitive subclasses of the given object class

get_current_schema()

TEXT

Returns the name of the active VCDB schema as determined by the current search_path

set_current_schema
(schema_name TEXT, local BOOLEAN)

SETOF VOID

Sets the active VCDB schema to the specified name by adjusting the search_path

schema_exists
(schema_name TEXT)

INTEGER

Checks whether the specified schema is a VCDB schema

The transform_implicit_geometry function transforms an implicit geometry from its local coordinate system into world coordinates. It requires the primary key id of the corresponding entry in the IMPLICIT_GEOMETRY table, a Point geometry specifying the reference point (ref_pt), and a 3x4 transformation matrix in row-major format (matrix), represented as a JSON array of double values. The reference point and matrix use the same format as the corresponding parameters stored in the PROPERTY table (see here). Those parameters can therefore be passed directly to the function. If matrix is NULL, the identity matrix is used. If the source geometry cannot be found, the transformed reference point is returned when ref_pt is provided. Otherwise, the function returns NULL.

The transformation uses the database CRS stored in the DATABASE_SRS table as target.

The functions get_current_schema and set_current_schema simplify managing the active VCDB schema. While get_current_schema returns the schema currently set in the search_path, set_current_schema updates it to the specified name. The local parameter controls the scope of the change: when set to true (default), it applies the current transaction only; when false, it applies to the entire session until explicitly changed. Additionally, set_current_schema automatically includes required schemas like citydb_pkg, vcdb_pkg and public.

The schema_exists function can be used to verify whether the specified schema is a valid VCDB data schema.

Applying functions to different schemas

The VCDB supports using multiple data schemas within the same PostgreSQL database. The default schema is named citydb, while additional schemas can be freely named by the user. A setup script to create additional data schemas is included in the VCDB software package and is documented here.

Most of the database functions described in this chapter accept an optional schema_name parameter to specify the target schema. If this parameter is provided, the function temporarily sets the database search_path to the given schema, making it the active schema for that operation. This change to the search_path applies only for the duration of the current transaction — not the entire session. This ensures that any change to the search_path remains isolated and does not affect other operations outside the transaction, helping to avoid unintended side effects.

If schema_name is omitted, the function uses the schema currently set in the search_path. This offers flexibility, allowing users to configure the search path according to their needs before calling any 3DCityDB database functions.

When setting up a VCDB instance, the default schema is always citydb. If you are not working with multiple schemas, you can simply omit the schema_name parameter when calling functions — they will automatically operate on the citydb schema. For most users, this is the default and recommended approach for invoking the database functions.

The utility functions get_current_schema and set_current_schema (see above) are especially useful for users who prefer not to manage the search_path manually.