Use ST_ImportOSGB to import OSGB files from OSS - PolarDB

Imports an oblique photography project in OSGB format from an Object Storage Service (OSS) bucket into a database.

Syntax

boolean ST_ImportOSGB(cstring table_name, cstring url, cstring options default '{}');

Return type: boolean

Limitations

Before importing, note the following requirements:

Only OSGB files are supported.
A file named metadata.xml must exist in the specified directory. It can be in the root directory or a subdirectory.
Import the complete OSGB project. Partial imports cause data loss.
For large imports via a client connection, increase the client timeout period to prevent import failures.

Parameters

Parameter	Description
`table_name`	The name of the primary table. This value is also used as the prefix for the tile table.
`url`	The path to the OSGB project directory in an OSS bucket. To ensure data access, make sure that the cluster and OSS are in the same region and that you use an internal endpoint for access. For more information, see Object storage service paths.
`options`	A JSON object that controls import behavior. Default: `'{}'`. See Options for available fields.

Options

Pass import options as a JSON string. All fields are optional.

Data write

Field	Type	Default	Description
`schema`	String	`public`	The schema of the destination table.
`parallel`	Integer	`1`	The degree of parallelism for the import. Values less than `1` are ignored. Keep this value below `16` to avoid excessive memory consumption.
`batch_size`	Integer	`10000`	The number of rows inserted per SQL statement. Valid values: `1` to `2147483647`.

Spatial reference

Field	Type	Default	Description
`srid`	Integer	—	The spatial reference identifier (SRID) of the data. When specified, overrides the SRID read from `ModelMetadata/SRS` in `metadata.xml`.

Project identity

Field	Type	Default	Description
`project`	String	—	The project name. Passed to the `project_name` field of every imported record.

Advanced

Field	Type	Default	Description
`gateway`	Boolean	`false`	Enables gateway mode. In gateway mode, data is stored as references rather than inline. The database fetches tile data from OSS at query time, which greatly reduces storage usage but may slow down data access.
`tileset_prefix`	String	—	A prefix for the generated 3D Tiles address. Use this to configure a custom reverse proxy path.

Output tables

A successful import creates two tables in the database.

Primary table

Table name: [table_name]

Stores metadata for the oblique photography project.

Field	Data type	Description
`project_id`	uuid	Project ID. Automatically generated.
`project_name`	text	Project name. Populated from the `project` option. Empty if not specified.
`srid`	integer	Spatial reference identifier. Read from `ModelMetadata/SRS` in `metadata.xml`. Overridden by the `srid` option if specified.
`ref_point`	geometry	The 3D anchor point of the project. Read from `ModelMetadata/SSRSOrigin` in `metadata.xml`. Geometry type: `Point3DZ`.
`extent`	geometry	Approximate bounding area of the project. Geometry type: `PolygonZ`.
`aux`	text	Full content of `metadata.xml`.
`tiletable`	varchar(64)	Name of the associated tile table.

Tile table

Table name: [table_name]_tile

Stores all tile data for the project.

Field	Data type	Description
`project_id`	uuid	Project ID. Same as the primary table.
`project_name`	text	Project name. Same as the primary table.
`uid`	uuid	Tile ID. Automatically generated.
`lod`	integer	Level of Detail (LOD) of the tile. Parsed from the file name (for example, `Tile_L14_0.osgb`). Set to `0` if the LOD cannot be determined.
`precision`	float8	Estimated tile precision, used for 3D Tiles export.
`parent`	uuid	ID of the parent tile. Empty for root tiles.
`children`	uuid[]	IDs of all child tiles. Empty if no children exist.
`aux`	jsonb	Tile metadata. Contains the relative file path of the tile under the `file_name` node.
`tile`	scene	Tile entity.

Examples

Standard import

SELECT ST_ImportOSGB(
    'test_osgb',
    'OSS://<ak>:<ak_secret>@oss-cn-beijing-internal.aliyuncs.com/mybucket/path_to_oblique_project/'
);

Returns t on success.

Parallel import

When parallel is greater than 1, automatic table creation is disabled. Create the primary table and tile table manually before running the import.

-- Create the primary table
CREATE TABLE IF NOT EXISTS test_osgb(
    project_id uuid PRIMARY KEY,
    project_name text,
    srid integer,
    ref_point geometry,
    extent geometry,
    aux text,
    tiletable varchar(64) NOT NULL
);

-- Create the tile table
CREATE TABLE IF NOT EXISTS test_osgb_tile(
    project_id uuid NOT NULL,
    project_name text,
    uid uuid NOT NULL,
    lod integer,
    precision float8,
    parent uuid,
    children uuid[],
    aux jsonb,
    tile scene NOT NULL,
    PRIMARY KEY(project_id, uid)
);

-- Run the import with parallelism set to 4
SELECT ST_ImportOSGB(
    'test_osgb',
    'OSS://<ak>:<ak_secret>@oss-cn-beijing-internal.aliyuncs.com/mybucket/path_to_oblique_project/',
    '{"parallel": 4}'
);

Returns t on success.