pg2b3dm

Converting CityGML to 3D Tiles using 3DCityDB v5

Overview

3DCityDB v5 is an open-source database designed for storing and managing semantic 3D city models based on the CityGML 3.0 standard. It runs on PostgreSQL with PostGIS and organizes city data into about 17 core tables that handle geometry, metadata, appearances, and relationships.
This guide outlines how to install 3DCityDB v5, import CityGML data, export it as 3D Tiles, and visualize the results.

See also https://tum-gis.github.io/citydb-3dtiler/ for support creating 3D Tiles using pg2b3dm in 3DCityDB.

1. Installation

3DCityDB v5 can be quickly deployed using Docker:

docker run -d -p 5440:5432 -it -e POSTGRES_PASSWORD=postgres -e PROJ_NETWORK=ON -e SRID=7415 3dcitydb/3dcitydb-pg

Explanation:

After launching, the database schema is automatically created with tables for features (aka. city objects), geometry data, attributes, and appearances.

2. Importing CityGML Data

Download a sample CityGML file, such as the Den Haag Archipelbuurt 3D model (45MB) from https://ckan.dataplatform.nl/dataset/3d-stadsmodel-den-haag-2021-citygml/resource/be8d3a16-50f3-415f-a8bd-55d24c9d8cdc.

Install command line tool ‘citydb’ - see https://github.com/3dcitydb/citydb-tool

Example command for importing a CityGML file:

citydb import citygml -H localhost -d postgres -u postgres -p postgres --db-port 5440 den_haag_3d_archipelbuurt.gml

In a PostgreSQL client (like PGAdmin/ DBeaver/psql) open database connection to localhost, port 5440, user postgres.

psql -h localhost -p 5440 -U postgres -d postgres

In the PostgreSQL client create a spatial index:

CREATE INDEX ON citydb.geometry_data USING gist(st_centroid(st_envelope(geometry)))

Notes:

3. Converting to 3D Tiles

Once the data is imported, convert the data into 3D Tiles using the pg2b3dm tool:

pg2b3dm --connection "Host=localhost;Port=5440;Username=postgres;Database=postgres;CommandTimeOut=0" -t citydb.geometry_data -c geometry --attributecolumns geometry_properties

Result:

4. Visualization

The resulting 3D Tiles can be viewed in any Cesium-compatible viewer -make sure to load the buildings tileset and a terrain.
When loaded, Cesium displays buildings and terrain data.
Optional styling can be added to control building colors, materials, or feature visibility.

3D Tiles Visualization of Den Haag

5. Advanced visualization with shaders

To enhance the visual appearance of the 3D Tiles, we can apply shaders when generating the tiles. For example, use different colors per CityGML class (like RoofSurface, WallSurface, OuterfloorSurface).

See materials_for_features.csv for an example mapping of CityGML feature classes to material colors.

Import the materials mapping into the database:

CREATE TABLE citydb.materials_for_features (
    namespace_of_classname TEXT,
    classname TEXT,
    pbr_metallic_roughness_base_color TEXT,
    pbr_metallic_roughness_metallic_roughness TEXT
);

Copy the csv to the database:

\copy materials_for_features FROM 'materials_for_features.csv' WITH (FORMAT csv, HEADER true);

Create a view containing the geometry data along with the corresponding material colors:

CREATE OR REPLACE VIEW citydb.geoms4tiles
AS 
WITH material_data_cte AS (
    SELECT
        mtf.namespace_of_classname,
        mtf.classname,
        JSON_OBJECT(
            'PbrMetallicRoughness' : JSON_OBJECT(
                'BaseColors' :
                    NULLIF(
                        ARRAY[mtf.pbr_metallic_roughness_base_color],
                        '{NULL}'::text[]
                    ),
                'MetallicRoughness' :
                    NULLIF(
                        ARRAY[mtf.pbr_metallic_roughness_metallic_roughness],
                        '{NULL}'::text[]
                    )
                ABSENT ON NULL
                RETURNING json
            )
            ABSENT ON NULL
            RETURNING json
        ) AS material_data
    FROM materials_for_features mtf
)
SELECT 
    ftr.objectid,
    obcl.classname,
    pbr.material_data,
    geometry AS geom
FROM geometry_data gmdt
    LEFT JOIN feature ftr ON ftr.id = gmdt.feature_id
    LEFT JOIN objectclass obcl ON obcl.id = ftr.objectclass_id
    LEFT JOIN namespace ns ON ns.id = obcl.namespace_id
    LEFT JOIN material_data_cte pbr ON pbr.namespace_of_classname = ns.alias AND pbr.classname = obcl.classname
	where ftr.objectid NOT LIKE 'bag%';

Sample resulting JSON in column geoms4tiles.material_data:

{"PbrMetallicRoughness" : {"BaseColors" : ["#FFFF00"]}}

Notes:

Now, generate the 3D Tiles using the new view - adding the shaderscolumn parameter:

pg2b3dm --connection "Host=localhost;Port=5440;Username=postgres;Database=postgres;CommandTimeOut=0" -t citydb.geoms4tiles -c geom --shaderscolumn material_data -a objectid,classname

Result: The exported 3D Tiles will now have different colors based on the CityGML feature classes, enhancing the visualization quality.

3D Tiles Visualization of Den Haag with Shaders

6. Textures in 3DCityDB v5

For 3DCityDB v5 input (citydb.geometry_data or a view carrying geometry_data.id), pg2b3dm now automatically detects texture data and applies textures per tile. Note: Executing texture compression is out of scope for pg2b3dm.

Relevant texture tables and columns:

Example query:

SELECT
    gmdt.id,
    gmdt.geometry,
    gmdt.geometry_properties,
    sdm.texture_mapping,
    ti.image_uri,
    ti.image_data
FROM citydb.geometry_data gmdt
JOIN citydb.surface_data_mapping sdm
    ON gmdt.id = sdm.geometry_data_id
JOIN citydb.surface_data sd
    ON sd.id = sdm.surface_data_id
JOIN citydb.tex_image ti
    ON ti.id = sd.tex_image_id
WHERE sdm.texture_mapping IS NOT NULL
  AND ti.image_data IS NOT NULL;

Priority rule during export:

Sample World Port Center Rotterdam:

image

7. Conclusion

3DCityDB v5 streamlines the process of:

This site is open source. Improve this page.