ST_ImportOSGB - PolarDB - Alibaba Cloud Documentation Center

This topic describes the ST_ImportOSG function. This function imports an object storage-based oblique photography project in the OSGB format to a database.

Syntax

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

Parameters

Parameter	Description
table_name	The name of the osg table and the prefix of its tile table.
url	The path of the file in an Object Storage Service (OSS) bucket. Note 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	The fields for the import. schema: the schema name of the destination table. Data type: String. Default value: public. parallel: the degree of parallelism. Increasing the parallelism accelerates data import, but may consume a large amount of memory. You can specify an appropriate value based on the actual data or the database running status. We recommend that you set this field to a value less than 16. If the value is less than 1, the value is ignored. Data type: Integer. Default value: 1. batch_size: the size of the SQL statements that are executed at a time. Data type: Integer. Valid values: 1 to 2147483647. Default value: 10000. project: the name of the project. The value of this field is passed to the `project_name` field of each record. Data type: String. srid: the spatial reference identifier (SRID) of the data. Data type: Integer. gateway: specifies whether to import data in gateway mode. If you use the gateway mode, data is stored in the database as references. When the data is called, the data is obtained from the reference address in real time. This greatly reduces the storage usage of the database but may cause slow data calls. Data type: Boolean. Default value: false. tileset_prefix: the prefix of the generated 3D Tiles address. If you specify this prefix, you can customize the reverse proxy in a convenient manner. Data type: String.

Description

This function imports an object storage-based OSGB file to a database.

Import limits:

Only OSGB files are supported.
A file named metadata.xml must exist in the specified directory. The file can be located in the root directory or a subdirectory.
You must import the complete OSGB file. Otherwise, data loss occurs.
If you use a client to connect to the database and import a large amount of OSGB files, adjust the client timeout period to avoid import failure due to timeout errors.

After the import is successful, two tables are generated in the database:

Primary table

Table name format: [table_name]

Description: This table stores the metadata of the oblique photography project.

Table schema:

Field	Data type	Description
project_id	uuid	The ID of the project, which is automatically generated.
project_name	text	The name of the project. If you do not specify a value for the `project_name` field, the column is empty. In most cases, the project is used to store the tables of multiple sub-projects.
srid	integer	The SRID of the project. By default, the value is obtained from the `ModelMetadata/SRS` field in the `metadata.xml` file. If you specify the `srid field in the options parameter`, the specified value is preferentially used.
ref_point	geometry	The 3D point at which the project is anchored. By default, the value is obtained from the `ModelMetadata/SSRSOrigin` field in the `metadata.xml` file. The value is of the `Point3DZ` type.
extent	geometry	The approximate scope of the project. The value is of the `PolygonZ` type.
aux	text	The metadata. The value is the content of the `metadata.xml` file.
tiletable	varchar(64)	The name of the tile table.

Tile table

Table name format: [table_name]_tile

Description: This table stores all tile data of the project.

Table schema:

Field	Data type	Description
project_id	uuid	The ID of the project, which is the same as the project ID for the primary table.
project_name	text	The name of the project, which is the same as project name for the primary table.
uid	uuid	The ID of the tile, which is automatically generated.
lod	integer	The Level of Detail (LOD) of the tile. You can determine the value based on the file name, such as `Tile_L14_0.osgb`. If the LOD cannot be determined based on the file name, the value 0 is used.
precision	float8	The estimated tile precision, which is used to export 3D Tiles.
parent	uuid	The ID of the parent tile of the tile. If the root node is used, the value is empty.
children	uuid[]	The IDs of all sub-tiles of the tile. If no sub-tiles exist, the value is empty.
aux	jsonb	The metadata of the tile. The value is the file name of the relative root directory of the tile. You can obtain the value from the `file_name` node.
tile	scene	The entity of the tile.

Important

If the degree of parallelism is not 1, automatic table creation is not supported. You must execute the following statement to manually create a table in advance:

# For this example, create a table named test_osgb in the default schema.
# Create a 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 a 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)
);

Example

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

---------
t

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

---------
t