[postgis-users] (shp,dbf) export and field (type,length) problem

[Lionel Bargeot]

Hi all,

I need to tune field types in a dbf output file.
I've just tryed with the two utilities :
- pgsql2shp
- ogr2ogr

I appologize in advance, but my mail is very long.

ogr2ogr is a better solution for me because it accepts a sql query to 
export my data and could avoide me a temporary table creation in my 
database.
But I can't obtain what I need with those utilities. Field length and 
field types in my dbf file are quiet different form my data in the 
database. I've tried a simple output with pgsql2shp and multiple output 
with ogr2ogr using type casting in a query.

In my database, my table look like this :

form=# \d tb_exemple

           Table «public.tb_exemple»

   Colonne    |      Type      | Modificateurs

--------------+----------------+---------------

 ogc_fid      | integer        |

 wkb_geometry | geometry       |

 objectid     | numeric(9,0)   |

 numero       | numeric(4,0)   |

 other_id     | character(9)   |

 id_ilot      | character(10)  |

 an_area      | numeric(19,11) |



----------------------
With pgsql2shp :
----------------------
pgsql2shp -f temp/test.shp -h localhost -g wkb_geometry form tb_exemple

dbfdump -i temp/test.dbf
Filename:       temp/test.dbf
Version:        0x03 (ver. 3)
Num of records: 2
Header length:  225
Record length:  132
Last change:    1995/7/26
Num fields:     6
Field info:

Num     Name            Type    Len     Decimal

1.      OGC_FID         N       16      0

2.      OBJECTID        N       32      10

3.      NUMERO          N       32      10

4.      OTHER_ID        C       9       0

5.      ID_ILOT         C       10      0

6.      AN_AREA         N       32      10

You can understand that dbf file will be to large.
----------------------
With ogr2ogr :
----------------------
simple export :
----------------------
ogr2ogr -f "ESRI Shapefile" temp  PG:"dbname=form user=lionel" -nln test 
-sql "SELECT * from tb_exemple"
dbfdump -i temp/test.dbf
...
Field info:

Num     Name            Type    Len     Decimal

1.      OBJECTID        C       80      0

2.      NUMERO          C       80      0

3.      OTHER_ID        C       80      0

4.      ID_ILOT         C       80      0

5.      AN_AREA         C       80      0

to large for me
------------------------
export with type cast in a query
------------------------
ogr2ogr -f "ESRI Shapefile" temp  PG:"dbname=form user=lionel" -nln test 
-sql "SELECT objectid::smallint, numero::int2, an_area, wkb_geometry 
from tb_exemple";
dbfdump -i temp/test.dbf
...
Field info:
Num     Name            Type    Len     Decimal
1.      OBJECTID        N       11      0
2.      NUMERO          N       11      0
3.      AN_AREA         C       80      0


Well, what I can see, is
- if I have an integer input (size 2, 4 or 8), output will be a 11 
numeric long type
- if I have a numeric input (size don't care), output will be a 80 
character long type

Well, my questions are :
- has anyone already encountered this problem
- is there an option in pgsql2shp or ogr2ogr I haven't seen
- is there a known way to obtain
    4 byte N types in a DBF for a numeric(4,0) in the database
    (19,11) Float types in a DBF for a numeric(19,11) in the db
    etc ....

Thank you very much for reading,


Lionel

-- 
Chargé de mission Sols et Territoire de Bourgogne
CNERTA-ENESAD, 2 rue champs-prevois, batiment grand-champs, 21000 Dijon
03.80.77.28.49
http://www.igcs-stb.org/