4.9.5. Delete command

Synopsis

impexp delete [-ahvV] [--ade-extensions=<folder>] [-c=<file>]
              [--delete-log=<file>] [--log-file=<file>]
              [--log-level=<level>] [-m=<mode>] [--pid-file=<file>]
              [--plugins=<folder>] [--use-plugin=<plugin[=true|false]>[,
              <plugin[=true|false]>...]]... [[-g]]
              [[--lineage=<lineage>] [--updating-person=<name>]
              [--reason-for-update=<reason>]] [[-t=<[prefix:]name>[,<
              [prefix:]name>...] [-t=<[prefix:]name>[,<[prefix:]
              name>...]]... [--namespace=<prefix=name>[,
              <prefix=name>...]]...] [[-r=<version>] [-R=<timestamp[,
              timestamp]>]] [-i=<id>[,<id>...] [-i=<id>[,<id>...]]...]
              [--db-id=<id>[,<id>...] [--db-id=<id>[,<id>...]]...]
              [-b=<minx,miny,maxx,maxy[,srid]> [--bbox-mode=<mode>]]
              [[--count=<count>] [--start-index=<index>]] [-s=<select>]
              [-q=<xml>]] [-f=<file>[,<file>...] [-f=<file>[,
              <file>...]]... [-w] [[-C=<type>] [[-n=<name>] [-I=<index>]
              [--[no-]header] [-D=<string>] [-Q=<char>]
              [--quote-escape=<char>] [-M=<char>]
              [--csv-encoding=<encoding>]]]] [[-T=<database>]
              [-H=<host>] [-P=<port>] [-d=<name>] [-S=<schema>]
              [-u=<name>] [-p[=<password>]]] [@<filename>...]

Description

The delete command deletes top-level city objects from the 3D City Database. The city objects can either be physically deleted or just terminated. In the latter case, the objects remain in the database and their terminationDate attribute is set to the time of the delete operation. This way, terminated objects can be easily identified and filtered in, for instance, export operations using a feature version filter.

The delete command supports both thematic and spatial filters to specify the features to be deleted. In addition, you can also use a delete list. Delete lists are CSV files that contain a list of identifiers (and possibly further data). Each city object in the database whose identifier matches an entry in the delete list will be deleted. For example, the import log file created when importing CityGML/CityJSON files into the database (see Section 4.4.6.11) can be used as delete list. This comes in very handy when you want to “rollback” an import process, for instance, because it aborted due to errors.

The top-level city objects affected by the delete operation can optionally be recorded in a separate delete log. The delete log is a comma-separated value (CSV) file that contains the type name, the database ID and the object identifier of each city object as separate records.

A corresponding delete operation is not offered by the graphical user interface.

General options

-m, --delete-mode=<mode>: Specify the delete mode. Allowed values are delete and terminate. Be careful to make the right choice here. When choosing delete, the city objects will be permanently removed from the database. This operation cannot be undone. Terminated objects remain in the database. However, if you only terminate but never delete, your database will grow over time. The default mode is delete.

Note

City objects that are already terminated in the database will not be terminated again when running in terminate mode. Thus, their terminationDate will not be updated. However, terminated city objects can, of course, be deleted from the database.

-v, --preview: This flag allows you to tun the delete command in preview mode. The delete operation will print the number and types of features that would be affected by the delete operation as summary overview to the console.

-a, --auto-commit: The default behavior of the delete operation is to delete all or nothing. An advantage of this approach is that no top-level feature gets deleted in case of errors or if the process is aborted by the user. However, too many deletes in one transaction might cause the transaction to become too large and let the database fail. When enabling the --auto-commit option, every top-level feature is directly deleted/terminated in the database. This prevents the transaction from becoming too large but rollbacks are no longer possible.

--delete-log=<file>: If you want a delete log to be created for all top-level features deleted from the database, provide the path to the delete log file with this option.

-g, --cleanup-global-appearances: Flag to indicate that global appearances should be cleaned up after having deleted the city objects. Global appearances are not automatically deleted because they can reference more than one city object. If this option is set, the delete operation will search the database for global appearances that do not reference any city object anymore. Only these appearances will be removed from the database.

Metadata options

The delete command allows for specifying metadata that is assigned to every city object when terminate is chosen as delete mode. The values are stored in columns of the table CITYOBJECT.

--lineage=<lineage>: Value to store as lineage of the city objects.

