geopython / geopython/pygeofilter
SqlAlchemy temporal operations not properly handled
- Dominant language
- Python
- Stars
- 90
- Forks
- 44
- PR merge metrics
- No merged PRs in 30d
Description
Correct me if i'm completely misusing this but I'm trying to perform a simple "meets" expression as cql-json:
For simplicity my db contains the following datetimes:
```
1: 2019-06-24T07:41:10Z
2: 2019-06-24T07:41:13Z
3: 2019-06-24T07:41:17Z
4: 2019-06-24T07:41:21Z
```
And I want to perform a "meets" request with time interval `2019-06-24T07:41:13 - 2019-06-24T07:41:18` as input.
This should result in all rows from the db where the datetime coinsides with the beginning of the interval, which in my db is only the second row: `2019-06-24T07:41:13Z`
However running the query, it seems to parse correctly:
```
from pygeofilter.parsers.cql_json import parse as parse_json
from pygeofilter.backends.sqlalchemy import to_filter
import sqlalchemy as sa
cql_json = "meets": [{ "property": "datetime" }, ["2019-06-24T07:41:13Z", "2019-06-24T07:41:18Z"]]
ast = parse_json(cql_json)
TimeMeets(lhs=ATTRIBUTE datetime, rhs=Interval(start=datetime.datetime(2019, 6, 24, 7, 41, 13, tzinfo=), end=datetime.datetime(2019, 6, 24, 7, 41, 18, tzinfo=)))
```
but when it evaluates the ast to a SqlAlchemy expression, and queries the db, it returns both row 2 and 3: `2019-06-24T07:41:13Z` and `2019-06-24T07:41:17Z`
Inspecting the temporal method of `filters.py` in the sqlalchemy backend:

it seems to handle special cases for `before`, `after` and `tequals` operations, thus other temporals end up being evaluated as either `between`, `>=`, or `<=` so my query ends up being evaluated as a `between(datetime, "2019-06-24T07:41:13Z", "2019-06-24T07:41:18Z")` explaining why row 3 is also returned
On a sidenote:
It seems that you **have** to specify an interval as input for anything besides `before` and `after` otherwise this crashes:

with `TypeError: cannot unpack non-iterable datetime.datetime object`
From the [OAF specification](https://portal.ogc.org/files/96288#enhanced-temporal-operators):
> CQL supports date and timestamps as time instants, but even the smallest "instant" has a duration and can also be evaluated as an interval.
Hence to my understanding you should be able to run "meets", "metby", "begins" ect. on exact timedates, treating them as intervals, and not having to explicitly specify an interval (despite it being nonsensical to do in practise)
Am I completely missing something here or is the temporal method in fact not handling all temporal cases?
Contributor guide
Assessment
This issue has not been assessed yet.