[postgis-users] shp2pgsql -> pgsql2shp problem

[Stephen Woodbridge]

strk at refractions.net wrote:
> Stephen, no way to specify dbf attribute sizes currently.
> Integer columns are output with a length of 16.
> Floating values with a length of 32.

OK, I can probably live with the Integer and Floating column changes, 
but the character column width are also changing. When I load and 
extract a shapefile the character columns widths change and they seem to 
change a different amount for each file loaded and extracted. I am 
assuming that there is a query to fine the max width of the data and to 
set the width of the column to the max. This is great if it is an 
option, but if you try to extract a province worth of data for each 
province then the column width varies with each file and you can not use 
the data in a mapserver tileindex.

notice the character column widths after extraction and before insertion 
  below.

-Steve

> Why do you find they are not the same as their sql definitions ?
> 
> --strk;
> 
> 
> 
> On Sun, Oct 02, 2005 at 12:30:58AM -0400, Stephen Woodbridge wrote:
> 
>>Hi all,
>>
>>I just tried to use shp2pgsql and pgsql2shp to rename some columns in 
>>Canadian hydrology shpfiles. I seems they named the original columns
>>"xxCART_" and "xxCART_ID" where xx is the province abbreviation. Each 
>>province's shapefile has different column names. So you can't build a 
>>mapserver tileindex. So I wrote the following bash script to use postgis 
>>to rename the columns:
>>
>>mkdir ../p-new
>>for x in ab bc mb nb nf ns nt nu on pe qc sk yt ; do
>>y=${x}drcarto_p
>>echo $y
>>shp2pgsql -c -W LATIN1 $y $y | psql -U pgsql -h localhost temp > /dev/null
>>echo "alter table $y rename ${x}cart_ to cart_;" | psql -U pgsql -h 
>>localhost temp
>>echo "alter table $y rename ${x}cart_id to cart_id;" | psql -U pgsql -h 
>>localhost temp
>>(cd ../p-new ; pgsql2shp -f $y -u pgsql -h localhost -g the_geom temp $y )
>>echo "drop table $y;" | psql -U pgsql -h localhost temp
>>done
>>
>>This works great EXCEPT the output column widths are not the same as the 
>> input column widths. See below! Is there a way to force this to be the 
>>same? Why are they not the same as their sql definitions?
>>
>>-Steve
>>
>>woodbri at linus:/u/data/canada/hydro/p-new$ dbfdump -info abdrcarto_p
>>Filename:       abdrcarto_p.dbf
>>Version:        0x03 (ver. 3)
>>Num of records: 3165
>>Header length:  353
>>Record length:  180
>>Last change:    1995/7/26
>>Num fields:     10
>>Field info:
>>Num     Name            Type    Len     Decimal
>>1.      AREA            N       32      10
>>2.      PERIMETER       N       32      10
>>3.      CART_           N       16      0
>>4.      CART_ID         N       16      0
>>5.      F_CODE          C       5       0
>>6.      HYC             N       16      0
>>7.      CGNDB_EN        C       5       0
>>8.      CGNDB_FR        C       5       0
>>9.      NAME_EN         C       26      0
>>10.     NOM_FR          C       26      0
>>woodbri at linus:/u/data/canada/hydro/p-new$ dbfdump -info ../p/abdrcarto_p 
>>
>>Filename:       ../p/abdrcarto_p.dbf
>>Version:        0x03 (ver. 3)
>>Num of records: 3165
>>Header length:  353
>>Record length:  261
>>Last change:    2003/6/20
>>Num fields:     10
>>Field info:
>>Num     Name            Type    Len     Decimal
>>1.      AREA            N       18      5
>>2.      PERIMETER       N       18      5
>>3.      ABCART_         N       11      0
>>4.      ABCART_ID       N       11      0
>>5.      F_CODE          C       5       0
>>6.      HYC             N       11      0
>>7.      CGNDB_EN        C       5       0
>>8.      CGNDB_FR        C       5       0
>>9.      NAME_EN         C       88      0
>>10.     NOM_FR          C       88      0
>>_______________________________________________
>>postgis-users mailing list
>>postgis-users at postgis.refractions.net
>>http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>