1

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:

enter image description here

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

  • 1
    No good idea but maybe try a middle ground: renaming the table to FIREBIRD_GEOMETRY_COLUMNS or something similar to it ? – ThomasG77 Feb 26 at 0:05
  • @ThomasG77 That kind of helps. The table GEOMETRY_COLUMNS is required as part of the OGC <portal.opengeospatial.org/files/?artifact_id=829> so GIS software knows the spatial fields are present. If I rename the table, layers already added in GIS are still visible, but new layers cannot be added because the GIS software can't reference the GEOMETRY_COLUMNS table. – Nate Wanner Feb 26 at 16:09
  • However, I can use a GEOMETRY_COLUMNS view in place of having a table, with it simply querying all records in FIREBIRD_GEOMETRY_COLUMNS. If I modify the view with an impossible clause like "WHERE SRID = -99999999" so there are no records in the table, then I am able to import the foreign schema from Firebird to PostgreSQL using ogr_fdw. For now, I will likely modify my Python scripts so they change the View prior to sync and then change it back afterwards. Kind of a hack, but it works. Thanks @ThomasG77. – Nate Wanner Feb 26 at 16:11
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.