PostGIS (and possibly other SQL-based sources) varchar field lengths ignored

[kyngchaos@…]

When reading a PostGIS/Postgres layer or table, varchar field lengths are ignored.  Varchar fields have a 
length, the same as fixed char fields.  It's just a matter of whether the data is stored padded with 
spaces or not.

As a test, in a Postgres table with varchar fields, ogrinfo -al -so returns String(0.0) as the type for all 
varchar fields.  When a PostGIS layer is translated to shapefile, for example, which only has fixed length 
char fields, varchar fields are all translated as char(80), regardless of their max length.

After a little poking around, I was able to patch the pg driver to handle the length of varchar fields.  
This diff is for GDAL release 1.3.1, ogr/ogrsf_frmts/pg/ogrpgtablelayer.cpp:

--- ogrpgtablelayer-old.cpp	2005-09-25 23:37:17.000000000 -0500
+++ ogrpgtablelayer.cpp	2006-01-12 22:40:46.000000000 -0600
@@ -320,11 +320,11 @@
             continue;
         }
 
-        if( EQUAL(pszType,"varchar") || EQUAL(pszType,"text") )
+        if( EQUAL(pszType,"text") )
         {
             oField.SetType( OFTString );
         }
-        else if( EQUAL(pszType,"bpchar") )
+        else if( EQUAL(pszType,"bpchar") || EQUAL(pszType,"varchar") )
         {
             int nWidth;
 
@@ -333,6 +333,8 @@
             {
                 if( EQUALN(pszFormatType,"character(",10) )
                     nWidth = atoi(pszFormatType+10);
+                else if( EQUALN(pszFormatType,"character varying(",18) )
+                    nWidth = atoi(pszFormatType+18);
                 else
                     nWidth = 0;
             }

A similar thing should be done for other SQL-based drivers.  I've seen that the MySQL driver also 
returns 0 lengths for varchar fields.  And others may also have the problem - OCI, ODBC, Sqlite.

[warmerdam]

William, 

I have applied the patch. 

Thanks!

I have checked the ODBC and mysql driver and they don't seem to have the
same issue. 

[kyngchaos@…]

Thanks.

I checked the mysql driver by ogrinfo -al -so and it also shows String (0.0) for varchar fields.  I haven't 
been able to get the mysql ODBC connector to work, and I don't have any other appropriate ODBC DBs to 
test, so I haven't tried that yet.

But, the PostGIS fix is what I really needed, and it looks like the patch passes your inspection, so I'll leave 
it at that.

[warmerdam]

William, 

The mysql code looks like this:

        else if( EQUAL(pszType,"varchar") 
                 || (strlen(pszType)>3 && EQUAL(pszType+strlen(pszType)-4,"text"))
                 || EQUAL(pszType+strlen(pszType)-4,"enum") 
                 || EQUAL(pszType+strlen(pszType)-4,"set") )
        {


            oField.SetType( OFTString );
            char ** papszTokens;

            papszTokens = CSLTokenizeString2(pszType,"(),",0);
            
            /* width is the second */
            oField.SetWidth(atoi(papszTokens[1]));

            CSLDestroy( papszTokens );
            oField.SetType( OFTString );
        }

It would seem this should pick up a width for the field if the
information is available.  

[kyngchaos@…]

Got it - that's in CVS, right?  It looks like that was added since 1.3.1.  Cool.  Maybe it's time I started 
looking at building a CVS copy again...  (and I probably should have checked CVS before complaining 
about the pg driver, eh ^_^)