Preserve scale and precision when generating shape file with pgsql2shp

[hopfgartner]

This patch uses the precision and scale information from the database server in order to write the shape file. The advantage is twofold: a major control on the shape file format and less disk space used when writing DECIMALs/NUMERICs which have a precision of less then 32 digits.

The patch is against branch 1.5. If the approach is ok, I can prepare a patch for trunk.

[hopfgartner]

Patch for current trunk

[hopfgartner]

Test case

[strk]

With your patch applied, I tried the following:

./pgsql2shp strk "select 1.2345678901234::numeric(12,12)"

The numeric(12,12) form tells PostgreSQL that there are a total of 12 significant digits, and all of them are in the decimal part. Ref: ​http://www.postgresql.org/docs/9.1/static/datatype-numeric.html

In output (with your patch), I get (checked with dbfview -e):

Field Name      Type    Length  Decimal Pos
Numeric           N        12      12
Numeric    : 0.1234567890

You can see the actual value is truncated to 12 _characters_ rather than significant digits. Without your patch, I get:

Field Name      Type    Length  Decimal Pos
Numeric           N        32      10
Numeric    : 0.123456789012

[strk]

Changing the code with this:

+               else if (pgfieldtype == 1700)
+               {
+                       if (pgfieldscale == 0)
+                               dbffieldtype = FTInteger;
+                       else
+                               dbffieldtype = FTDouble;
+
+                       dbffieldsize = pgfieldprec;
+                       if (pgfieldscale > 0) {
+                         dbffieldsize += 2; /* the decimal digit separator and terminating null ? */
+      }
+                       dbffielddecs = pgfieldscale;
+               }

Seems to handle better a few cases. Testcase needs to try a few combinations and values.

[strk]

Also, it might be nice to have shp2pgsql also honour numeric precision on import

[robe]

pramsey it seems you have a lot on your plate, none of which you have committed yet. Are you sure you are going to get to this. I'd like to push this out to focus on more critical things.

[strk]

It's a pity to drop this, but if the original reporter doesn't reply we're lacking a stakeholder. I'm changing to FundMe milestone