search

geopython / pygeoapi

pygeoapi is a Python server implementation of the OGC API suite of standards. The project emerged as part of the next generation OGC API efforts in 2018 and provides the capability for organizations to deploy a RESTful OGC API endpoint using OpenAPI, GeoJSON, and HTML. pygeoapi is open source and released under an MIT license.
https://pygeoapi.io
MIT License
460 stars 250 forks source link

PostgreSQL provider does not support datetime #1622

Closed tomkralidis closed 2 months ago

tomkralidis commented 2 months ago

Description The PostgreSQL provider does not handle datetime_ inputs as part of its query function (even though we advertise support for same in docs).

Steps to Reproduce Assuming a PostgreSQL time enabled resource in pygeoapi config, execute an OGC API - Features items query with .../items?datetime=t1

Expected behavior datetime support should be handled accordingly in the PostgreSQL provider.

Screenshots/Tracebacks If applicable, add screenshots to help explain your problem.

Environment

Additional context Thanks to @david-i-berry for reporting.

cc @KoalaGeo

KoalaGeo commented 2 months ago

We've been doing datetime filters using CQL like:

  1. https://ogcapi.bgs.ac.uk/collections/sensor-water-level-sta/items?f=json&limit=5&sortby=-resulttime&properties=resulttime,result,location_name
  2. https://ogcapi.bgs.ac.uk/collections/sensor-water-level-sta/items?f=json&limit=5&filter=resulttime%20BEFORE%202024-04-06T22:30:00Z&sortby=-resulttime&properties=resulttime,result,location_name
  3. https://ogcapi.bgs.ac.uk/collections/sensor-water-level-sta/items?f=json&limit=5&filter=resulttime%20AFTER%202024-04-07T22:30:00Z&sortby=-resulttime&properties=resulttime,result,location_name
tomkralidis commented 2 months ago

The equivalent queries using the datetime parameter would be:

  1. https://ogcapi.bgs.ac.uk/collections/sensor-water-level-sta/items?f=json&limit=5&sortby=-resulttime&properties=resulttime,result,location_name (same)
  2. https://ogcapi.bgs.ac.uk/collections/sensor-water-level-sta/items?f=json&limit=5&datetime=../2024-04-06T22:30:00Z&sortby=-resulttime&properties=resulttime,result,location_name
  3. https://ogcapi.bgs.ac.uk/collections/sensor-water-level-sta/items?f=json&limit=5&datetime=2024-04-07T22:30:00Z/..&sortby=-resulttime&properties=resulttime,result,location_name
KoalaGeo commented 2 months ago

Ah i see....

{ "code":"InvalidParameterValue", "description":"Configured times should be RFC3339" }

tomkralidis commented 2 months ago
  1. same request, so works
  2. returns The requested URL was rejected. Please consult with your administrator.. I am guessing there is something before pygeoapi that is returning this error (likely the ../ in the URL)
  3. returns Configured times should be RFC3339 error
KoalaGeo commented 2 months ago
  1. Is allowed through now (pedantic F5....) now same error as 3.
tomkralidis commented 2 months ago

FYI @david-i-berry has a fix and will test/issue a PR in the next few days (thanks Dave).

webb-ben commented 2 months ago

Related to https://github.com/geopython/pygeoapi/discussions/1607