ogr2ogr from PG to SHP changes data-type in .DBF

Question

UpDate:

Yes, that was it!

Like mloskot said, the whole tabel needs to be dumped. selecting fields with sql loses information about fuield widths.

Thanks, that fixed it!

Hi All,

I have a problem with ogr2ogr. I'm trying to dump a PostgreSQL tabel to a Shapefile. ogr2ogr seems to change the size of data-types like integer and char.

Here's part of the DB structure that the Shapefile set was read into:

Table "test"
nd_1 - numeric(8,0)
nd_2 - numeric(2,0)
nd_3 - numeric(2,0)
nd_9 - character varying(60)

and it looks correctly.

Here's where the fun begins:

ogr2ogr air5000.shp "PG: [...]" sql 'select
CAST(nd_1 AS INTEGER),
CAST(nd_2 AS INTEGER),
CAST(nd_3 as INTEGER),
CAST(ND_9 AS CHARACTER VARYING(60))
from test' ;

then

dbview -e test.dbf 

Field Name Type Length Decimal Pos
nd 1 - N - 11 - 0
nd 2 - N - 11 - 0
nd 3 - N - 11 - 0
nd 9 - C - 80 - 0

num-length goes form 8,2,2 to 11, char length goes from 10 to 80.

I read that these are the defaults if width (length) is not specified.

How can i specify the width?

I tried every combo with or without casting, like this:

select
CAST(nd_1 AS NUMERIC),
CAST(nd_2 AS NUMERIC),
CAST(nd_3 as NUMERIC),
CAST(ND_9 AS CHARACTER VARYING(60))

which gives in dbview:

Field Name Type Length Decimal Pos
nd 1 - N - 24 - 15
nd 2 - N - 24 - 15
nd 3 - N - 24 - 15
nd 9 - C - 80 - 0

no casting (select * from test) gives

Field Name      Type    Length  Decimal Pos
nd 1 - N - 24 - 15
nd 2 - N - 24 - 15
nd 3 - N - 24 - 15
nd 9 - C - 80 - 0

Nowhere near the originals on any occasion.

debugfile [--debug on] gives nothing special IMHO, here is the output of the 3 ogr2ogr command i tried:

PG: PQexec(DECLARE executeSQLCursor CURSOR for select CAST(nd_1 AS INTEGER), CAST(nd_2 AS INTEGER), CAST(nd_3 as INTEGER), CAST(ND_9 AS CHARACTER VARYING(60)) from test)
PG: PQexec(DECLARE OGRPGResultLayerReader CURSOR for select CAST(nd_1 AS INTEGER), CAST(nd_2 AS INTEGER), CAST(nd_3 as INTEGER), CAST(ND_9 AS CHARACTER VARYING(60)) from test)
PG: PQexec(DECLARE OGRPGResultLayerReader CURSOR for select CAST(nd_1 AS INTEGER), CAST(nd_2 AS INTEGER), CAST(nd_3 as INTEGER), CAST(ND_9 AS CHARACTER VARYING(60)) from test)
PG: 2 features read on layer 'sql_statement'.

PG: PQexec(DECLARE executeSQLCursor CURSOR for select CAST(nd_1 AS NUMERIC), CAST(nd_2 AS NUMERIC), CAST(nd_3 as NUMERIC), CAST(ND_9 AS CHARACTER VARYING(60)) from test)
PG: PQexec(DECLARE OGRPGResultLayerReader CURSOR for select CAST(nd_1 AS NUMERIC), CAST(nd_2 AS NUMERIC), CAST(nd_3 as NUMERIC), CAST(ND_9 AS CHARACTER VARYING(60)) from test)
PG: PQexec(DECLARE OGRPGResultLayerReader CURSOR for select CAST(nd_1 AS NUMERIC), CAST(nd_2 AS NUMERIC), CAST(nd_3 as NUMERIC), CAST(ND_9 AS CHARACTER VARYING(60)) from test)
PG: 2 features read on layer 'sql_statement'.
PG: PQexec(DECLARE executeSQLCursor CURSOR for select * from test)
PG: PQexec(DECLARE OGRPGResultLayerReader CURSOR for select * from test)
PG: PQexec(DECLARE OGRPGResultLayerReader CURSOR for select * from test)
PG: 2 features read on layer 'sql_statement'.

I also tried -dsco precision=no but that did not help either.

Maybe dbview is pulling my leg, but it showed the header of the source .dbf of the Shapefile set correctly.

