Considering the number of shapefiles I've imported to PostGIS (using shp2pgsql or ogr2ogr), I'm a little surprised I haven't come across this before. But I have a shapefile that has numeric columns with unnecessarily generous precision and scale settings, and instead of importing as
double precision, they are importing as
The shapefile has several DBF columns defined as
COLUMN1,N,10,0, and instead of importing as
int they are importing as
numeric(10,0). There are also some floating point columns which are defined to accommodate the maximum possible precision, e.g.
COLUMN2,N,19,8 which instead of importing as
double precision is imported as
I have a number of related questions about this behavior. First, regarding understanding why this is happening, is this behavior documented anywhere? I found some mailing list discussions, but nothing in documentation about what precision and scale will force the switch to the
numeric data type.
Then, does anyone have suggestions for handling this that do not involve manually altering the column types in PostGIS after import? Approaches I can think of (but feel free to suggest others):
- Is there any switch or setting in shp2pgsql or ogr2ogr which can override this behavior, similar to the
-iswitch that forces shp2pgsql to avoid creating
- Is there a way to automate the cleanup after import? That is, does someone have ideas for a script that could find all the
numericcolumns in a table and force them to
- Is there a way to automate altering the data types in the shapefile/DBF before import to reduce the precision enough to avoid importing as