[postgis-devel] dbf attributes size

[Markus Schaber]

Hi, Strk,

strk at refractions.net wrote:
> Hello there, I'm taking a look at shp2pgsql and pgsql2shp
> trying to make numerical attribute size handling stricter.
[...]
> For what I can tell the correct mapping between integer DBF attributes
> and sql types should be as follows:
> 
> 	DBF_SIZE	SQL_TYPE	LONGEST_TEXT_REP
> 	0-6		int2		-32768
> 	7-11		int4		-2147483648
> 	12-20		int8		-9223372036854775808
> 	21-x		numeric(size,0)
> 
> Does anyone see any drawback in implementing this mapping ?

This looks good.

However, I think it is difficult to avoid "type growing loops" if one
repeatedly dumps/reloads the data between SQL and Shape file.

The problem is that, in both ways, we have to be conservative to enshure
that the target types are large enough.

So we get a cyle from int2->DBF_6->int4->DBF_11->int8->DBF_20->numeric.

For shp2pgsql, the -p and -a switches provide a manual way to work
around this, as one can "hand-craft" the sql fields to the real needs.

Maybe we should implement an option to probe the field sizes by scanning
the whole table / shape file and look for the real data sizes?

Markus