Anyone any ideas?

EJ

UpDate: hi mloskot,

thanks for your reply and the reformatting, much appreciated!

i'm on GDAL 1.6.3.

i tried specifying the width, but it does not make a difference.

ogr2ogr test.shp "PG: [...] " -sql 'select CAST(nd_1 AS INTEGER), CAST(nd_2 AS numeric(2,0)), CAST(nd_3 as NUMERIC(2,0)) from test' ;

gives:

Field Name  Type    Length  Decimal Pos
nd 1          N    11       0
nd 2          N    24      15
nd 3          N    24      15

yes i tried specifying the fields without casting, like this:

ogr2ogr air5000.shp "PG: [...] "-sql 'select nd_1, nd_2, nd_3, ND_9 from test'

which gives:

 Field Name Type    Length  Decimal Pos
nd 1          N    24      15
nd 2          N    24      15
nd 3          N    24      15
nd 9          C    80       0

trying

integer(field_length)

like

CAST(nd_2 AS INTEGER(2))

does not work, gives error

ERROR 1: ERROR: current transaction is aborted, commands ignored until end of transaction block

maybe my Postgresql and PostGIS version are dated? Postgres = 8.3.5, PostGIS = 1.3.3

i will check Shapelib, i installed it just now.

Answer 1

First, you don't specify what version of GDAL/OGR you use, so it may be hard to diagnose where is the problem. Note, improvements are being applied from version to version.

Yes, you are correct values 11 and 80 for integer width and char width respectively, are default values set by OGR driver for ESRI Shapefile

The problem is that you are using SQL query to fetch only some of the rows form table and this causes OGR PG driver driver does not preserves field widths.

Compare these two outputs:

Query the whole table

$ ogrinfo PG:dbname=test test_ogr
Layer name: test_ogr
Geometry: Point
Feature Count: 1
Extent: (1.000000, 2.000000) - (1.000000, 2.000000)
Layer SRS WKT:
(unknown)
FID Column = id
Geometry Column = geom
n3: String (60.0)
n1: Integer (8.0)
n2: Integer (2.0)
OGRFeature(test_ogr):1
  n3 (String) = abcdefg
  n1 (Integer) = 12345678
  n2 (Integer) = 12
  POINT (1 2)

Same query but with manually specified SQL select

$ ogrinfo PG:dbname=test -sql "SELECT n1, n2, n3, geom FROM test_ogr"
Layer name: sql_statement
Geometry: Unknown (any)
Feature Count: 1
Extent: (1.000000, 2.000000) - (1.000000, 2.000000)
Layer SRS WKT:
(unknown)
Geometry Column = geom
n1: Real (0.0)
n2: Real (0.0)
n3: String (0.0)
OGRFeature(sql_statement):0
  n1 (Real) = 12345678
  n2 (Real) = 12
  n3 (String) = abcdefg
  POINT (1 2)

As you can see, the second option looses field properties completely and it is how OGR works.

Here is the same example of two outputs, but with translation to Shapefile:

Dumping all data from test_ogr table

$ ogr2ogr -f "ESRI Shapefile" test.shp PG:dbname=test test_ogr
$ dbfdump -h test.dbf
Field 0: Type=C/String, Title=`n3', Width=60, Decimals=0
Field 1: Type=N/Integer, Title=`n1', Width=8, Decimals=0
Field 2: Type=N/Integer, Title=`n2', Width=2, Decimals=0
n3                                                                 n1 n2
abcdefg                                                      12345678 12

With use of -sql option and SELECT query:

$ ogr2ogr -f "ESRI Shapefile" test.shp PG:dbname=test -sql "SELECT n1, n2, n3, geom FROM test_ogr"
$ dbfdump -h test.dbf
Field 0: Type=N/Double, Title=`n1', Width=24, Decimals=15
Field 1: Type=N/Double, Title=`n2', Width=24, Decimals=15
Field 2: Type=C/String, Title=`n3', Width=80, Decimals=0
                      n1                       n2 n3
12345678.000000000000000       12.000000000000000 abcdefg

So, in order to preserve all properties exactly, you need to dump all table without specifying SQL query.

By the way, if you are suspicious regardig dbfview, you may try to examine your output Shapefile files (.shp, .dbf) with shpdump and dbfdump utilities from Shapelib. This software uses exactly the same C code which is used in GDAL/OGR to handle Shapefile data.