QGIS » QGIS Application

Hmm this is an interesting problem... It is all about width/precision issues of the SHAPE_Area field.

OGR reports that the field has width=19 and precision=11. In OGR the width includes also the decimal point. QGIS reports the field as with=18 and precision=11 which is correct (QGIS follows SQL convention of numeric(width,precision) data type where "width" does not include decimal point - only significant numbers). During export, QGIS therefore creates field numeric(18,11) for SHAPE_Area, which is correct.

Now here is the interesting bit: the stored value is "11456942.7758000010" that actually needs field type numeric(18,10) to be stored, the number is simply too big for numeric(18,11).

ogr2ogr works just fine because it handles the width in the same manner for shapefiles and postgres. Just tested and ogr2ogr indeed looses precision of last digits when converting from Postgres to shapefile.

One could argue that 1) the input shapefile data are badly written and 2) ogr2ogr looses precision, while QGIS handles everything correctly and the error message thrown during import to DB is perfectly valid.

It works in 2.14 without any errors because 2.14 does not preserve the exact numerical type and uses float8 instead (which looses some precision). Even though most of the time people probably do not care about that, in some cases such loss of precision may be an issue. OGR for example has an option for Postgres driver ("PRECISION") which allows users to say whether to use exact width/precision from the source field or whether to simply use float8.

By the way there is a related closed bug #11755

Opinions on what to do with it are most welcome :-)

For the completeness, the change in behavior to not loose precision was introduced in this PR: https://github.com/qgis/QGIS/pull/3098

I will point this to Even to see what he thinks.

Would be nice if the user could still import it. QGIS could handle this difference of width/precision reporting between QGIS and OGR or OGR could be fixed. For QGIS 2.14/2.16, as OGR won't be fixed in the same time, it would be nice if QGIS could handle this for now, until it is potentially aligned in OGR (or not).

I pretty much agree with Martin's analysis :
- OGR field and width semantics likely originates from the shapefile/DBFfile and MapInfo convention, ie OGR width is the number of characters needed including leading minus sign and decimal point, and precision is the number of decimal figures
- The OGR PostgreSQL driver should likely do the adjustment to convert between the OGR width/precision and SQL precision/scale (https://www.postgresql.org/docs/9.0/static/datatype-numeric.html: beware of naming clashes ! OGR width ~= SQL precision and OGR precision = SQL scale !), but currently it does not.

There's not really a way of doing a perfect round-tripping because of the optional minus sign. So if you translate from SQL convention to OGR convention, you must compute OGR width as SQL precision + 1 (for minus sign) + 1 (for decimal point). But on the reverse way, for safety, you must do SQL precision = OGR width - 1, so if you do several roundtripping, the OGR width/SQL precision will keep growing. Apart from potentially doing the above fix in the OGR PG driver (which would break importing this shapefile because the DBF width.precision isn't consistant with the content), at this point of GDAL history, we wouldn't probably want to change the semantics of OGR width and precision in the abstract model as it could break user expectations.

Andreas Neumann wrote:

I will point this to Even to see what he thinks.

Would be nice if the user could still import it. QGIS could handle this difference of width/precision reporting between QGIS and OGR or OGR could be fixed. For QGIS 2.14/2.16, as OGR won't be fixed in the same time, it would be nice if QGIS could handle this for now, until it is potentially aligned in OGR (or not).

That was what #11755 (2fcbc8b) is about. The actual problem here is that the value in the DBF is too big for the definition of the DBF field (not sure how that works). I came to the same conclusion - but didn't have a good/quick solutions either (and moved on...)

I've seen the same problem loading a single .dbf file into PostGIS via DB Manager.

An easy workaround is to use Shapefile & DBF Loader in PgAdmin to load the file directly into PostGIS, without use of QGIS. This worked well for me.

PostGIS loader (shp2pgsql) behaves as QGIS-2.14, sing "numeric" only when DBF width is > 18.
What was the rationale to change that behavior ? The referenced PR, which was merged as 92f71b696ca93c792ae5602ed82863fcef0e5006 in the 2.14 branch, did not contain an automated testcase.

Ok I was wrong, shp2pgsql (from PostGIS) behavior is to specify a width, but not a precision.
Doing so within QGIS results in a successful import. I'll look at automating a test for this.

Actually, I'm taking this back. shp2pgsql, which uses shapelib, reports the field to have width=19 and precision=11, but then doesn't set precision/width of the target field when the input is "FTDouble" (only does for input "integer").

So we're back to the question: why do we want to be strict about precision ?

So upon further digging, I've found that OGR correctly reports width as 19 (which would accept the value) but QGIS is reducing it by 1 in QgsOgrProvider::loadFields, for unknown reasons (there's no comment explaining that).

I confirm that removing the unit-subtraction from QgsOgrProvider::loadFields fixes the import.
The QgsField::length member is not documented either, so it isn't easy to know why would anyone change it to what value. Lack of documentation about it seems to be the main problem here.

I also confirm the other fix is to perform unit-addition in QgsPostgresProvider::convertField, but then the QgsField::length would be different if you load from shapefile or from the imported PostgreSQL table. Semantic of QgsField::length should be carefully defined, for the Double type.

Note that the PostgreSQL "numeric" type definition interprets the precision and scale modifiers as the total number of digits (not including the comma) and the number of digits on the right of the comma. Setting those values straight would require knowing in advance how many digits would possibly be on the left of the comma, and I don't think DBF would contain that info.

Are we sure we want to constraint ?

Sandro Santilli wrote:

I also confirm the other fix is to perform unit-addition in QgsPostgresProvider::convertField, but then the QgsField::length would be different if you load from shapefile or from the imported PostgreSQL table. Semantic of QgsField::length should be carefully defined, for the Double type.

It should follow what also the edit widgets expect (and was at some point).

I see by default there's always "text edit" as the edit widget associated with floating point fields, but I'm no expert there

Sandro Santilli wrote:

I see by default there's always "text edit" as the edit widget associated with floating point fields, but I'm no expert there

with a QgsFieldValidator that follows the field definition.

I've filed a PR with a testcase and a revert of the offending commit:
https://github.com/qgis/QGIS/pull/3590

The testcase checks for being able to import to PostgreSQL a memory layer in which a field has length/precision of 6/4 and a value of 123.456. The test fails before the revert. The test does not check the ability to retain (round-trip) the length/precision information, and I think it doesn't make sense to even try, given the inability of QgsField to actually use those parameters as constraining ones.

Hello Sandro,

It's me that change this some times ago, because the french ministry of developement says that importing some data from postgis db to another postgis db create the new table with floats intead of decimal fields.

Note that using floats intead of decimals under 18 digits do not fix the problem over 18 digits.

Arnaud: note that as long as we store the value in a double, 18 digits just won't fit, so the value would be changed anyway.
PostgreSQL is able to store 18 digits, in a "numeric" (need not specify scale/precision for that), but qgis would still write an interpretation of the floating point number, with less precision, unless the value is kept as a string in qgis.