How to effiecently union multiple 3d polygones and cast them to another table?

Question

I am working with a large amount of CityGML 3d building data (LoD2) and imported the data using the 3D City Database (and its Importer/Exporter tool) (see https://www.3dcitydb.org/3dcitydb/) which creates a new schema in the database and uses java routines to translate the XML-style geodata to PostGIS compatible database input.

The information (spatial and non spatial) for each building is then written to different tables, where "building" table holds building meta data such as building function or street name and "surface_geometry" table holds solid and 3d surface geometries of all buildings. With columns "id", "parent_id" and "root_id" used as PKs and FKs to ensure the elements of one table can be matched to one or more elements of the other table.

Since I need 2d building footprints (or bird view polygones) for most of my analyses, I created queries to make surfaces 2d with st_force2d() before st_union()ing all 2d surface of the same building with group by on the available FK.

Are there any ideas how to improve the following code to effiecntly process a huge dataset?

The queries are

CREATE TABLE citydb.building_geom_tmp AS
SELECT a.*, b.bldg_footprint AS geom
FROM Citydb.building AS a
LEFT JOIN (
        SELECT *, ST_Force2D(geometry) AS bldg_footprint FROM citydb.surface_geometry
        ) AS b
ON a.lod2_solid_id = b.root_id;

DROP TABLE IF EXISTS citydb.building_geom ;
CREATE TABLE citydb.building_geom AS
SELECT a.*, b.geom
FROM citydb.building AS a
LEFT JOIN (
        SELECT id, ST_Union(geom) AS geom
        FROM citydb.building_geom_tmp  AS c
        GROUP BY c.id
        ) AS b
ON a.id = b.id;

DROP TABLE IF EXISTS citydb.building_geom_tmp

I am working on a VM with Win 10 and SELECT version() and SELECT postgis_full_version() give

PostgreSQL 13.2, compiled by Visual C++ build 1914, 64-bit

and

POSTGIS="3.1.1 3.1.1" [EXTENSION] PGSQL="130" GEOS="3.9.1-CAPI-1.14.1" PROJ="7.1.1" GDAL="GDAL 3.2.1, released 2020/12/29" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" RASTER

respectively. The 3dcityDB is 4.1.0 on the virtual machine with postgres/postgis-versions shown above.

Sample CityGML LoD2 data could be downloaded from the German Federal Agency for Cartography and Geodesy (here).

In addition I asked the 3DCityDB developers for ideas in ther github repo here.

Thanks in advance for any ideas and best regards
André

Answer 1

Got an answer from the developers directly on Github: https://github.com/3dcitydb/3dcitydb/issues/73