--updating-person=<name>: Name of the user responsible for the delete. By default, the name of the database user is chosen.

--reason-for-update=<reason>: Reason for deleting the data.

Query and filter options

The delete command offers additional options to define both thematic and spatial filters that are used to more precisely specify the top-level city objects to be deleted from the 3D City Database.

-t, --type-name=<[prefix:]name>[,<[prefix:]name>...]: Comma-separated list of one or more names of the top-level feature types to be deleted. The type names are case sensitive and shall match one of the official CityGML feature type names or a feature type name from a CityGML ADE. To avoid ambiguities, you can use an optional prefix for each name. The prefix must be associated with the official XML namespace of the feature type. You can either use the official CityGML namespace prefixes listed in Table 4.12. Or you can use the --namespace option to declare your own prefixes.

--namespace=<prefix=name>[,<prefix=name>...]: Used to specify namespaces and their prefixes as comma-separated list of one or more prefix=name pairs. The prefixes can be used in other options such as --type-name.

-r, --feature-version=<version>: Specify the version of the top-level features to use for the delete operation. Allowed values are latest, at, between, terminated, terminated_at and all. When choosing latest, only those features that have not been terminated in the database are deleted, whereas all will delete all features. You can also choose to delete only features that were valid at a given timestamp using at or for a given time range using between. Likewise, terminated will delete all terminated features whereas terminated_at will select features that were terminated at a given timestamp. In all cases, timestamps must be provided using the --feature-version-timestamp option.

