>> Jukka,
>>
>> The GPKG spec also mentions that the declared column data type that must be
>> used is "DATE" (or "DATETIME"), that is the one used in the CREATE TABLE
>> statement : CREATE TABLE my_table ( DATE my_date, ...)
>
> You are right even I can't find an example of using CREATE table...DATE from
> the specification. However, the CREATE clauses for the system tables do use
> DATETIME and I can imagine that the meaning is that DATE should be used
> similarly.
That’s correct. The intention is that gpkg authors only use declared column
types that are listed in the spec (see req 5 and table 1). This is to allow
correct interpretation of columns when exchanging gpkg files between systems.
We restricted the DATE and DATETIME format to a single ISO 8601 variant for
each for the same reason. It would makes things too complicated and error prone
when exchanging files if any date were allowed. The only downside to this is a
performance hit compared to julian time as REAL or unix timestamp as INTEGER
since the ISO 8601 string needs to be parsed.
> All in all, handling dates is the typeless SQLite in a reliable and
> interoperable way seems to be a bit complicated. I will think about writing
> a line or two into http://www.gdal.org/drv_sqlite.html
> <http://www.gdal.org/drv_sqlite.html> about how GDAL plays
> with them once I understand it myself.
That was my conclusion a year or two ago as well :) That’s what led to the
current text in gpkg.
The same applies for the other declared column types as well. Earlier revisions
of the spec didn’t have the type table which resulted in one vendor using
int32, int64, float32, float64, etc; another using int, bigint, float, double
and so on. Restricting numeric types to a single list again simplifies things
and improves interoperability.
The sizes are intended as hints for applications. The idea is that if a column
is declared as FLOAT that you can assign values from this column to a 32-bit
floating point variable in Java, C, C#, etc without losing precision. Without
this everything has to be 64-bit.
Pepijn
_______________________________________________
gdal-dev mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/gdal-dev
