[postgis-devel] Attributes size (was: shp2pgsql -> pgsql2shp problem)

[strk at refractions.net]

On Mon, Oct 03, 2005 at 07:13:05AM -0400, Stephen Woodbridge wrote:
> 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.

You're assumption is right, max width is extracted from actual values.

The attached patch makes pgsql2shp use the length limits set to char
and varchar attributes, if any. And shp2pgsql set length limits for
dbf attributes of type STRING.

Before committing, I'd like to gather some feedback as there might
be cases of failures due to malformed shapefiles. These cases
might be handled by using the -p (prepare) switch and manually
editing the receiving table structure or adding yet another switch
to avoid setting limits on STRING attributes.

Comments welcome.
The patch applies to current CVS (head branch).

--strk;
-------------- next part --------------
Index: pgsql2shp.c
===================================================================
RCS file: /home/cvs/postgis/postgis/loader/pgsql2shp.c,v
retrieving revision 1.78
diff -U2 -r1.78 pgsql2shp.c
--- pgsql2shp.c	22 Jul 2005 19:15:28 -0000	1.78
+++ pgsql2shp.c	3 Oct 2005 13:04:58 -0000
@@ -2415,5 +2415,6 @@
 	if ( schema )
 	{
-		sprintf(query, "SELECT a.attname, a.atttypid, a.attlen FROM "
+		sprintf(query, "SELECT a.attname, a.atttypid, a.attlen, "
+			"a.atttypmod FROM "
 			"pg_attribute a, pg_class c, pg_namespace n WHERE "
 			"n.nspname = '%s' AND a.attrelid = c.oid AND "
@@ -2424,5 +2425,6 @@
 	else
 	{
-		sprintf(query, "SELECT a.attname, a.atttypid, a.attlen FROM "
+		sprintf(query, "SELECT a.attname, a.atttypid, a.attlen, "
+			"a.atttypmod FROM "
 			"pg_attribute a, pg_class c WHERE "
 			"a.attrelid = c.oid and a.attnum > 0 AND "
@@ -2472,5 +2474,5 @@
 	{
 		int j;
-		int type, size;
+		int type, size, mod;
 		char *fname; // pgsql attribute name
 		char *ptr;
@@ -2480,4 +2482,5 @@
 		type = atoi(PQgetvalue(res, i, 1));
 		size = atoi(PQgetvalue(res, i, 2));
+		mod = atoi(PQgetvalue(res, i, 3));
 
 //printf( "A: %s, T: %d, S: %d\n", fname, type, size);
@@ -2628,7 +2631,18 @@
 		else if(size == -1)
 		{
-			size = getMaxFieldSize(conn, schema, table, fname);
-			if ( size == -1 ) return 0;
-			if ( ! size ) size = 32; // might 0 be a good size ?
+			if ( (type == 1042 || type == 1043) && mod != -1 )
+			{
+				size = mod-3; // 4 is header size, we
+				              // keep 1 for terminating 
+				              // NULL.
+			}
+			else
+			{
+				size = getMaxFieldSize(conn, schema,
+					table, fname);
+				if ( size == -1 ) return 0;
+				if ( ! size ) size = 32;
+				// might 0 be a good size ?
+			}
 		}
 //printf( "FIELD_NAME: %s, SIZE: %d\n", field_name, size);
Index: shp2pgsql.c
===================================================================
RCS file: /home/cvs/postgis/postgis/loader/shp2pgsql.c,v
retrieving revision 1.98
diff -U2 -r1.98 shp2pgsql.c
--- shp2pgsql.c	3 Oct 2005 07:45:58 -0000	1.98
+++ shp2pgsql.c	3 Oct 2005 13:04:58 -0000
@@ -532,5 +532,6 @@
 			if(type == FTString)
 			{
-				printf ("varchar");
+				// do not need space for terminating NULL
+				printf ("varchar(%d)", field_width-1);
 			}
 			else if(type == FTInteger)