[postgis-devel] DBF-Column type boolen and date
strk at refractions.net
strk at refractions.net
Wed Jan 11 09:59:54 PST 2006
Attached is a patch that will implement
support for Date and Bool DBF<->pgsql transfer.
I didn't commit yet as I'm concerned about compatibility
of PostgreSQL 'Date' type canonical output and DBF
expectations in a 'Date' field.
Also note the case mismatch for boolean types.
If you happen to have Shapefile (or .dbf) writers/readers
please test them to see how shp2pgsql and pgsql2shp cope
with Date and Boolean fields (does anyone complain about them?).
In particular, PostgreSQL Date canonical input and output would
depend on locale settings, and there's no direct way to specify
locale settings in shp2pgsql and pgsql2shp.
Finally, note that the patch includes patches to the shapelib,
not supporting writing of Date fields up to latest release.
--strk;
On Wed, Jan 11, 2006 at 04:39:08PM +0100, strk at refractions.net wrote:
> [included postgis-devel]
>
> Thanks, here is (one of) the problem(s):
>
> The input has this form:
>
> Id : 1
> Date : 20051130
> Bool : T
>
> This is safely imported in 'date' and 'bool' fields.
>
> When exported, postgresql returns this:
>
> Id : 1
> Date : 2005-11-30
> Bool : t
>
> Attached is the output of pgsql2shp (after shp2pgsql).
> Does your reader chokes ? Please try multiple readers
> if you do have them.
>
> The problem (if it is a problem at all) is that
> we use canonical output of every attribute w/out doing
> any particular check, when exporting. So if the DBF
> format specification expects a specific format for
> dates and boolean we'll need to add a post processing
> based on type.
>
> --strk;
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
--
/"\ ASCII Ribbon Campaign
\ / Respect for low technology.
X Keep e-mail messages readable by any computer system.
/ \ Keep it ASCII.
-------------- next part --------------
Index: loader/dbfopen.c
===================================================================
RCS file: /home/cvs/postgis/postgis/loader/dbfopen.c,v
retrieving revision 1.7
diff -U2 -r1.7 dbfopen.c
--- loader/dbfopen.c 9 Jan 2006 16:40:16 -0000 1.7
+++ loader/dbfopen.c 11 Jan 2006 17:38:20 -0000
@@ -671,4 +671,6 @@
else if( eType == FTString )
psDBF->pachFieldType[psDBF->nFields-1] = 'C';
+ else if( eType == FTDate )
+ psDBF->pachFieldType[psDBF->nFields-1] = 'D';
else
psDBF->pachFieldType[psDBF->nFields-1] = 'N';
@@ -986,7 +988,9 @@
return( FTLogical);
+ else if( psDBF->pachFieldType[iField] == 'D' )
+ return ( FTDate );
+
else if( psDBF->pachFieldType[iField] == 'N'
- || psDBF->pachFieldType[iField] == 'F'
- || psDBF->pachFieldType[iField] == 'D' )
+ || psDBF->pachFieldType[iField] == 'F' )
{
if( psDBF->panFieldDecimals[iField] > 0 )
@@ -1178,5 +1182,5 @@
int DBFWriteAttributeDirectly(DBFHandle psDBF, int hEntity, int iField,
- void * pValue )
+ const void * pValue )
{
Index: loader/pgsql2shp.c
===================================================================
RCS file: /home/cvs/postgis/postgis/loader/pgsql2shp.c,v
retrieving revision 1.81
diff -U2 -r1.81 pgsql2shp.c
--- loader/pgsql2shp.c 9 Jan 2006 16:40:16 -0000 1.81
+++ loader/pgsql2shp.c 11 Jan 2006 17:38:20 -0000
@@ -103,4 +103,5 @@
static void parse_table(char *spec);
static int create_usrquerytable(void);
+static const char *nullDBFValue(char fieldType);
/* WKB functions */
@@ -132,5 +133,5 @@
void skipdouble(byte **c);
void dump_wkb(byte *wkb);
-byte * HexDecode(char *hex);
+byte * HexDecode(const char *hex);
#define WKBZOFFSET 0x80000000
@@ -1939,5 +1940,5 @@
for (j=0; j<nFields; j++)
{
- char *val;
+ const char *val;
byte *v;
size_t junk;
@@ -1949,13 +1950,12 @@
/*
* Transform NULL numbers to '0'
- * This is because the shapelibe
+ * This is because the shapelib
* won't easly take care of setting
* nulls unless paying the acquisition
* of a bug in long integer values
*/
- if ( PQgetisnull(res, residx, j) &&
- ( type_ary[j] == 1 || type_ary[j] == 2 ) )
+ if ( PQgetisnull(res, residx, j) )
{
- val = "0";
+ val = nullDBFValue(type_ary[j]);
}
else
@@ -2617,5 +2617,5 @@
return 0;
}
- type_ary[mainscan_nflds]=1;
+ type_ary[mainscan_nflds]=FTInteger;
mainscan_flds[mainscan_nflds++] = fname;
continue;
@@ -2633,9 +2633,9 @@
11, 0) == -1 )
{
- printf( "error - Field could not "
+ printf( "Error - Ingeter field could not "
"be created.\n");
return 0;
}
- type_ary[mainscan_nflds]=1;
+ type_ary[mainscan_nflds]=FTInteger;
mainscan_flds[mainscan_nflds++] = fname;
continue;
@@ -2653,9 +2653,9 @@
20, 0) == -1 )
{
- printf( "error - Field could not "
+ printf( "Error - Integer field could not "
"be created.\n");
return 0;
}
- type_ary[mainscan_nflds]=1;
+ type_ary[mainscan_nflds]=FTInteger;
mainscan_flds[mainscan_nflds++] = fname;
continue;
@@ -2675,9 +2675,9 @@
if(DBFAddField(dbf, field_name,FTDouble,32,10) == -1)
{
- printf( "error - Field could not "
+ printf( "Error - Double field could not "
"be created.\n");
return 0;
}
- type_ary[mainscan_nflds]=2;
+ type_ary[mainscan_nflds]=FTDouble;
mainscan_flds[mainscan_nflds++] = fname;
continue;
@@ -2685,10 +2685,36 @@
/*
- * date field, which we store as a string so we need
+ * Boolean field, we use FTLogical
+ */
+ if ( type == 16 )
+ {
+ if ( DBFAddField(dbf, field_name, FTLogical,
+ 2, 0) == -1 )
+ {
+ printf( "Error - Boolean field could not "
+ "be created.\n");
+ return 0;
+ }
+ type_ary[mainscan_nflds]=FTLogical;
+ mainscan_flds[mainscan_nflds++] = fname;
+ continue;
+ }
+
+ /*
+ * Date field, which we store as a string so we need
* more width in the column
*/
if(type == 1082)
{
- size = 10;
+ if ( DBFAddField(dbf, field_name, FTDate,
+ 10, 0) == -1 )
+ {
+ printf( "Error - Date field could not "
+ "be created.\n");
+ return 0;
+ }
+ type_ary[mainscan_nflds]=FTDate;
+ mainscan_flds[mainscan_nflds++] = fname;
+ continue;
}
@@ -2732,9 +2758,9 @@
if(DBFAddField(dbf, field_name, FTString, size, 0) == -1)
{
- printf( "error - Field could not "
+ printf( "Error - String field could not "
"be created.\n");
return 0;
}
- type_ary[mainscan_nflds]=3;
+ type_ary[mainscan_nflds]=FTString;
mainscan_flds[mainscan_nflds++] = fname;
}
@@ -2972,8 +2998,8 @@
*/
byte *
-HexDecode(char *hex)
+HexDecode(const char *hex)
{
byte *ret, *retptr;
- char *hexptr;
+ const char *hexptr;
byte byt;
int len;
@@ -3217,4 +3243,29 @@
}
+/* This is taken and adapted from dbfopen.c of shapelib */
+static const char *
+nullDBFValue(char fieldType)
+{
+ switch(fieldType)
+ {
+ case FTInteger:
+ case FTDouble:
+ /* NULL numeric fields have value "****************" */
+ return "****************";
+
+ case FTDate:
+ /* NULL date fields have value "00000000" */
+ return "00000000";
+
+ case FTLogical:
+ /* NULL boolean fields have value "?" */
+ return "?";
+
+ default:
+ /* empty string fields are considered NULL */
+ return "";
+ }
+}
+
/**********************************************************************
* $Log: pgsql2shp.c,v $
Index: loader/shapefil.h
===================================================================
RCS file: /home/cvs/postgis/postgis/loader/shapefil.h,v
retrieving revision 1.4
diff -U2 -r1.4 shapefil.h
--- loader/shapefil.h 1 Dec 2003 20:52:00 -0000 1.4
+++ loader/shapefil.h 11 Jan 2006 17:38:20 -0000
@@ -417,5 +417,6 @@
FTDouble,
FTLogical,
- FTInvalid
+ FTInvalid,
+ FTDate
} DBFFieldType;
@@ -470,5 +471,5 @@
int SHPAPI_CALL
DBFWriteAttributeDirectly(DBFHandle psDBF, int hEntity, int iField,
- void * pValue );
+ const void * pValue );
const char SHPAPI_CALL1(*)
DBFReadTuple(DBFHandle psDBF, int hEntity );
Index: loader/shp2pgsql.c
===================================================================
RCS file: /home/cvs/postgis/postgis/loader/shp2pgsql.c,v
retrieving revision 1.105
diff -U2 -r1.105 shp2pgsql.c
--- loader/shp2pgsql.c 9 Jan 2006 16:40:16 -0000 1.105
+++ loader/shp2pgsql.c 11 Jan 2006 17:38:21 -0000
@@ -333,6 +333,8 @@
if ( val[strlen(val)-1] == '.' ) val[strlen(val)-1] = '\0';
break;
+
case FTString:
case FTLogical:
+ case FTDate:
if ( -1 == snprintf(val, 1024, "%s",
DBFReadStringAttribute(hDBFHandle, row, i)) )
@@ -342,4 +344,5 @@
}
break;
+
default:
fprintf(stderr,
@@ -517,65 +520,60 @@
printf(",\n\"%s\" ", field_names[j]);
- if(hDBFHandle->pachFieldType[j] == 'D' ) /* Date field */
+ if(type == FTString)
{
- printf ("varchar(8)");/*date data-type is not supported in API so check for it explicity before the api call. */
+ /* use DBF attribute size as maximum width */
+ printf ("varchar(%d)", field_width);
}
-
- else
+ else if (type == FTDate)
{
-
- if(type == FTString)
+ printf ("date");
+ }
+ else if (type == FTInteger)
+ {
+ if ( forceint4 )
+ {
+ printf ("int4");
+ }
+ else if ( field_width <= 5 )
{
- /* use DBF attribute size as maximum width */
- printf ("varchar(%d)", field_width);
+ printf ("int2");
}
- else if(type == FTInteger)
+ else if ( field_width <= 10 )
{
- if ( forceint4 )
- {
- printf ("int4");
- }
- else if ( field_width <= 5 )
- {
- printf ("int2");
- }
- else if ( field_width <= 10 )
- {
- printf ("int4");
- }
- else if ( field_width <= 19 )
- {
- printf ("int8");
- }
- else
- {
- printf("numeric(%d,0)",
- field_width);
- }
+ printf ("int4");
}
- else if(type == FTDouble)
+ else if ( field_width <= 19 )
{
- if( field_width > 18 )
- {
- printf ("numeric");
- }
- else
- {
- printf ("float8");
- }
+ printf ("int8");
}
- else if(type == FTLogical)
+ else
{
- printf ("boolean");
+ printf("numeric(%d,0)",
+ field_width);
+ }
+ }
+ else if(type == FTDouble)
+ {
+ if( field_width > 18 )
+ {
+ printf ("numeric");
}
else
{
- printf ("Invalid type in DBF file");
+ printf ("float8");
}
- }
+ }
+ else if(type == FTLogical)
+ {
+ printf ("boolean");
+ }
+ else
+ {
+ printf ("Invalid type in DBF file");
+ }
}
printf (");\n");
- /*create the geometry column with an addgeometry call to dave's function */
+ /* Create the geometry column with an addgeometry call */
if ( schema )
{
More information about the postgis-devel
mailing list