Using the ogr_fdw extension on PG 14 PostGIS 3.2 on Ubuntu 22.04.1 LTS, I am trying to register a foreign table in my local PostgreSQL database based on an OGC API Feature endpoint serving GeoJSON features as follow:
CREATE EXTENSION postgis;
CREATE EXTENSION ogr_fdw;
CREATE SERVER "my_server"
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource 'OAPIF:https://server.org/pygeoapi/collections/my_collection',
format 'OAPIF' );
CREATE FOREIGN TABLE my_collection (
id bigint,
geometry Geometry(MultiPolygon,4326),
name VARCHAR(40),
title TEXT,
measure_date DATE,
length NUMERIC
)
SERVER "my_server"
OPTIONS (layer 'my_collection');
But then, this query raises an error:
SELECT id, geometry, name, title, measure_date, length
FROM public.my_collection;
ERROR: column "measure_date" of foreign table "my_collection" converts OGR "String" to "date"
SQL state: HV004
Is this a known bug or did I miss something somewhere?
Note: it works very well if I remove the measure_date
field from all these queries.
Versioning information:
PostgreSQL 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit
POSTGIS="3.2.0 c3e3cc0" [EXTENSION]
PGSQL="140"
GEOS="3.10.2-CAPI-1.16.0""
PROJ="8.2.1"
LIBXML="2.9.12"
LIBJSON="0.15"
LIBPROTOBUF="1.3.3"
WAGYU="0.5.0 (Internal)"
On the ogr_fdw
extension table:
CREATE EXTENSION IF NOT EXISTS ogr_fdw
SCHEMA public
VERSION "1.1";
ogrinfo OAPIF:https://server.org/pygeoapi/collections/my_collection -so
and we would not need to guess as much.