Problem implementing an ogr_fdw FDW

[Brent-Wood]

I'm unable to get an FDW set up, hopefully the issue & solution will be clear to someone with more clues than me!! Postgres v12.6 on Linux Mint, all packages installed via apt from Postgres & ubuntugis-unstable repositories.

Proj: Rel. 7.2.1, January 1st, 2021 GDAL: GDAL 3.2.1, released 2020/12/29

My VRT file works with QGIS and with ogr_info:

ogrinfo /tmp/SpecifyQuery2_180.vrt INFO: Open of /tmp/SpecifyQuery2_180.vrt' using driverOGR_VRT' successful. 1: collectionObjects_180 (Point)

The SQL I tried to generate an FDW from this VRT file is:

create server specify_fdw foreign data wrapper ogr_fdw
options ( datasource '/tmp/SpecifyQuery2_180.vrt', format 'OGR_VRT' );

It returns the following error message, which I have no idea how to resolve.

ERROR: GDAL AppDefined [1] PROJ: proj_create_from_database: SQLite error on SELECT name, type, coordinate_system_auth_name, coordinate_system_code, datum_auth_name, datum_code, area_of_use_auth_name, area_of_use_code, text_definition, deprecated FROM geodetic_crs WHERE auth_name = ? AND code = ?: no such column: area_of_use_auth_name

Any advice appreciated.

[pramsey]

Looks like you have multiple copies of proj lying around? And somewhere a mismatch between the support database it expects and the one it finds. Or perhaps your gdal has proj statically linked into it? Anyways, there's definitely something going on in support libs here, you're not getting to the point of having an ogr_fdw issue yet.

[Brent-Wood]

Thanks Paul, That gives me a direction to follow up... Brent

On Tuesday, June 15, 2021, 6:44:34 AM GMT+12, Paul Ramsey ***@***.***> wrote:  

Looks like you have multiple copies of proj lying around? And somewhere a mismatch between the support database it expects and the one it finds. Or perhaps your gdal has proj statically linked into it? Anyways, there's definitely something going on in support libs here, you're not getting to the point of having an ogr_fdw issue yet.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub, or unsubscribe.

[robe2]

@Brent-Wood it seems a lot of people have this issue when mixing UbuntuGIS with apt.postgresql.org (or postgresql from core ubuntu distro). The reason is each ships their own version of Proj and GDAL and their proj is incompatible.

That said the last guy I ran into that had this issue (on PostGIS mailing list and irc). Said UbuntuGIS unstable does carry PG 12, so he went with that. Better even UbuntuGIS carries GEOS 3.9. The one you get from apt (apt.postgresql.org gets it's dependencies from the core Ubuntu distro), has GEOS 3.8, so you'd be missing out on the precision goodies if you are running PostGIS 3.1.

Here is a snippet of what he wrote on IRC

benboughton: Today using UbuntuGIS-unstable worked best for my PostGIS problem :) I still got postgres 12 and postgis 3.1.1 gdal 3.2.1. I don't need bleeding edge postgres for what I am doing. He said unstable gives

3.9.0-CAPI-1.16.2 proj 7.2.1

The other option if you don't want to mess with your installed PostgreSQL is change the path of your proj.db file to the version of Proj PostgreSQL is expecting as I noted here:

https://lists.osgeo.org/pipermail/postgis-users/2021-June/044917.html

.