3.5.8. CITYDB_DELETE

The package CITYDB_DELETE consists of several functions that facilitate to delete single and multiple city objects. Each function automatically takes care of integrity constraints between relations in the database. The package is meant as low-level API providing a delete function for each relation (except for linking tables) – from a single polygon in the table SURFACE_GEOMETRY (del_surface_geometry) up to a complete CityObject (del_cityobject) or even a whole CityObjectGroup (del_cityobjectgroup). This should help users to develop more complex delete operations on top of these low-level functions without re-implementing their functionality.

Most of the stored procedures take the primary key ID value of the entry to be deleted as input parameter and return the ID value if the entry has been successfully removed. So, if NULL is returned, the entry is either already gone or the deletion did not work due to an error. Nearly every delete function comes with a pendant to delete multiple entries at once. These alternative functions take an array of ID values as input and return an array of successfully deleted entries. For PostgreSQL, the array is unrolled inside the functions as PL/pgSQL can return a SET OF INTEGER values.

In order to illustrate the low-level approach of this package, assume a user wants to delete a building feature together with all its nested sub features. For this purpose, the user calls the del_building (or del_cityobject) function, which internally leads to subsequent calls to the following stored procedures:

  • del_building for the building and its dependent building parts (recursive call)

  • del_thematic_surface for dependent boundary surfaces of the building (nested call of del_opening for dependent openings of the boundary surfaces)

  • del_building_installation for dependent outer installations of the building (nested call of del_thematic_surface for boundary surfaces of the installations)

  • del_room for dependent rooms of the building (nested call of del_thematic_surface for interior boundary surfaces, del_building_installation for interior installation and del_building_furniture for furniture within the room)

  • del_address for dependent addresses that are not referenced by other buildings and bridges

  • del_implicit_geometry for each prototype geometry of a nested feature, e.g. Openings, BuildingInstallation

  • del_surface_geometry for deleting the geometry of the building and its nested features

  • del_cityobject to remove the entry in the CITYOBJECT table that corresponds to the deleted building and the deleted child features (also deletes generic attributes, external references, appearances, etc.)

Note, that global Appearances with no direct reference to a CityObject are not deleted during such a deletion process. Therefore, the method cleanup_appearances should be executed afterwards, to remove all Appearance information (incl. entries in tables APPEAR_TO_SURFACE_DATA, SURFACE_DATA and TEX_IMAGE). Like with the stored procedures from the CITYDB_OBJCLASS package, the delete functions are part of the ‘citydb’ schema and not ‘citydb_pkg’. This is not only because of a better performance without dynamic SQL. It is mandatory as the code for the delete functions is generated automatically based on the foreign keys.

The del_ prefix is used to not exceed 30 characters in Oracle. As explained in Section 3.4, managing different CityGML ADEs in different schema would require different delete scripts for each schema. A simple code block to delete objects based on a query result can look like this:

Oracle:

-- single version
DECLARE
  deleted_id NUMBER;
  dummy_ids ID_ARRAY := ID_ARRAY();
BEGIN
  FOR rec IN (SELECT * FROM cityobject WHERE ...) LOOP
    deleted_id := citydb_delete.del_cityobject(rec.id);
  END LOOP;
  dummy_ids := citydb_delete.cleanup_appearances;
END;
-- array version
DECLARE
  pids ID_ARRAY := ID_ARRAY();
  deleted_ids ID_ARRAY := ID_ARRAY();
  dummy_ids ID_ARRAY := ID_ARRAY();
BEGIN
  SELECT id BULK COLLECT INTO pids
    FROM cityobject WHERE ...;

  deleted_ids := citydb_delete.del_cityobject(pids);
  dummy_ids := citydb_delete.cleanup_appearances;
END;

PostgreSQL:

-- single version
SELECT citydb.del_cityobject(id) FROM cityobject WHERE ... ;
SELECT citydb.cleanup_appearances();

-- array version
SELECT citydb.del_cityobject(array_agg(id))
  FROM cityobject WHERE ... ;
SELECT citydb.cleanup_appearances();

Which delete function to use depends on the ratio between the number of entries to be deleted and the total count of objects in the database. One array delete executes each necessary query only once compared to numerous single deletes and can be faster. However, if the array is huge and covers a great portion of the table (say 20% of all rows) it might be faster to go for the single version instead or batches of smaller arrays. Nested features are deleted with arrays anyway.

The previously available CITYDB_DELETE_BY_LINEAGE package has been included into the CITYDB_DELETE package and reduced to only one function. It allows to delete multiple city objects that share a common value in the LINEAGE column of the CITYOBJECT table. The procedure cleanup_schema provides a convenient way to reset an entire 3DCityDB instance under both Oracle and PostgreSQL. After invoking this procedure, all entries from all tables are deleted and all sequences are reset.

The following table only lists functions that differ from each other where del_cityobject stands for the general layout of a delete function:

Table 3.25 API of the CITYDB_DELETE package for Oracle
Function
Return Type
Explanation
cleanup_appearances
(only_global)
ID_ARRAY
Removes unreferenced Appearences incl.
SurfaceData and textures and returns an array of
their IDs. Pass 1 (default) to only delete global
appearances, or 0 to include local appearances
cleanup_schema
(schema_name)
void
Truncates most tables and resets sequences in a
given 3D City Database schema
cleanup_table (table_name)
ID_ARRAY
Removes entries in given table which are not
referenced by any other entities
del_cityobject (NUMBER)
NUMBER
Removes the CityObject with the given ID incl.
all references to other tables. The ID value
is returned on success
del_cityobject (ID_ARRAY)
ID_ARRAY
Removes CityObjects with the given IDs incl.
all references to other tables. An array of
IDs of successfully deleted objects is returned
del_cityobjects_by_lineage
(lineage_value)
ID_ARRAY
Removes all CityObjects on behalf of a LINEAGE
value and returns an array of their IDs
Table 3.26 API of the CITYDB_DELETE package for PostgreSQL
Function
Return Type
Explanation
cleanup_appearances
(only_global)
SET OF INTEGER
Removes unreferenced Appearences incl.
SurfaceData and textures and returns an array of
their IDs. Pass 1 (default) to only delete global
appearances, or 0 to include local appearances
cleanup_schema
(schema_name)
void
Truncates most tables and resets sequences in a
given 3D City Database schema
cleanup_table (table_name)
SET OF INTEGER
Removes entries in given table which are not
referenced by any other entities
del_cityobject (INTEGER)
INTEGER
Removes the CityObject with the given ID incl.
all references to other tables. The ID value
is returned on success
del_cityobject ((INTEGER[ ])
SET OF INTEGER
Removes CityObjects with the given IDs incl.
all references to other tables. An array of
IDs of successfully deleted objects is returned
del_cityobjects_by_lineage
(lineage_value)
SET OF INTEGER
Removes all CityObjects on behalf of a LINEAGE
value and returns an array of their IDs