[Gdal-dev] shp,dbf export and field (type,length) problem

[Lionel Bargeot]

Hi all,

I don't know it is the appropriate mailing list. I've already post my 
message in the postgis ML, but perhap's it's more ogr related than 
postgis. That's the reason why I talk about pgsql2shp (you can forget it).

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
 --------------+----------------
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

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/