2

When downloading geopackages via GeoServer from the data provider datamap.gov.wales (e.g. this one) I get a geopackage containing the designation_date field, which is marked as TIMESTAMP. The raw WFS type is marked as xsd:dateTime.

Since SQLite doesn’t have a concept of types TIMESTAMP is permissible, however it doesn’t conform to any standard. I would have thought the type should be DATETIME.

The reason for the importance is that GDAL drops this field.

I am unclear whether this is a problem with the configuration of the service, the GeoServer geopackage extension, or the geopackage code within Geotools.

Does anyone have any ideas before I start spamming bug-trackers?

1
  • 1
    Don't start with spamming any bug-trackers. If you cannot get an answer from here send mail to geoserver-users mailing list. It is a relatively responsive list and also the main developers tend to read it.
    – user30184
    Commented Aug 17, 2022 at 6:14

2 Answers 2

2

ok i have a quick hack for you.

You need to change the datatype of the Geopackage, before opening it via GDAL. You can do this using DB Browser (SQLite) software. Open the gpkg in DB Browser, right click on the cadw_rhpg_registeredareas table and select "Modify Table" In the dropdown for the 'type' for the designation_date field, just type in DATETIME

Now open in GDAL.

I just tested this, using the dataset and opened it in QGIS. Prior to changing it, QGIS didn't recognise the field. After making the change as per above, it recognizes and displays the field and field values.

This can probably be done a bit more elegantly, but it worked!

enter image description here

0

I have an alternative solution in code:

import sqlite3
import re

col_name = 'your_col_name'
col_type = 'desired_new_type'
table_name = 'your table name'

con = sqlite3.connect('myfile.gpkg')
cur = con.cursor()

cur.execute('PRAGMA writable_schema=True')
res = cur.execute(f'select sql from sqlite_master where type="table" and tbl_name="{table_name}"')

existing_sql = res.fetchone()[0]
regex = f'("{col_name}" )\w*'
new_sql = re.sub(regex,rf'\1{col_type}',existing_sql)

cur.execute(f'''update sqlite_master set sql='{new_sql}' where name="{table_name}"''')
con.commit()

Bit annoying but it works, and is a lot easier than adding, copying then removing columns, particularly if you sqlite version is < 3.35.

I would say it should only be used where you are sure the column name is not a standard sqlite one anyway, otherwise that might cause some problems, see the docs for PRAGMA writable_schema

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.