2

I am looking at forestry data of Finland's Etelä-Savo region. I download the data:

curl -L -O https://avoin.metsakeskus.fi/aineistot/Metsankayttoilmoitukset/Maakunta/MKI_Etelä-Savo.zip

Unzip it:

ditto -xk MKI_Etelä-Savo.zip MKI_Etela-Savo/

And try converting it to GeoJSON:

ogr2ogr out.geojson MKI_Etela-Savo/MKI_Etelä-Savo.gpkg

Output I get:

ERROR 6: Invalid unixTime = 371054750083200
ERROR 6: Invalid unixTime = 371054750083200
ERROR 6: Invalid unixTime = 371055261657600
ERROR 6: Invalid unixTime = 371055261657600

Which is very strange, as 371054750083200 and 371055261657600 seem to be valid unix timestamps, pointing to points in time on 4th October 1981 (can check here or here).

A way around this error could be disregarding all columns of type Date. One can do that via:

nonDateColumns=$(ogrinfo -so -al -json MKI_Etela-Savo/MKI_Etelä-Savo.gpkg forestusedeclaration | jq -r '.layers[].fields[] | select(.type != "Date") | .name' | tr '\n ' ',' | sed 's/,$//')
layerName=$(ogrinfo -json MKI_Etela-Savo/MKI_Etelä-Savo.gpkg | jq -r '.layers[].name')
ogr2ogr no_date_columns.geojson MKI_Etela-Savo/MKI_Etelä-Savo.gpkg -sql "SELECT ${nonDateColumns} FROM ${layerName}"

no_date_columns.geojson is then successfully created. This way of doing it is just avoiding the problem instead of properly fixing it, so I am not a fan.

How do I fix the Invalid unixTime error?

1
  • 04-Oct-1981 is just 371 million seconds, not 371 trillion. Multiplying by 1000 for milliseconds makes sense due to the range of 64-bit integers, but multiplying for microseconds makes no sense at all.
    – Vince
    Commented Dec 4, 2023 at 15:27

2 Answers 2

2

This SQL query

select id, standarrivaldate as d
from forestusedeclaration
order by d asc;

Prints these two rows first:

58624   1005-11-23
59494   1005-11-23

Obviously that date is not correct. There is also another odd date 1022-02-08. We can't know for sure what are the correct dates but maybe they are a thousand year later. Fixing the data makes the conversion into GeoJSON to work

update forestusedeclaration
set standarrivaldate='2005-11-23'
where standarrivaldate='1005-11-23';

update forestusedeclaration
set standarrivaldate='2022-02-08'
where standarrivaldate='1022-02-08';

You can use whatever SQLite client for correcting the data. With ogrinfo the syntax is:

ogrinfo  MKI_Etelä-Savo.gpkg -sql "update forestusedeclaration set standarrivaldate='2022-02-08'   where standarrivaldate='1022-02-08'"
1

Loads fine in QGIS

I agree with @user30184, those 'dates' do look bad. Nevertheless, opening the GPKG in QGIS apparently works just fine. It read 130523 tuples.

The dates QGIS reads for the id's mentioned:

id      standarivaldate
58624   2013-06-07
59494   2014-01-15

The GEOJSON export also looks fine regarding dates and times.

Not much help if you want to automate your process, but if you just need the data this may be a reasonable workaround.

2
  • Thanks for checking! This is very strange, as my QGIS does display the problematic dates.
    – zabop
    Commented Dec 5, 2023 at 10:30
  • You're welcome, but I'm out of my depth here. I'm on QGIS 3.16.5. Let me know if you need more info or testing. I guess this sounds like a bug?
    – peter
    Commented Dec 5, 2023 at 11:04

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.