1

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";

2
  • 1
    I guess that your pygeoapi serves data as GeoJSON. GeoJSON does not have a native data type for dates, but GDAL tries to find strings which look like dates/datetimes. Sometimes it does not recognize them right. Create column "measure_date" as a string because that's what you get from pygeoapi. You can cast the strings which are presenting dates into real dates in the database. Add a sample of the data from the pygeoapi service and the schema from ogrinfo OAPIF:https://server.org/pygeoapi/collections/my_collection -so and we would not need to guess as much.
    – user30184
    Commented Nov 18, 2022 at 13:36
  • Would you mind giving some information about how it goes?
    – user30184
    Commented Nov 20, 2022 at 18:24

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.