Opened 10 years ago

Last modified 12 months ago

#2850 new enhancement

Preserve scale and precision when generating shape file with pgsql2shp

Reported by: hopfgartner Owned by: strk
Priority: medium Milestone: PostGIS Fund Me
Component: utils/pgsql2shp Version: 2.1.x
Keywords: pgsql2shp Cc:

Description

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.

Attachments (3)

postgis-preserve-numeric.patch (3.0 KB ) - added by hopfgartner 10 years ago.
loader.pgsql2shp-core.c.diff (2.9 KB ) - added by hopfgartner 10 years ago.
Patch for current trunk
regress.dumper.numeric.tar.gz (354 bytes ) - added by hopfgartner 10 years ago.
Test case

Download all attachments as: .zip

Change History (15)

by hopfgartner, 10 years ago

comment:1 by strk, 10 years ago

Milestone: PostGIS 2.2.0

by hopfgartner, 10 years ago

Patch for current trunk

by hopfgartner, 10 years ago

Test case

comment:2 by strk, 10 years ago

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

comment:3 by strk, 10 years ago

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.

comment:4 by strk, 10 years ago

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

comment:5 by robe, 9 years ago

Milestone: PostGIS 2.2.0PostGIS 2.3.0

comment:6 by robe, 8 years ago

Milestone: PostGIS 2.3.0PostGIS 2.4.0

comment:7 by pramsey, 7 years ago

Owner: changed from mcayland to strk

comment:8 by robe, 7 years ago

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.

comment:9 by robe, 7 years ago

Milestone: PostGIS 2.4.0PostGIS 2.5.0

comment:10 by robe, 6 years ago

Milestone: PostGIS 2.5.0PostGIS 3.0.0

comment:11 by strk, 5 years ago

Milestone: PostGIS 3.0.0PostGIS Fund Me

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

comment:12 by strk, 12 months ago

Component: utils/loader-dumperutils/pgsql2shp
Note: See TracTickets for help on using tickets.