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 RDS instance and the data from other RDS instances. In Ganos, 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:
Directly create the Ganos_FDW extension.
CREATE EXTENSION ganos_spatialref; CREATE EXTENSION ganos_geometry; CREATE EXTENSION ganos_fdw;
Use the CASCADE keyword to create the Ganos_FDW extension.
CREATE EXTENSION Ganos_FDW CASCADE;
Register a spatial data file in the shapefile format as a foreign table.
Register the shapefile as a foreign table.
SELECT ST_RegForeignTables('OSS://<ak_id>:<ak_secret>@<endpoint>/path/poly.shp');
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.
endpoint specifies the endpoint of the OSS bucket. To ensure data accessibility, make sure that your RDS instance and your OSS bucket reside in the same region and are connected by using the internal OSS endpoint. For more information, see OSS endpoints.
/path/poly.shp
specifies the directory of the .shp file in your 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;