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

  1. 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;
  2. Register a spatial data file in the shapefile format as a foreign table.
    1. Register the shapefile as a foreign table. 
      SELECT ST_RegForeignTables('OSS://<ak_id>:<ak_secret>@<endpoint>/path/poly.shp');
      Note The ak_id and ak_secret parameters 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. The Endpoint parameter specifies the ID of the region where your OSS bucket resides. 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 endpoint of your RDS instance. For more information, see OSS endpoints.
    2. 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;
  3. Query data from the foreign table. 
    SELECT fid, ST_AsText(geom), name, age, height FROM poly WHERE fid = 1;
------------
   1 | POLYGON((5 0,0 0,0 10,5 10,5 0)) | ZhangShan |  35 |   1.84
  4. 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;
  5. Optional. Delete the Ganos_FDW extension. 
    DROP EXTENSION Ganos_FDW CASCADE;