[postgis-devel] dbf attributes size

[strk at refractions.net]

On Mon, Oct 24, 2005 at 05:27:13PM +0200, Markus Schaber wrote:
> 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.

Wait a second, there should be no loop in fact:

	int2->DBF_6->int2

The DBF_SIZE range is inclusive.
The problem is that shp->sql->shp usually produces a bigger shape
due to conversions:

	DBF_2->int2->DBF_6
	DBF_12->int8->DBF_20

> 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?

This would not be straightforward for floats, where text representation
does not always represent actual precision, unless we always use
the numeric() type and hack with atttypmod value...

--strk;

> 
> Markus
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel