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

[Trygve Aspenes]

OK, so this is a version thing. I upgraded the gdal version and the
following sqlite3 version 3.39.3 then unixepoch works(using the SQLITE
dialect)

ogrinfo -dialect SQLITE -sql "select * from li_flatgeobuf_demo where
unixepoch(TimeStamp) between unixepoch('2013-06-20T17:00:29Z') and
unixepoch('2013-06-20T17:00:29Z')+300" /data/mapserver/tile-index/*
INFO: Open of `/data/mapserver/tile-index/points-20130620070000.fgb'
      using driver `FlatGeobuf' successful.
layer names ignored in combination with -sql.

Layer name: SELECT
Geometry: Point
Feature Count: 17
Extent: (7.254900, -6.423300) - (58.263300, 51.656400)
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)"],
        MEMBER["World Geodetic System 1984 (G2139)"],
        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 = GEOMETRY
TimeStamp: String (0.0)
OGRFeature(SELECT):0
  TimeStamp (String) = 2013-06-20T17:00:29Z
  POINT (11.3157 -5.3703)

Trygve Aspenes



ons. 19. okt. 2022 kl. 14:10 skrev Trygve Aspenes <trygveas at met.no>:

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


-- 
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/0f816578/attachment.htm>