Reducing shapefile size from PostgreSQL using ogr2ogr

Question

I have a PostGIS database created from an old AutoCAD system. Because of this, every column is type varchar(254), even though actual data lengths are small. I need to export tables as shapefiles, and would like to minimize the extra space created in the shapefile due to the large column fields, in order to reduce the overall size of the .dbf file. Is there any way to reduce the size of fields when utilizing ogr2ogr to convert PostgreSQL data to Esri shapefiles?

• Vince: dBase has a 4000 byte record limit, which you'd overwhelm with 16 254-width string fields.
• Hugh_Kelley: consider checking the length of the longest string in each column with a query like select max(LENGTH(my_column)) from my_table ;

Answer 1

You can shrink the shapefile that ogr2ogr has created. Read https://gdal.org/drivers/vector/shapefile.html#field-sizes

It is also possible to force a resize of the fields to the optimal width by issuing a SQL ‘RESIZE ’ via the datasource ExecuteSQL() method. This is convenient in situations where the default column width (80 characters for a string field) is bigger than necessary.

Usage example with ogrinfo

ogrinfo -sql "RESIZE test" test.shp