search

pramsey / pgsql-ogr-fdw

PostgreSQL foreign data wrapper for OGR
MIT License
238 stars 33 forks source link

List all tables with ogr_fdw ODBC connection ignoring geometry_columns table #180

Closed nwanner closed 4 years ago

nwanner commented 4 years ago

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 that has a geometry_columns table I am limited to only the tables listed in that table. I have seen reference that MSSQL connections can get around this by setting environment variables, but I am using ODBC to a FireBird database.

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've found a work-around for what I need to do right now, by renaming the GEOMETRY_COLUMNS table in FireBird to FIREBIRD_GEOMETRY_COLUMNS, and then creating a view titled "GEOMETRY_COLUMNS". I can then temporarily change the view definition so that it has no records while actively using the ogr_fdw connection. (more info here: https://gis.stackexchange.com/a/352111/37413).

I expect that the workaround will take care of my needs for the foreseeable future, but wanted to propose an enhancement idea where list_all_tables and use_geometry_columns can be defined in the connection string rather than environment variables similar to the SQL I attempted above.

Thank you for your work on ogr_fdw - it has been a God-send.

pramsey commented 4 years ago

Thanks so much. This ODBC behaviour is inherited from the OGR ODBC driver, it's not something I have direct control of in the FDW. You'd want an option on the GDAL side in the ODBC implementation. A quick grep of the source seems to indicate it does not support either LIST_ALL_TABLES or USE_GEOMETRY_COLUMNS as options. Once it did you could pass those tokens in on the FDW options line, as you showed, but as of now it won't work. Move this feature request up to the GDAL issue tracker, I'm going to close this one out.