Import OSGB files from OSS with ST_ImportOSGB - PolarDB

Imports an oblique photography project in OSGB format from Object Storage Service (OSS) into a PolarDB database. Returns true on success.

Syntax

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

Parameters

Parameter	Type	Description
`table_name`	cstring	The name of the primary OSG table and the prefix of its tile table.
`url`	cstring	The OSS path to the OSGB project directory. The cluster and OSS bucket must be in the same region. Use an internal endpoint for access. See Object storage service paths.
`options`	cstring	A JSON object of import options. Default: `'{}'`. See Options below.

Options

Field	Type	Default	Required	Description
`schema`	String	`public`	No	The schema of the destination table.
`parallel`	Integer	`1`	No	The degree of parallelism. Set to a value less than 16. Values less than 1 are ignored. Higher values speed up import but increase memory usage. When set to any value other than `1`, you must create the tables manually before importing.
`batch_size`	Integer	`10000`	No	The number of SQL statements executed per batch. Valid values: 1–2,147,483,647.
`project`	String	—	No	The project name. Passed to the `project_name` field of each record.
`srid`	Integer	—	No	The spatial reference identifier (SRID) of the data. Overrides the SRID read from `metadata.xml`.
`gateway`	Boolean	`false`	No	Specifies whether to import data in gateway mode. When `true`, records are stored as references and fetched from OSS at query time, which reduces database storage usage but may slow queries. When `false`, tile data is stored directly in the database.
`tileset_prefix`	String	—	No	The prefix for generated 3D Tiles addresses. Use this to configure a custom reverse proxy.

Limitations

Only OSGB files are supported.
A metadata.xml file 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 through a client connection, increase the client timeout to prevent import failure.

Output tables

A successful import creates two tables in the database.

Primary table — named [table_name]

Stores metadata for the oblique photography project.

Field	Data type	Description
`project_id`	uuid	The project ID, automatically generated.
`project_name`	text	The project name. Set by the `project` option. Empty if not specified.
`srid`	integer	The SRID of the project. Read from `ModelMetadata/SRS` in `metadata.xml`. The `srid` option takes priority if specified.
`ref_point`	geometry	The 3D anchor point of the project. Read from `ModelMetadata/SSRSOrigin` in `metadata.xml`. Type: `Point3DZ`.
`extent`	geometry	The approximate spatial extent of the project. Type: `PolygonZ`.
`aux`	text	The full content of `metadata.xml`.
`tiletable`	varchar(64)	The name of the tile table.

Tile table — named [table_name]_tile

Stores all tile data for the project.

Field	Data type	Description
`project_id`	uuid	The project ID. Same as in the primary table.
`project_name`	text	The project name. Same as in the primary table.
`uid`	uuid	The tile ID, automatically generated.
`lod`	integer	The Level of Detail (LOD) of the tile. Derived from the file name (for example, `Tile_L14_0.osgb`). Defaults to `0` if the LOD cannot be determined.
`precision`	float8	The estimated tile precision, used when exporting to 3D Tiles.
`parent`	uuid	The ID of the parent tile. Empty for root tiles.
`children`	uuid[]	The IDs of all child tiles. Empty if none exist.
`aux`	jsonb	Tile metadata. Contains the file name relative to the project root directory, accessible from the `file_name` node.
`tile`	scene	The tile entity.

Usage notes

Parallel import requires manual table creation. When parallel is set to any value other than 1, automatic table creation is disabled. Create the primary table and tile table 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)
);

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

Parallel import

-- parallel: 4 enables concurrent processing.
-- Keep this value below 16 to avoid excessive memory usage.
-- When parallel != 1, create the tables manually before running this statement.
SELECT ST_ImportOSGB(
    'test_osgb',
    'OSS://<ak>:<ak_secret>@oss-cn-beijing-internal.aliyuncs.com/mybucket/path_to_oblique_project/',
    '{"parallel": 4}'
);

Returns: t