[postgis-users] shp2pgsql -> pgsql2shp problem

[strk at refractions.net]

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.

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