[gdal-dev] Fwd: OGR SQL dialect on FlatGeoBuf manipulating timestamps

[Trygve Aspenes]

Hi

I have some FlatGeoBuf files I have generated with the python fiona
library. This is a lat lon point and a timestamp. So I want to filter by
timestamp. What I have come up with is to use gdal sql dialect like this:

ogrinfo -sql "select * from flatgeobuf_demo where
TimeStamp>='2013-06-20T17:00:29Z'" flatgeobuf-data/*
INFO: Open of `flatgeobuf-data/points-20130620070000.fgb'
      using driver `FlatGeobuf' successful.
layer names ignored in combination with -sql.

Layer name: flatgeobuf_demo
Geometry: Point
Feature Count: 17
Extent: (-55.071900, -57.866400) - (58.263300, 51.737400)
Layer SRS WKT:
GEOGCRS["WGS 84",
    ENSEMBLE["World Geodetic System 1984 ensemble",
        MEMBER["World Geodetic System 1984 (Transit)"],
        MEMBER["World Geodetic System 1984 (G730)"],
        MEMBER["World Geodetic System 1984 (G873)"],
        MEMBER["World Geodetic System 1984 (G1150)"],
        MEMBER["World Geodetic System 1984 (G1674)"],
        MEMBER["World Geodetic System 1984 (G1762)"],
        ELLIPSOID["WGS 84",6378137,298.257223563,
            LENGTHUNIT["metre",1]],
        ENSEMBLEACCURACY[2.0]],
    PRIMEM["Greenwich",0,
        ANGLEUNIT["degree",0.0174532925199433]],
    CS[ellipsoidal,2],
        AXIS["geodetic latitude (Lat)",north,
            ORDER[1],
            ANGLEUNIT["degree",0.0174532925199433]],
        AXIS["geodetic longitude (Lon)",east,
            ORDER[2],
            ANGLEUNIT["degree",0.0174532925199433]],
    USAGE[
        SCOPE["Horizontal component of 3D system."],
        AREA["World."],
        BBOX[-90,-180,90,180]],
    ID["EPSG",4326]]
Data axis to CRS axis mapping: 2,1
Geometry Column = _ogr_geometry_
TimeStamp: String (0.0)
OGRFeature(flatgeobuf_demo):19
  TimeStamp (String) = 2013-06-20T17:00:29Z
  POINT (11.3157 -5.3703)
<truncated>

However what I want is to make a query with all points between one
timestamp and another. And here is my problem: the time parameters come
from a request query and have the format YYYY-mm-ddTHH:MM:SSZ. So I need a
way to say:

ogrinfo -sql "select * from flatgeobuf_demo where TimeStamp between
'2013-06-20T17:00:29Z' and '2013-06-20T17:00:29Z'+TimeInterval"
flatgeobuf-data/*

I could rewrite the flatgeobuf file setting TimeStamp as a float containing
the epoch instead. But then I can not convert the incoming query with the
time from my request( with fixed format)

Any idea if, and hopefully how, I can achieve this in OGR SQL?

Trygve Aspenes
--
Trygve Aspenes - Senioringeniør/Senior Engineer
Fjernmåling Utvikling/Remotesensing development  Telefon/Phone: 22963133
Fjernmålingsavdelingen/Remotesensing department  Postboks 43, Blindern
Metklim                                          0313 OSLO
Meteorologisk Institutt
/Norwegian Meteorological Institute               NORWAY
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20221019/11298b50/attachment.htm>