0

I need to link an external dbf table into an existing PostgreSQL database. I managed to import that table into PostgreSQL but that creates a copy and therefor redundant data. The dbf table is the attribute table of a shapefile, but I don’t need the spatial aspect of it. The exercise is part of a project to move data from an MS Access database to PostgreSQL hoping that the data then become accessible from QGIS. Th dbf table is at the moment linked into the MS Access database and used in queries (views) which I want to re-build in PostgreSQL. I found lots of posts about importing dbf tables into but nothing which would work about linking a dbf table. The closest I got was the Foreign Data Wrapper, but I didn’t manage to use it for my purpose. I’m using PostgreSQL with pgAdmin 4.24.

Many thanks

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
Onosma
  • 1
  • 2
    If you write a [foreign data wrapper](https://www.postgresql.org/docs/current/fdwhandler.html) for it yes. Don't see an existing one besides an ODBC FDW where you could use an ODBC based dBase driver. – Brian Sep 17 '20 at 20:44
  • 1
    Closest I know of is [pgsql-ogr-fdw](https://github.com/pramsey/pgsql-ogr-fdw). It works with the entire shapefile though. – Adrian Klaver Sep 17 '20 at 21:11
  • Personally, I would try to eliminate the shapefile completely and store everything in postgresql / postgis. That to me is the main benefit of postgres - you can replace both Access and shapefiles, manage all your data in one place and use QGis linked to postgres to visualise your data. – mlinth Sep 18 '20 at 13:58
  • Hi Brian and Adrian, I was afraid somebody would suggest fdw. I already had a look into it, but didn't manage to make it work for me. Ideally I would find an example for a dbase file somewhere. I will investigate further and might come back with more questions. – Onosma Sep 18 '20 at 17:20
  • Hi mlinth, Unfortunately PostgreSQL as I can see so far cannot replace Access. PostgreSQL is just a database backend without a frontend (Forms etc.). And I will use it as backend for my MS Access database so that the data are via PostgreSQL available to QGIS and the rest of the non-MS world. I might transfer the shapefile into PostgreSQL at some point when I'm sure that I can do in QGIS what I can do in ArcView. – Onosma Sep 18 '20 at 17:33

1 Answers1

0

The exercise is part of a project to move data from an MS Access database to PostgreSQL hoping that the data then become accessible from QGIS.

If you must use PostgreSQL in order to provide access to your spatial data from QGIS, I see no other option than importing the shapefile into PostgreSQL (PostGIS). If for whatever reason you do not need the geometries, you can drop the geometry column after importing the shapefile into the database:

ALTER TABLE table_name DROP COLUMN column_name;

Alternative scenario:

If we're talking about static shapefiles and you don't really need to use PostgreSQL, you can use GeoServer to publish this shapefile via Web Feature Service (WFS) - it is at least what I do in small projects. The easiest option would be to copy the shapefiles into a so called GeoServer Data Directory and publish them afterwards. After that you'd be able to access the data from QGIS using its WFS Client.

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • Hi Jim, the shapfile is a product of a Union and will be frequently updated. For that reason any import wouldn't help. By the way I already managed to import the data via MS Access into PostgreSQL. What I need is a link not a copy. Thanks for the hint about dropping the geometry. – Onosma Sep 18 '20 at 17:43