[Gdal-dev] Text field size ogr2ogr PostgreSQL to MIF/MID

[Frank Warmerdam]

Marc Jacquin wrote:
> Hi all,
> 
> I am using ogr2ogr to convert a PG table into MIF/MID. 
> 
> The table fields are varchar(x) or char(x) type and the field width is never
> more than let say 50.
> 
> It seems that ogr2ogr forces text fields to be 254 wide. But when you get
> more than 20 fields the record size exceeds the 4 K limit of MapInfo 6.
> 
> Is there a PG usable type so that ogr2ogr would keep the width for
> exporting?

Marc,

It looks like the CHARACTER(n) field type in Postgres will be treated by
OGR as a field with a particular width.  The code that decides whether
to associate a width with text fields in the driver looks like this:


         pszType = PQgetvalue(hResult, iRecord, 1 );
         pszFormatType = PQgetvalue(hResult,iRecord,3);

         ...

         else if( EQUAL(pszType,"bpchar") || EQUAL(pszType,"varchar") )
         {
             int nWidth;

             nWidth = atoi(PQgetvalue(hResult,iRecord,2));
             if( nWidth == -1 )
             {
                 if( EQUALN(pszFormatType,"character(",10) )
                     nWidth = atoi(pszFormatType+10);
                 else if( EQUALN(pszFormatType,"character varying(",18) )
                     nWidth = atoi(pszFormatType+18);
                 else
                     nWidth = 0;
             }
             oField.SetType( OFTString );
             oField.SetWidth( nWidth );
         }

I'm a bit confused about the distinction between "type" and "format
type".  The pszType is the typname field from the pg_type table. while
the pszFormatType is the result of calling the SQL format_type() function
for the attribute type id and attribute type "mod".  So I don't really
know how this all corresponds back to actual SQL declarations for fields.

We do often run into this problem though, of fields in one datasource
having to fixed length associated with them, but having to write to
another format where explicit lengths are required for everything.  Usually
we do as in mapinfo which is to pick the widest width supported but at
a substantial cost.

I think it would be desirable to have a commandline switch to ogr2ogr
(perhaps supported by a library function) to do a first pass scan of
the feature set to identify the longest value that occurs for each
variable width text field and then use that for the width (or perhaps
a width that is modestly larger than the longest current occurance).

It might also be prudent to add a command line switch to ogr2ogr to
support overriding widths for particular fields.

If you would be willing to file an enhancement request in the GDAL/OGR
bugzilla capturing all this, I'll see if something can be done ...
eventually.  For now, you might dig into the CHARACTER(n) type in
postgres to see if it helps.

Best regards,
-- 
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | President OSGeo, http://osgeo.org