ExportSHP data loss?
Ayhan Ergul
SELECT DropTable(NULL, 'a_minimal', 1);
CREATE TABLE a_minimal(k INT, v REAL, geometry);
INSERT INTO a_minimal (k, v, geometry) VALUES (1, 0.023201, MakePoint(-99, 33, 4326));
INSERT INTO a_minimal (k, v, geometry) VALUES (2, 0.139207, MakePoint(-99, 33, 4326));
INSERT INTO a_minimal (k, v, geometry) VALUES (3, -0.07498, MakePoint(-99, 33, 4326));
INSERT INTO a_minimal (k, v, geometry) VALUES (4, 0.301135, MakePoint(-99, 33, 4326));
INSERT INTO a_minimal (k, v, geometry) VALUES (5, -0.19822, MakePoint(-99, 33, 4326));
SELECT RecoverGeometryColumn('a_minimal', 'geometry', 4326, 'POINT');
SELECT ExportSHP('a_minimal', 'geometry', '/your_path/a_minimal_shp' , 'UTF-8') AS export_SHP;
In QGIS:
ExportDBF() does not suffer the same fate:
SELECT ExportDBF('a_minimal', '/your_path/a_minimal_dbf.dbf' , 'UTF-8') AS export_DBF;
In QGIS:
sandro furieri
> In the following snippet, having two negative values less than 1 in
> the column results in those values being exported as 0/NULL/empty
> (depending on the client used to open the shapefile) when using
> ExportSHP() function:
>
>
congratulations, you just discovered a new bug.
The amazing thing is that it is caused by a piece of C code that was
written almost 20 years ago and during all this time no one had ever
noticed the issue.
It all stems from the fact that numeric values in a DBF file must be
represented in textual and not binary form, and that all values in
the same column must have a fixed length declared in advance.
After all, it's well known that SHP is an archaic and obsolete
format full of defects; this one of the rudimentary management
of numeric values is just one of many.
The crudest approach to the problem is to assume that all numeric
values have the maximum size allowed by the DBF format, but this
inevitably wastes space unnecessarily.
SpatiaLite wants to be a little smarter, and so it tries to
estimate in advance the strictly necessary space for each
numeric column.
But this is precisely where the trap you have fallen into comes
into play; to represent a negative number in text form, an extra
byte is needed for the minus sign.
This was precisely what prevented the negative values in your
example from being exported into the DBF.
A small note: it all depends on the statistical distribution of
the values. In many cases, even negative values were exported
without problems, which explains why in about 20 years no one
had ever noticed the flaw.
Values in your example are all very small [< abs(1.0)] and this
triggered the bug.
> ExportDBF() does not suffer the same fate
>
in this case SpatiaLite does nothing to save space: all numeric
values always occupy the maximum space allowed by the DBF format.
Bottom line: I've already identified and fixed the bug, and
the fix will be included in the next version that will be
released.
best regards.
Sandro