List all tables with ogr_fdw ODBC connection

Question

I have been using ogr_fdw to connect PostgreSQL/PostGIS databases to FireBird databases via ODBC connection on Windows machines. The connections have been working great, and are immensely useful for transferring data between the databases  - I am using the foreign data wrapper to keep data synced as we transition from FireBird to PostgreSQL.

FireBird is not spatially enabled. On one of the databases, I had created my own "GEOMETRY_COLUMNS" table in FireBird and used SQL to create views and tables with WKT and WKB geometries. This was partly an experiment, but at the same time shoe-horned some spatial functionality into the FireBird database that we needed.

On all of the other databases, I can use "import foreign schema" to connect to all tables, including non-spatial. However, on this one database I am limited to only the tables in the geometry_columns table. Based on this post Can't connect to foreign SQL Server table from PosgreSQL using OGR_FDW, it seems that I need to set variables to list_all_tables and use_geometry columns. However, the post was for MSSQL and I am using ODBC with FireBird. I suspect these settings tie into GDAL: https://gdal.org/drivers/vector/pg.html. I've been trying to create a foreign server that will ignore the GEOMETRY_COLUMNS table and show all tables, not just spatial ones, but have not been successful.

This is my typical create server SQL:

CREATE SERVER fb_db_svr FOREIGN DATA WRAPPER ogr_fdw OPTIONS (datasource 'ODBC:FB_DB')

CREATE SCHEMA IF NOT EXISTS firebird;

I then import schema for individual tables as follows:

IMPORT FOREIGN SCHEMA ogr_all LIMIT TO ("TBL_NAME") FROM SERVER fb_db_svr INTO firebird;

I tried taking a guess at a different foreign server SQL statement based on this https://pgxn.org/dist/ogr_fdw/

CREATE SERVER fb_db_svr FOREIGN DATA WRAPPER ogr_fdw 
OPTIONS (datasource 'ODBC:ODBC:FB_DB', config_options 'LIST_ALL_TABLES=YES USE_GEOMETRY_COLUMNS=NO');

I also tried setting a few environment variables on the server:

Still no luck. Is this possible, or is my only option to delete the GEOMETRY_COLUMNS table in the FireBird database?

Nate Wanner · Answer

This is a hack and I would very much like a better answer, but I will put it out there in case someone else runs into something similar.

Instead of a GEOMETRY_COLUMNS table in the FireBird database, I created a new table called "FIREBIRD_GEOMETRY_COLUMNS" with the same structure. For background, FireBird databases are typically all caps, whereas PostgreSQL is typically all lowercase. Additionally, FireBird does not allow renaming tables. To rename a table, you have to create a new table, copy data from the old table, and then delete the old table.

SQL to create table (all SQL in this answer is executed in FireBird, not PostgreSQL):

CREATE TABLE FIREBIRD_GEOMETRY_COLUMNS (
    F_TABLE_CATALOG    VARCHAR(255),
    F_TABLE_SCHEMA     VARCHAR(255),
    F_TABLE_NAME       VARCHAR(255),
    F_GEOMETRY_COLUMN  VARCHAR(255),
    G_TABLE_CATALOG    VARCHAR(255),
    G_TABLE_SCHEMA     VARCHAR(255),
    G_TABLE_NAME       VARCHAR(255),
    STORAGE_TYPE       INTEGER,
    GEOMETRY_TYPE      INTEGER,
    COORD_DIMENSION    INTEGER,
    MAX_PPR            INTEGER,
    SRID               INTEGER
);

COMMENT ON TABLE FIREBIRD_GEOMETRY_COLUMNS IS
'Required for OGC compliance when using WKB and WKT.';

I then created a view titled "GEOMETRY_COLUMNS":

CREATE or ALTER VIEW GEOMETRY_COLUMNS
AS SELECT * FROM FIREBIRD_GEOMETRY_COLUMNS

When I need to use the foreign schema "firebird" in PostgreSQL, I modify the FireBird view so that it has 0 records:

CREATE or ALTER VIEW GEOMETRY_COLUMNS
AS SELECT * FROM FIREBIRD_GEOMETRY_COLUMNS
WHERE SRID = -99999999

The tables can then be imported to the foreign schema and accessed. GIS software using the FireBird tables appear to still be able to read previously loaded layers, but cannot add new layers.

Once done using the foreign schema, I modify the FireBird view so it once again shows all records.

CREATE or ALTER VIEW GEOMETRY_COLUMNS
AS SELECT * FROM FIREBIRD_GEOMETRY_COLUMNS

Because FireBird doesn't allow renaming of tables, modifying the view is a simpler way of maintaining data in the database than creating and dropping tables. When the GEOMETRY_COLUMNS view has no records, ogr_fdw will access non-spatial tables. When it does have records, ogr_fdw will only access spatial tables defined in GEOMETRY_COLUMNS.

List all tables with ogr_fdw ODBC connection

One Answer

Add your own answers!

Ask a Question