Setting field width and precision when generating GeoPackage files does not work properly

[rudigens]

I am currently working on some Python code to generate GeoPackage files. The following code defines a very simple point file with ID, latitude and longitude fields.

ds = ogr.GetDriverByName('GPKG').CreateDataSource(gpkgFile)
layer = ds.CreateLayer('point', geom_type = ogr.wkbPoint, srs = srs)
field_ID = ogr.FieldDefn('ID', ogr.OFTString)
field_ID.SetWidth(255)
layer.CreateField(field_ID)
field_Lat = ogr.FieldDefn('Latitude', ogr.OFTReal)
field_Lat.SetWidth(12)
field_Lat.SetPrecision(5)
layer.CreateField(field_Lat)
field_Lon = ogr.FieldDefn('Longitude', ogr.OFTReal)
field_Lon.SetWidth(12)
field_Lon.SetPrecision(5)
layer.CreateField(field_Lon)

The issue is that the floating point fields don't show up with width and precision, when I use ogrinfo for the generated file.

Layer SRS WKT:
GEOGCS["WGS 84",
    DATUM["WGS_1984",
        SPHEROID["WGS 84",6378137,298.257223563,
            AUTHORITY["EPSG","7030"]],
        AUTHORITY["EPSG","6326"]],
    PRIMEM["Greenwich",0,
        AUTHORITY["EPSG","8901"]],
    UNIT["degree",0.0174532925199433,
        AUTHORITY["EPSG","9122"]],
    AUTHORITY["EPSG","4326"]]
FID Column = fid
Geometry Column = geom
ID: String (255.0)
Latitude: Real (0.0)
Longitude: Real (0.0)

The last two lines should both be 'Real (12, 5).

I have tested the code by saving the result as a shape file. That worked properly.

[Jukka Rahkonen]

By reading the GeoPackage standard ​https://portal.opengeospatial.org/files/?artifact_id=64506 I can't find any mention about setting width of precision for numbers. If you'll find something, please add a quotation into the description.

Generally SQLite does not really care about width and precision. Read for example ​http://stackoverflow.com/questions/21757722/how-to-use-sqlite-decimal-precision-notation. However, it is possible to create a field so that it contains sort of a wish about what kind of numbers should be stored into it. It is still just a wish.

create table foo (test real(12,5));

I feel that this is not a bug, but I am not sure.

[Even Rouault]

I agree with Jukka. Width and precision for numeric fields are not part of the GeoPackage standard. See Table 1 " GeoPackage Data Types" in ​http://www.geopackage.org/spec/#_sqlite_container. Width is only for TEXT columns. Extending real to support width and precision isn't really a technical problem, but this would be an extension of the specification. And also worth to note that real field are internally stored as IEEE754 Float64 and not as decimal numbers.

[rudigens]

My argument was not based on the GeoPackage standard. Strictly speaking it does not seem to be part of the standard, so I certainly see a reason not to consider this change. I am more looking for the equivalent of the shape file implementation.

The lack of defining the width and precision of floating point values gives a false sense for what the significant number actually is. For example, a latitude value should only have 4-5 decimals. You can't measure an angle more accurately than that. To me that makes a lot of sense. I don't think, in this context, it matters whether the values are actually stored as decimal numbers or not.

[Jukka Rahkonen]

I agree that knowing the reasonable precision of the data is usable information. However, not all GIS formats support that natively. Shapefiles and Mapinfo files do, as well as PostGIS, Oracle and some others.

If you want to play with Geopackage or Spatialite you can edit the schema with some post-processing SQL but it may be that Geopackage is no standard compliant after such schema manipulation. Spatialite should not care, though. It may also be that you must use your own tools for interpreting the create table SQL from pragma. Please see this example:

Check the schema of an existing table 'foo' with SQL:
select sql from sqlite_master where name='foo'
Result:
CREATE TABLE foo (test real)

Set SQLite to allow fiddling with the schema
PRAGMA writable_schema=1;

Add precision to field 'test'
UPDATE SQLITE_MASTER SET SQL = 'CREATE TABLE foo (test real(12, 5))'
where name='foo';

Check the result
select sql from sqlite_master where name='foo';
CREATE TABLE foo (test real(12, 5))

Turn schema editing off
PRAGMA writable_schema=1;

Another possibility could be to save the precision model of the data into some metadata table. People reading gdal-dev mailing list may have experience and ideas about that.

[Even Rouault]

This ticket has been automatically closed because Trac is no longer used for GDAL bug tracking, since the project has migrated to GitHub. If you believe this ticket is still valid, you may file it to ​https://github.com/OSGeo/gdal/issues if it is not already reported there.