A foreign data wrapper (FDW) is an extension of PostgreSQL. You can use an FDW to access external data. External data sources include the data from the databases in your ApsaraDB RDS instance and the data from other RDS instances. In GanosBase, an FDW supports unified access to various spatial data and can automatically map geometric spatial data types to the Geometry type. You can use an FDW to access and query data from foreign tables and from the tables of your RDS instance in a unified manner.
Procedure
Create the Ganos_FDW extension.
You can use one of the following methods to create the Ganos_FDW extension:
Use the CASCADE keyword to create the Ganos_FDW extension.
CREATE EXTENSION ganos_fdw WITH schema public CASCADE;
NoteWe recommend that you install the extension in public mode to avoid permissions issues.
Directly create the Ganos_FDW extension.
CREATE EXTENSION ganos_spatialref; CREATE EXTENSION ganos_geometry; CREATE EXTENSION ganos_fdw;
Register a spatial data file in the shapefile format as a foreign table.
Register the shapefile as a foreign table.
SELECT ST_RegForeignTables('OSS://<access_id>:<secrect_key>@[<Endpoint>]/<bucket>/path_to/filet');
NoteThe following list describes the parameters:
ak_id and ak_secret specify the AccessKey ID and AccessKey secret of the AccessKey pair that is used to access your Object Storage Service (OSS) bucket. For more information, see Obtain an AccessKey pair.
To ensure data accessibility, make sure that your RDS instance and the OSS bucket reside in the same region and are connected by using the internal OSS endpoint. For more information, see OSS domain names.
/<bucket>/path_to/file
specifies the directory of the file in the OSS bucket. If you use a file in the shapefile format, the file must contain at least three types of the following files:.shp,. shx, and. dbf
. The files must be uploaded to the same folder of an OSS bucket.
Query the foreign table by using the
information_schema.foreign_tables
view.SELECT foreign_table_name FROM information_schema.foreign_tables ORDER BY foreign_table_name ASC;
Query data from the foreign table.
SELECT fid, ST_AsText(geom), name, age, height FROM poly WHERE fid = 1;
Sample output:
fid | ST_AsText(geom) | name | age | height -------------------------------------------------------------------- 1 | POLYGON((5 0,0 0,0 10,5 10,5 0)) | ZhangShan | 35 | 1.84
Import the data of the foreign table into a table that is created in your RDS instance.
If no table is created in your RDS instance, run the following command to create a table and insert the data into the table:
CREATE TABLE poly_db AS SELECT * FROM poly;
If a table is created in your RDS instance, you can use one of the following methods to import the data into the table:
Use the INSERT SELECT statement.
INSERT INTO poly_db SELECT * FROM poly;
Use the IMPORT FOREIGN SCHEMA statement.
CREATE SCHEMA imp; IMPORT FOREIGN SCHEMA ganos_fdw FROM SERVER ganos_fdw_server INTO imp;
Optional. Delete the Ganos_FDW extension.
DROP EXTENSION Ganos_FDW CASCADE;