Opened 18 years ago
Last modified 18 years ago
#1037 closed defect (fixed)
PostGIS (and possibly other SQL-based sources) varchar field lengths ignored
Reported by: | Owned by: | warmerdam | |
---|---|---|---|
Priority: | high | Milestone: | |
Component: | OGR_SF | Version: | unspecified |
Severity: | normal | Keywords: | |
Cc: |
Description
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.
Change History (4)
comment:2 by , 18 years ago
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.
comment:3 by , 18 years ago
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.
comment:4 by , 18 years ago
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 ^_^)
Note:
See TracTickets
for help on using tickets.