[Gdal-dev] ogr2ogr export from postgis to esri incorrectly exports attributes

[Dan Moore]

Hi folks,

First off, I'm pretty new to this toolset, so please forgive me if I use 
incorrect terminology.

I'm using ogr2ogr to turn some data in a PostGIS database (8.1.4) into 
an ESRI shapefile.  The data has about 25 fields, and when I open up the 
dbf file in excel, they are exported incorrectly.  Lat/lngs are exported 
correctly, but the other attributes are not.  All the values are there, 
but they are offset.  Some are smashed together.  This is also apparent 
when I view the exported ESRI file in QGIS (0.8).

For example, I'll have fields (| separates fields)
lat|lng|address|beds|bath|garage|heating|remarks|listprice
and the values don't align:
2.3|3.4|222 Canyon Blvd|2|2|||nice garagecentral heating||remarks...|123

Here you can see that the garage and heating sections were smashed 
together, and some extra fields were put in before the garage data. 
This happens after the twelfth column in the export.  The last few 
fields are not in the export (they're pushed off by the blank columns).

This happens whether or not I have a where clause on this export.  I 
also tried exporting a few other tables in this database, and the 
attribute shifting didn't happen to either one.  Neither of those tables 
had as many columns as this one, however.

Here's the output of the command when I run it with --debug on:
$ ogr2ogr --debug on -where " dataset_id = 25 " -f "ESRI Shapefile" 
out.shp PG:"user=user dbname=db password=pass" data
OGR_PG: DBName="db"
OGR_PG: PostSIS version string: '1.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1' 
-> '1.1'
OGR_PG: POSTGIS_VERSION=1.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
OGR_PG: Layer 'data' geometry type: POINT:Point, Dim=2
OGR_PG: Layer 'roads' geometry type: MULTILINESTRING:Multi Line String, 
Dim=2
OGR: OGROpen(PG:user=user dbname=db password=pass/xxxxx) succeeded as 
PostgreSQL.
OGR_PG: PQexec(DECLARE OGRPGLayerReader CURSOR for SELECT 
AsEWKT("the_geom"), "address", "baths", "bedrooms", "city", 
"closeddate", "construction", "contractdate", "coolsystem", 
"dataset_id", "daysonmarket", "garagedesc", "heatsystem", "latitude", 
"level", "listdate", "listprice", "listpriceorig", "liststatus", 
"longitude", "lotsize", "mlsnumber", "offmarketdate", 
"percentoflistprice", "precision", "privatepool", "remarks", 
"salesprice", "schooldistrictelem", "schooldistricthigh", "schoolelem", 
"schoolhigh", "schooljunior", "sqfttotal", "state", "subdivision", 
"yearbuilt", "zip" FROM "data" WHERE  dataset_id = 25  )
PG: 11157 features read on layer 'data'.

However, an export to mapinfo seems to work (the attributes are all 
there), and an ogr2ogr from the exported mapinfo file to an ESRI file 
seems to preserve the attributes.  So I can use this workaround, but 
would prefer to understand the real problem.  (As an aside, the mapinfo 
export gives this error message:
ERROR 1: IMapInfoFile::CreateField() called with unsupported field type 
9. Note that Mapinfo files don't support list field types.)

I googled against this list, but didn't find anything that referenced 
this particular problem.

I couldn't figure out the version of ogr2ogr, but this is at the bottom 
of the man page:
GDAL                       13 Apr 2006                 ogr2ogr(1)

Has anyone seen such behavior?  Can you recommend any other debugging 
steps (can I turn on debugging more)?  Removing columns and seeing what 
happens? Should I try to upgrade the GDAL tools?  Any other suggestions 
are welcome.

Thanks,
Dan