[postgis-users] shp2pgsql - dbf attribute size

[Tom Glancy]

We are seeing ERROR:  value "6906401929" is out of range 
for type integer from Postgres when using shp2pgsql to load 
shapefiles. Here is what ArcView reports for field properties:

Alias          Type           Width Decimal
-------------------------------------------------------
Shape          FIELD_SHAPEPOLY  8      0
[snip]
Geosect        FIELD_DECIMAL  10     0
Georang        FIELD_DECIMAL  8      0
[snip]

Values for Geosect:
ERROR:  value "6906401929" is out of range for type integer
ERROR:  value "6905901328" is out of range for type integer
ERROR:  value "4515704005" is out of range for type integer

shp2pgsql defined geosect as "int4", and the insert failed.

We saw similar errors for fields of type FIELD_DECIMAL defined
with Width=5, Decimal=0 when values exceeded about 68000 (we 
didn't try to find the exact value where it failed...)

In shp2pgsql.c, the CreateTable function, field widths are 
mapped to data types. It appears that int2, int4 and int8 
are off by a length of one. We made these changes to 
shp2pgsql.c and are no longer seeing the errors:

$ diff ./shp2pgsql.c ../../postgis-1.3.1-original/loader/shp2pgsql.c
539c539
<                       else if  ( field_width < 5 )
---
>                       else if  ( field_width <= 5 )
543c543
<                       else if  ( field_width < 10 )
---
>                       else if  ( field_width <= 10 )
547c547
<                       else if  ( field_width < 19 )
---
>                       else if  ( field_width <= 19 )


Here is some previous discussion...

> strk at refractions.net strk at refractions.net
> Mon Oct 24 09:04:02 PDT 2005
> 
[snip]
> Oops... you're right, this means mapping needs to be different 
> depending on direction:
> 
>        [LOADING]
>        DBF_SIZE        SQL_TYPE        LONGEST_TEXT_REP
>        0-5             int2            -99999
>        6-10            int4            -9999999999
>        11-19           int8            -9999999999999999999
>        20-x            numeric(size,0)
> 
>        [DUMPING]
>        SQL_TYPE        DBF_SIZE        LONGEST_TEXT_REP
>        int2            6               -32768
>        int4            11              -2147483648
>        int8            20              -9223372036854775808
>        20-x            numeric(size,0)


Regards,
Tom

 

---------------------------------------
Tom Glancy
tom.glancy-at-dnr.state.mn.us ( mailto:tom.glancy at dnr.state.mn.us )
Minnesota Department of Natural Resources
---------------------------------------