pg2b3dm

pg2b3dm is a tool designed to convert 3D geometries stored in PostGIS into the 3D Tiles format. The resulting tilesets can be visualized in various 3D clients such as Cesium JS, Cesium for Unreal/Unity3D/Omniverse/Godot, QGIS, ArcGIS Pro, and ArcGIS Maps SDK for JavaScript.

Key features include:

• Support for both Explicit and Implicit tiling.
• QUADTREE and OCTREE tiling schemas.
• 3D Tiles extensions: EXT_Mesh_Features and EXT_Structural_Metadata.
• Valid glTF 2.0 files with PBR shading support.
• Automatic 3DCityDB v5 texture support.
• Support for global coordinates (EPSG:4978) or local cartesian coordinates.
• Triangulation of input geometries like LineStrings, Polygons, and PolyhedralSurfaces.

The tool supports different tiling strategies for organizing 3D tiles:

• QUADTREE: The default tiling method.
• OCTREE: Supported via the subdivision parameter. This allows for more complex spatial partitioning.
• Explicit Tiling: Allows for manual control over tiling. When using explicit tiling, the tool can support multiple subtree files and specific subdivision parameters.

Use the subdivision parameter to switch between QUADTREE and OCTREE.

When exporting from 3DCityDB v5, pg2b3dm automatically detects texture data and applies it per tile.

Required Data Structure: For automatic detection, the input (table or view) must provide access to the following tables/columns:

• citydb.surface_data_mapping.texture_mapping (texture coordinates)
• citydb.surface_data.tex_image_id
• citydb.tex_image.image_data (image bytes)

Priority Rule: If both textures and shaders are available for the same tile, textures take priority. Tiles without texture data will fall back to the existing shader or default behavior.

The -q, --query flag allows you to pass a string that will be appended to the WHERE clause of all queries generated by the tool. This is useful for filtering specific attributes or spatial subsets.

Attribute Query Example:

-q "ogc_fid=118768"

Spatial Query Example:

-q "ST_Intersects(wkb_geometry, 'SRID=4326;POLYGON((-75.56996406 39.207228824,-75.56996406 39.2074420320001,-75.5696300339999 39.2074420320001,-75.5696300339999 39.207228824,-75.56996406 39.207228824))'::geometry)"

Note: When using large tables, ensure you have appropriate indexes on the columns used in your query.

The --subdivision parameter determines how the 3D space is partitioned.

• QUADTREE (Default): Best for geometries distributed in a flat area, such as buildings in a city. It partitions space along the X and Y axes.
• OCTREE: Best for geometries distributed in a cube-like area (3D volume). It partitions space along X, Y, and Z axes.

Important Limitations:

• Most features are supported in OCTREE mode, but LOD (Level of Detail) support is not available when using OCTREE.
• The --keep_projection parameter is only implemented for implicit tiling. Using --keep_projection with explicit tiling will cause the program to exit with an error.

Implicit Tiling (3D Tiles 1.1) reduces the size of the tileset.json file by not explicitly listing every tile. Instead, it uses subtree files (*.subtree) located in a subtrees folder.

Key details:

• Activation: Use the --use_implicit_tiling parameter (defaults to true).
• Client Support: Currently only supported in the CesiumJS client.
• Limitations:
  • Does not support octrees (only quadtrees).
  • Does not support multiple contents per tile.
  • Does not support implicit tiling metadata.
  • The -l or --lodcolumn parameter is ignored when implicit tiling is active.
  • LOD Support: The LOD function is not available when implicit tiling is used.
• Geometric Error Calculation: The root tileset.json uses the maximum geometric error. For child tiles, the error is calculated as: parent_geometric_error / geometric_error_factor.

To ensure efficient processing of large datasets, follow these best practices:

Spatial Indexing

For large datasets, create a spatial index on the geometry column to avoid performance warnings and slow queries:

CREATE INDEX ON the_table USING gist(st_centroid(st_envelope(geom_triangle)));

Line Geometry Radius

By default, line geometries are converted into 3D tubes with a radius of 1 meter. If you want variable radii, specify a column containing the radius values using the --radiuscolumn option.

Example of adding a random radius column in PostgreSQL:

ALTER TABLE delaware_buildings ADD COLUMN radius real;
UPDATE delaware_buildings SET radius = 0.5 + random() * (1.5 - 0.5);

Once CityGML data is imported into 3DCityDB, use the pg2b3dm tool to export it as 3D Tiles. This produces a tileset.json file, subtree files, and binary glTF 2.0 (.glb) tiles.

Basic Conversion Command:

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

Arguments:

• --connection: The PostgreSQL connection string.
• -t: The target table (e.g., citydb.geometry_data).
• -c: The column containing geometry data.
• --attributecolumns: Columns containing attribute data to include in the tiles.

You can style 3D Tiles directly in the browser using the CesiumJS API and the 3D Tiles Styling Language. This allows you to apply visual properties based on feature attributes.

Note: To use attributes in your styles, you must include them during the 3D Tiles creation process using the -a <attribute_name> flag.

Example: Styling buildings by year (bouwjaar)

var buildings = new Cesium.Cesium3DTileset({
    url : './buildings/tileset.json'
});

// Apply conditional colors based on the 'bouwjaar' attribute
buildings.style = new Cesium.Cesium3DTileStyle({
  color: {
    conditions: [
      ["${feature['bouwjaar']} <= 1700", "color('#430719')"],
      ["${feature['bouwjaar']} > 1700", "color('#740320')"],
    ]
  }
});

// Filter visibility based on a query
buildings.style.show = "${feature['bouwjaar']} > 1975";

QGIS (version 3.34 and later) supports 3D Tiles.

Important Compatibility Note: Because 3D Tiles 1.1 features are not yet supported in QGIS, you must use specific parameters when generating your tileset to ensure compatibility. Use the following flags during the creation process:

• --create_gltf false
• --use_implicit_tiling false

--create_gltf false --use_implicit_tiling false