-R, --feature-version-timestamp=<timestamp[,timestamp]>: One or two timestamps to be used with the --feature-version option. A timestamp can be given as date in the form YYYY-MM-DD or as date-time specified as YYYY-MM-DDThh:mm:ss[(+|-)hh:mm. The date-time format supports an optional UTC offset. Use one timestamp with the at and terminated_at values and two timestamps separated by comma with the between value of the --feature-version option.

-i, --resource-id=<id>[,<id>...]: Comma-separated list of one or more identifiers. Only top-level features having a matching value for their identifier attribute will be deleted.

--db-id=<id>[,<id>...]: Comma-separated list of one or more database IDs. Only top-level features having a matching primary key for the ID column of the CITYOBJECT table will be deleted.

-b, --bbox=<minx,miny,maxx,maxy[,srid]>: 2D bounding box to use as spatial filter. The bounding box is given by four coordinates that define its lower left and upper right corner. By default, the coordinates are assumed to be in the same CRS that is used by the 3DCityDB instance. Alternatively, you can provide the database srid of the CRS associated with the coordinates as fifth value (e.g. 4326 for WGS84). All values must be separated by commas. The bounding box is evaluated against the GMLID column of the CITYOBJECT table.

--bbox-mode=<mode>: Choose the mode of the bounding box filter. Allowed values are overlaps (default) and within. When set to overlaps, all features overlapping with the bounding box are deleted. Otherwise, features must be within the given bounding box. Can only be used together with the --bbox option.

--count=<count>: Maximum number of top-level features to be deleted.

--start-index=<index>: Index within the result set of all top-level features from which the delete operation shall begin. The start index uses zero-based numbering. Thus, the first top-level feature is assigned the index 0, rather than the index 1.

-s, --sql-select=<select>: Provide an SQL SELECT statement to be used as SQL filter when querying the database. In general, any SELECT statement can be used as long as it returns a list of database IDs of the selected city objects (see Section 4.5.3 for more information). You can also use an @-file to provide the SELECT statement (see Section 4.9.9.3).

-q, --xml-query=<xml>: This option allows you to use a full-fledged XML query expression as filter for the delete operation. Make sure the query expression is valid and adheres to the specification for XML query expressions provided in Section 4.5.8. You can also use an @-file to provide the query expression (see Section 4.9.9.3). This option cannot be used with any other filter option of the delete command.

Delete list options

You can also pass a delete list to the delete command to control which city objects should be deleted. A delete list can be used in addition to or instead of the above filter options.

Delete lists are simple comma-separated values (CSV) files that provide the identifiers of the city objects to be deleted. Each identifier must be put on a separate line (row) of the file, and each line may contain additional values (columns) separated by a delimiter (typically a single reserved character such as comma, semicolon, tab, etc.). The first record may be reserved as header containing a list of column names. Usually, every row has the same sequence of columns. If a line starts with a predefined comment marker (typically a single reserved character such as #), the entire row is ignored and skipped.

Due to their simple structure, delete lists can be easily created with external tools and processes. The following snippet shows an example of a simple delete list that can be used with the delete command. It just provides an identifier per row. The first line is used as header.

GMLID
ID_0815
ID_0816
ID_0817
ID_0818
...

The following options let you define the layout and reserved characters of the delete list you want to use with the delete command. You can provide one or more CSV files as input for the delete list. If more than one file is specified, make sure all of them have the same layout and structure.

-f, --delete-list=<file>[,<file>...]: One or more CSV files to use as delete list for the delete operation.

-w, --delete-list-preview: Use this option to get a preview of the first few lines of the delete list when applying the provided options for parsing and interpreting the delete list. This preview is very helpful to adapt and specify the delimiter character(s), quoting rules, header information, identifier column name or index, etc. The preview is printed to the console. If more than one CSV file has been specified for the delete list, the preview is generated for the first file only.

-n, --id-column-name=<name>: Name of the column that holds the identifier value. Using this option only makes sense if the delete list contains a header line. Otherwise, use the --id-column-index option to specify the column index.

-I, --id-column-index=<index>: Index of the column that holds the identifier value. The first column of a row has the index 1. If this option is omitted, the value of the first column of each row will be used as identifier by default. This option is mutually exclusive with the --id-column-name option (only specify one).

-C, --id-column-type=<type>: Specify the type of the identifier. Allowed values are resource and db. When choosing resource, the identifiers provided in the delete list are interpreted as object identifiers (e.g., gml:id in CityGML) and are therefore matched against the GMLID column of the CITYOBJECT table. In contrast, the identifiers are taken as database IDs and checked against the ID column of the CITYOBJECT table when selecting db instead. The default value is resource.

--[no-]header: Define whether or not the delete list uses a header line. By default, the delete operation assumes that the first line contains header information.

-D, --delimiter=<string>: Specify the delimiter used for separating values in the delete list. By default, a comma , is assumed as delimiter. The provided delimiter may consist of more than one character.

-Q, --quote=<char>: The values in the delete list may be quoted (i.e., enclosed by a reserved character). This option lets you define the character used as quote (default: "). Only single characters are allowed as value.

--quote-escape=<char>: If the delete list contains quoted values, define the character used for escaping embedded quote characters. The default escape character is ". Only single characters are allowed as value.

-M, --comment-marker=<char>: Specify the character used as comment marker in the delete list. Any line starting with this comment marker is interpreted as comment and, thus, will be ignored. The default comment marker is #. Only single characters are allowed as value.

--csv-encoding=<encoding>: Define the encoding of the delete list using a IANA-based character encoding name. UTF-8 is assumed as default encoding.

Database connection options

The following options allow you to define the connection details that shall be used for establishing a connection to the 3D City Database. You can also use environment variables for this purpose (see Section 4.9.8).

-T, --db-type=<database>: Specify the database system used for running the 3DCityDB. Allowed values are postgresql for PostgreSQL/PostGIS databases (default), and oracle for Oracle Spatial databases.

-H, --db-host=<host>: Specify the host name of the machine on which the 3DCityDB database server is running.

-P, --db-port=<port>: Specify the TCP port on which the 3DCityDB database server is listening for connections. The default value is 5432 for PostgreSQL and 1521 for Oracle.

-d, --db-name=<name>: Specify the name of the 3DCityDB database to connect to. When connecting to an Oracle database, provide the database SID or service name as value.

-S, --db-schema=<schema>: Name of the database schema to use when connecting to the 3DCityDB. If not provided, the citydb schema is used for PostgreSQL by default, whereas the schema of the user specified by the option --db-username is used under Oracle.

-u, --db-username=<name>: Connect to the 3DCityDB database server as the user given by name.

-p, --db-password[=<password>]: Specify the password to use when connecting to the 3DCityDB database server. You can either provide the password as value for this option or leave the value empty to be prompted to enter the password on the console before connecting to the database. The password prompt will timeout after 60 seconds if no password is provided. If you skip this option completely, the impexp tool will try to connect to the database without a password. If the database server requires password authentication and a password is not available by other means, the connection attempt will fail in this case.

Examples

$ impexp delete -H localhost -d citydb_v4 -u citydb_user -p my_password

Delete all city objects from the database. The 3DCityDB to connect to is supposed to be running on a PostgreSQL database on the same machine. The connection will be established to the citydb_v4 database with the user citydb_user and the password my_password.

Caution

Be very careful with the above example and only use it in case you are absolutely sure. There will be no confirmation prompt and no undo operation (which is also true for all further examples…). If you really want to clean the entire database, the cleanup_schema database function provided by the 3DCityDB should be your preferred choice simply because it is much faster.

$ impexp delete -H localhost -d citydb_v4 -u citydb_user -p my_password \
                -m terminate -v \
                -t Building -b 13.3508824,52.4799281,13.3578297,52.4862805,4326 \
                --bbox-mode=within

Only terminate Building features within the given bounding box. The affected buildings are kept in database but marked as terminated by setting their terminationDate attribute. The entire process is run in preview mode. Thus, a summary overview of the number of affected feature is printed to the console but nothing will be committed to the database.

$ impexp delete -H localhost -d citydb_v4 -u citydb_user -p my_password \
                -m terminate -g \
                -s "select cityobject_id from cityobject_genericattrib \
                    where attrname='energy_level' and realval < 12" \
                --count 20

Terminate the first 20 top-level city objects satisfying the given SQL SELECT statement. Afterwards, global appearances not referencing a city object anymore will be deleted from the database.

$ impexp delete -H localhost -d citydb_v4 -u citydb_user -p my_password \
                -f imported-features.log -I 2 -C db

Only delete those top-level city objects having a database ID that is contained in the provided delete list imported-features.log. The command uses default values for parsing and interpreting the delete list except for the index of the column that holds the identifier values, which is set to 2, and the type of the identifier, which is set to db.

The above example has been chosen deliberately because it illustrates how to use an import log file created by a CityGML/CityJSON import operation as delete list for the delete command. The general layout and content of an import log file is discussed in Section 4.4.6.11. The snippet below shows an example.

#3D City Database Importer/Exporter, version "4.3.0"
#Database connection: citydb_user@localhost:5432/devel
#Timestamp: 2021-04-19 21:30:40
FEATURE_TYPE,CITYOBJECT_ID,GMLID_IN_FILE,INPUT_FILE
Building,532,BLDG_0003000000106562,C:\data\citygml\berlin\gasometer\tile_0_0\Gasometer.gml
Building,540,BLDG_00030000001065f4,C:\data\citygml\berlin\gasometer\tile_0_0\Gasometer.gml
Building,552,BLDG_0003000000106543,C:\data\citygml\berlin\gasometer\tile_0_0\Gasometer.gml
Building,556,BLDG_0003000a000afa94,C:\data\citygml\berlin\gasometer\tile_0_0\Gasometer.gml
Building,563,BLDG_0003000a0019a1c6,C:\data\citygml\berlin\gasometer\tile_0_0\Gasometer.gml
Building,533,BLDG_0003000000106686,C:\data\citygml\berlin\gasometer\tile_0_0\Gasometer.gml
Building,542,BLDG_0003000a000afacf,C:\data\citygml\berlin\gasometer\tile_0_0\Gasometer.gml
Building,548,BLDG_0003000a000afb3b,C:\data\citygml\berlin\gasometer\tile_0_0\Gasometer.gml
Building,560,BLDG_0003000e009b5355,C:\data\citygml\berlin\gasometer\tile_0_0\Gasometer.gml
Building,568,BLDG_0003000b003d0d1b,C:\data\citygml\berlin\gasometer\tile_0_0\Gasometer.gml
#Import successfully finished.

The import log uses a comma , as delimiter. Lines 1-3 and line 15 are comments starting with the comment marker # and should therefore be ignored. Line 4 is used as header to provide column names for the subsequent rows. The actual content is therefore provided in lines 5-14. Each row consists of four columns, and the second column CITYOBJECT_ID contains the database ID of the imported top-level features.

As you can see from the above example, the default CSV options of the delete command are already suited to correctly identify the comments, the header, and the separate columns of the import log file. As mentioned above, we only have to specify that we want to use database IDs as identifiers and provide the correct column index on the command line.

Note

The third column GMLID_IN_FILE of the import log file holds the object identifier of the imported city objects. To use this object identifier in the delete process, you just have to use -I 3 to specify the third column and omit the additional -C db option. However, be careful when using the GMLID_IN_FILE values because the object identifiers in the database might be different due to import preference settings.