What is the easiest way to avoid shapefile columns being imported as PostGIS `numeric(p,s)` data type?

Question

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 int or double precision, they are importing as numeric(p,s).

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 numeric(19,8).

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):

1. Is there any switch or setting in shp2pgsql or ogr2ogr which can override this behavior, similar to the -i switch that forces shp2pgsql to avoid creating bigint columns?
2. Is there a way to automate the cleanup after import? That is, does someone have ideas for a script that could find all the numeric columns in a table and force them to double precision or int?
3. 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 numeric?

Answer 1

The number width/precision is specified in the DBF file, as support by GDAL/OGR with get/set width/precision (i.e. see API).

Looking at the documentation for the PostgreSQL / PostGIS driver, there is a PRECISION layer creation option:

This may be "YES" to force new fields created on this layer to try and represent the width and precision information, if available using NUMERIC(width,precision) or CHAR(width) types. If "NO" then the types FLOAT8, INTEGER and VARCHAR will be used instead. The default is "YES".

Try -lco PRECISION=NO with ogr2ogr to see if it suites your preferences.

Answer 2

For the second point you can use the Information_schema and select the needed columns.

"SELECT column_name, data_type FROM information_schema.columns" and use this information in a loop with an ALTER TABLE statement together with SET DATA TYPE to change the types. This of course needs to be inside a loop for all tables which you also get from information_schema with "SELECT table_name FROM information_schema.tables" - Its not the fastest way and also includes some work for the correct pgsql procedure.

Answer 3

I would take OGR (ogr2ogr) and set the layer creation option COLUMN_TYPES: -lco COLUMN_TYPES field1=INTEGER,field1=FLOAT8. That's another layer creation option as Mike T suggested above.

In general, I would consider switching to GeoPackage since Shapefiles are deprecated and have deficiencies like field names limited to 10 chars.