[Xastir-checkins] CVS: xastir/src db_gis.c, 1.5, 1.6 db_gis.h, 1.3, 1.4

[Paul J. M. <chi...@us...>]

Update of /cvsroot/xastir/xastir/src
In directory sc8-pr-cvs10.sourceforge.net:/tmp/cvs-serv5014/src

Modified Files:
	db_gis.c db_gis.h 
Log Message:
Simple schema insert added for Postgresql/postgis.  Tested with Postgresql
8.2 and postgis 1.2.1.   

Simple station records can now be written to MySQL and Postgresql/Postgis 
databases, but xastir can't yet extract any data back.



Index: db_gis.c
===================================================================
RCS file: /cvsroot/xastir/xastir/src/db_gis.c,v
retrieving revision 1.5
retrieving revision 1.6
diff -C2 -d -r1.5 -r1.6
*** db_gis.c	8 Aug 2007 01:55:45 -0000	1.5
--- db_gis.c	12 Aug 2007 02:00:40 -0000	1.6
***************
*** 25,29 ****
--- 25,34 ----
  #ifdef HAVE_POSTGIS
  #include <libpq-fe.h>
+ // pg_type.h contains constants for OID values to use in paramTypes arrays
+ // in prepared queries.
+ #include <pg_type.h>   
  #endif // HAVE_POSTGIS
+ 
+ 
  // mysql error library for mysql error code constants
  #ifdef HAVE_MYSQL
***************
*** 161,166 ****
  // Layer 3 declarations
  
! char xastir_dbms_type[3][XASTIR_DB_DESCRIPTOR_MAX_SIZE+1] = {"","MySQL (lat/long)","Postgresql/Postgis","MySQL Spatial" } ;
! char xastir_schema_type[4][XASTIR_SCHEMA_DESCRIPTOR_MAX_SIZE+1] = {"","Xastir Simple","Xastir CAD","Xastir Full","APRSWorld" } ;
  
  /*
--- 166,171 ----
  // Layer 3 declarations
  
! char xastir_dbms_type[4][XASTIR_DB_DESCRIPTOR_MAX_SIZE+1] = {"","MySQL (lat/long)","Postgresql/Postgis","MySQL Spatial"} ;
! char xastir_schema_type[5][XASTIR_SCHEMA_DESCRIPTOR_MAX_SIZE+1] = {"","Xastir Simple","Xastir CAD","Xastir Full","APRSWorld"} ;
  
  /*
***************
*** 236,240 ****
  
  /* function storeStationToGisDb() 
!  * stores the information about a station and its most recent position
   * to a spatial database.
   * @param aDbConnection generic database connection to the database in
--- 241,245 ----
  
  /* function storeStationToGisDb() 
!  * Stores the information about a station and its most recent position
   * to a spatial database.
   * @param aDbConnection generic database connection to the database in
***************
*** 246,249 ****
--- 251,258 ----
  int storeStationToGisDb(Connection *aDbConnection, DataRow *aStation) { 
      int returnvalue = 0;
+     // This function is dbms agnostic, and hands the call off to a 
+     // function for the relevant database type.  That function picks the 
+     // relevant schema and either handles the query or passes it on to 
+     // a function to handle that schema.  
      switch (aDbConnection->type) {
          #ifdef HAVE_POSTGIS
***************
*** 509,512 ****
--- 518,523 ----
  Connection openConnection(ioparam *anIface) {
      char connection_string[900];
+     int connecting;  // connection polling loop test
+     int connected;   // status of connection polling loop
      unsigned long client_flag = 0; // parameter used for mysql connection, is normally 0.
      unsigned int port;  // port to make connection on
***************
*** 514,517 ****
--- 525,529 ----
      #ifdef HAVE_POSTGIS
      PGconn *postgres_connection;
+     PostgresPollingStatusType poll;
      #endif /* HAVE_POSTGIS */
      #ifdef HAVE_MYSQL
***************
*** 527,540 ****
          #ifdef HAVE_POSTGIS
          case DB_POSTGIS : 
              // If type is postgis, connect to postgis database.
              // build connection string from parameters
              xastir_snprintf(connection_string, sizeof(connection_string), \
!                "host=%s, user=%s, password=%s, db=%s, port=%d", \
                 anIface->device_host_name, anIface->database_username, anIface->device_host_pswd, anIface->database_schema, anIface->sp);
              // Use nonblocking connection
              postgres_connection = PQconnectStart(connection_string);
!             if (postgres_connection != NULL) {  
                  // can connect, run PQ_connect_poll loop
                  // Note: xastir needs to decide when to time out
              }
              break;
--- 539,576 ----
          #ifdef HAVE_POSTGIS
          case DB_POSTGIS : 
+ fprintf(stderr,"openConnection: In DB_POSTGIS\n");
              // If type is postgis, connect to postgis database.
              // build connection string from parameters
              xastir_snprintf(connection_string, sizeof(connection_string), \
!                "host=%s user=%s password=%s dbname=%s port=%d", \
                 anIface->device_host_name, anIface->database_username, anIface->device_host_pswd, anIface->database_schema, anIface->sp);
              // Use nonblocking connection
              postgres_connection = PQconnectStart(connection_string);
!             if (postgres_connection == NULL) {  
!                 xastir_snprintf(anIface->database_errormessage, sizeof(anIface->database_errormessage), "Insufficient memory to open connection.");
!             } else {
!                 connected = 0;
                  // can connect, run PQ_connect_poll loop
                  // Note: xastir needs to decide when to time out
+                 while (connected==0) { 
+                    // need to add a timer to polling loop
+                    poll = PQconnectPoll(postgres_connection);
+                    if (poll == PGRES_POLLING_FAILED || poll == PGRES_POLLING_OK) { 
+                       connected = 1;
+                    } 
+                    // add connection status feedback here if desired
+                 }
+                 if (PQstatus(postgres_connection)==CONNECTION_OK) {
+                     fprintf(stderr,"Connected to Postgresql database\n");
+                     // connection successfull
+                     conn.phandle = postgres_connection;
+                     conn.type=DB_POSTGIS;
+                     conn.descriptor = *anIface;
+                     xastir_snprintf(conn.errormessage, sizeof(conn.errormessage), " ");
+                 } else {
+                     // connection attempt failed
+                     fprintf(stderr,"Postgres connection failed %s\n", PQerrorMessage(postgres_connection));
+                     xastir_snprintf(anIface->database_errormessage, sizeof(anIface->database_errormessage), "Unable to make Postgresql connection %s. %s", PQerrorMessage(postgres_connection), connection_string); 
+                 }
              }
              break;
***************
*** 542,545 ****
--- 578,582 ----
          #ifdef HAVE_MYSQL_SPATIAL
          case DB_MYSQL_SPATIAL : 
+ fprintf(stderr,"openConnection: In DB_MYSQL_SPATIAL\n");
              mysql_connection = mysql_init(mysql_connection);
              // if type is mysql (=>4.1), connect to mysql database
***************
*** 552,556 ****
                  if (mysql_connection == NULL) { 
                      // unable to establish connection
!                     xastir_snprintf(anIface->database_errormessage, sizeof(anIface->database_errormessage), "Unable to establish connection. %s", mysql_error(mysql_connection));
                  } else { 
                      // connected to database
--- 589,593 ----
                  if (mysql_connection == NULL) { 
                      // unable to establish connection
!                     xastir_snprintf(anIface->database_errormessage, sizeof(anIface->database_errormessage), "Unable to establish connection: %s", mysql_error(mysql_connection));
                  } else { 
                      // connected to database
***************
*** 568,572 ****
          #ifdef HAVE_MYSQL
          case DB_MYSQL : 
! fprintf(stderr,"In DB_MYSQL\n");
              mysql_connection = mysql_init(mysql_connection);
              // if type is mysql, connect to mysql database
--- 605,609 ----
          #ifdef HAVE_MYSQL
          case DB_MYSQL : 
! fprintf(stderr,"openConnection: In DB_MYSQL\n");
              mysql_connection = mysql_init(mysql_connection);
              // if type is mysql, connect to mysql database
***************
*** 751,756 ****
      int returnvalue = 0;
      PGconn *conn = aDbConnection->phandle;
!     // check type of schema to use (XASTIR full or APRSWorld) 
      switch (aDbConnection->descriptor.database_schema_type) {
          case XASTIR_SCHEMA_APRSWORLD : 
              break;
--- 788,796 ----
      int returnvalue = 0;
      PGconn *conn = aDbConnection->phandle;
!     // check type of schema to use (XASTIR simple, full or APRSWorld) 
      switch (aDbConnection->descriptor.database_schema_type) {
+         case XASTIR_SCHEMA_SIMPLE : 
+             returnvalue = storeStationSimplePointToGisDbPostgis(aDbConnection,aStation);
+             break;
          case XASTIR_SCHEMA_APRSWORLD : 
              break;
***************
*** 785,813 ****
      int returnvalue = 0;  // Default return value is failure.
      int ok;  // Holds results of tests when building query.
!     char wkt[100];  // well know text representation of latitude and longitude of point
      char timestring[101];  // string representation of the time heard or the current time
!     char call_sign[MAX_CALLSIGN];  
      PGconn *conn = aDbConnection->phandle;
!     // prepare statement
!     char sql[100] = "insert into simpleStation (call, transmit_time, position) values ('%1','%2','%3')";
! 
  
!     // get call, position, and time
!     ok = xastirCoordToLatLongWKT(aStation->coord_lon, aStation->coord_lat, wkt);
!     if (ok==1) { 
!         xastir_snprintf(call_sign,sizeof(call_sign),"%s",aStation->call_sign);
!  
!         // get time in seconds, adjust to datetime
!         // If my station or another unset sec_heard is 
!         // encountered, use current time instead, use time
!         // provided if it was invalid.
!         get_iso_datetime(aStation->sec_heard,timestring,True,False);
  
!         // send query
!         
  
!     } else { 
!         // problem with coordinates of station 
!         xastir_snprintf(aDbConnection->errormessage, sizeof(aDbConnection->errormessage), "Error converting latitude or longitude from xastir coordinates: %d,%d",aStation->coord_lat,aStation->coord_lon);
      }
      return returnvalue;
--- 825,945 ----
      int returnvalue = 0;  // Default return value is failure.
      int ok;  // Holds results of tests when building query.
!     char wkt[MAX_WKT];  // well know text representation of latitude and longitude of point
      char timestring[101];  // string representation of the time heard or the current time
!     char call_sign[(MAX_CALLSIGN*2)+1];  // temporary holding for escaped callsign
!     char aprs_symbol[2];  // temporary holding for escaped aprs symbol
!     char aprs_type[2];    // temporary holding for escaped aprs type
!     char special_overlay[2];  // temporary holding for escaped overlay
      PGconn *conn = aDbConnection->phandle;
!     PGresult *prepared;
!     PGresult *result;
!     int count;  // returned value from count query
!     const int PARAMETERS = 6;
!     // parameter arrays for prepared query
!     const char *paramValues[PARAMETERS];  
!     int paramLengths[PARAMETERS];  // ? don't need, just null for prepared insert ?
!     int paramFormats[PARAMETERS];  // ? don't need, just null for prepared insert ?
!     // To use native Postgres POINT for position instead of postgis geometry point.
!     //const Oid paramTypes[6] = { VARCHAROID, TIMESTAMPTZOID, POINTOID, VARCHAROID, VARCHAROID, VARCHAROID };
!     //const Oid paramTypes[6] = { 1043, 1184, 600, 1043, 1043, 1043 };
!     // Native postgres (8.2) geometries don't have spatial support as rich as Postgis extensions.
!     // use postgis geometry Point instead:
!     // lookup OID for geometry:  select OID from pg_type where typname = 'geometry'; returns 19480
!     //const Oid paramTypes[6] = { VARCHAROID, TIMESTAMPTZOID, 19480, VARCHAROID, VARCHAROID, VARCHAROID };
!     //const Oid paramTypes[6] = { 1043, 1184, 19480, 1043, 1043, 1043 };
!     // Value 18480 is probably installation specific, use unknownOID instead:
!     //const Oid paramTypes[6] = { VARCHAROID, TIMESTAMPTZOID, UNKNOWNOID, VARCHAROID, VARCHAROID, VARCHAROID };
!     const Oid paramTypes[6] = { 1043, 1184, 705, 1043, 1043, 1043 };
!     const char *sql = "insert into simpleStation (station, transmit_time, position, symbol, overlay, aprstype) values ($1, $2, $3, $4, $5, $6)";
!     const char *StatementName = "InsertSimpleStation";
!     const char *StatementExists = "select count(*) from pg_prepared_statements where name = 'InsertSimpleStation'";
  
! fprintf(stderr,"In postgres simple station insert\n");
!     // Check to see if this prepared statement exists in the current session
!     // and create it if it does not.  
!     // Query adds connection overhead - should probably track with a global variable, 
!     // and query/recreate statment only on failure.  
!     ok = 0;
!     result = PQexec(conn, StatementExists);
!     if (result==NULL) { 
!        fprintf(stderr,"Postgres Check for Prepared Query exec Failed: %s\n", PQerrorMessage(conn));
!        xastir_snprintf(aDbConnection->errormessage,sizeof(aDbConnection->errormessage),PQerrorMessage(conn));
!     } else {
!         count = atoi(PQgetvalue(result,0,0));
!         if (count==0) { 
!             // Statement doesn't exist, so prepare it, let PQprepare report on any error that got us a NULL result.
!             prepared = PQprepare(conn, StatementName, sql, PARAMETERS, paramTypes);
!             if (PQresultStatus(prepared)==PGRES_COMMAND_OK) { 
!                 ok = 1;
!             } else {
!                // error condition - can't prepare statement
!                fprintf(stderr,"Postgres Prepare Query Failed: %s\n", PQerrorMessage(conn));
!                xastir_snprintf(aDbConnection->errormessage,sizeof(aDbConnection->errormessage),PQerrorMessage(conn));
!             }
!         } else if (count==1) { 
!             // prepared statement exists, we can go ahead with query.
!             ok = 1;
!         } else {
!             fprintf(stderr,"Postgres Check for Prepared Query getvalue (count=%d) failed: %s\n",count, PQresultErrorMessage(result));
!             xastir_snprintf(aDbConnection->errormessage,sizeof(aDbConnection->errormessage),PQresultErrorMessage(result));
!         }
!     }
!     if (ok==1) {
!         // native postgis POINT is (99.999 099.999) instead of POINT (99.999 099.999)
!         // ok = xastirCoordToLatLongPosgresPoint(aStation->coord_lon, aStation->coord_lat, wkt);
!         //
!         // Prepared query is ready, get and fill in the parameter values
!         // from the staiton provided, then fire the query.
!         ok = xastirCoordToLatLongWKT(aStation->coord_lon, aStation->coord_lat, wkt);
!         if (ok==1) { 
!             PQescapeString(call_sign,aStation->call_sign,(MAX_CALLSIGN*2)+1);
!     
!             // get time in seconds, adjust to datetime
!             // If my station or another unset sec_heard is 
!             // encountered, use current time instead. Conversely, use time
!             // provided in sec_heard if sec_heard is an invalid time.
!             get_iso_datetime(aStation->sec_heard,timestring,True,False);
  
!             // set parameter values to call, transmit_time, and position
!             paramValues[0]=call_sign;
!             paramValues[1]=timestring;
!             paramValues[2]=wkt;
!             if (aStation->aprs_symbol.aprs_symbol==NULL) { 
!                 xastir_snprintf(aprs_symbol,2," ");
!                 paramValues[3]=&aprs_symbol;
!             } else { 
!                 xastir_snprintf(aprs_symbol,2,"%c",aStation->aprs_symbol.aprs_symbol);
!                 paramValues[3]=aprs_symbol;
!             }
!             if (aStation->aprs_symbol.special_overlay==NULL) { 
!                 xastir_snprintf(special_overlay,2," ");
!                 paramValues[4]=&special_overlay;
!             } else { 
!                 xastir_snprintf(special_overlay,2,"%c",aStation->aprs_symbol.special_overlay);
!                 paramValues[4]=&special_overlay;
!             }
!             if (aStation->aprs_symbol.aprs_type==NULL) { 
!                 xastir_snprintf(aprs_type,2," ");
!                 paramValues[5]=&aprs_type;
!             } else { 
!                 xastir_snprintf(aprs_type,2,"%c",aStation->aprs_symbol.aprs_type);
!                 paramValues[5]=aprs_type;
!             }
! fprintf(stderr,"Inserting: Call: %s, Time: %s, Position: %s, Symbol:%s,%s,%s\n",call_sign,timestring,wkt,paramValues[3],paramValues[4],paramValues[5]);
  
!             // send query
!             result = PQexecPrepared(conn,StatementName,PARAMETERS,paramValues,NULL,NULL,POSTGRES_RESULTFORMAT_TEXT);
!             if (PQresultStatus(result)!=PGRES_COMMAND_OK) { 
!                    fprintf(stderr,"Insert query failed:%s\n",PQresultErrorMessage(result));
!                    // error, get error message.
!                    xastir_snprintf(aDbConnection->errormessage,sizeof(aDbConnection->errormessage),PQresultErrorMessage(result));
!             } else { 
!                  // query was successfull
!                 returnvalue=1;
!             }    
!         } else { 
!             // problem with coordinates of station 
!             xastir_snprintf(aDbConnection->errormessage, sizeof(aDbConnection->errormessage), "Error converting latitude or longitude from xastir coordinates: %ld,%ld",aStation->coord_lat,aStation->coord_lon);
!         }
      }
      return returnvalue;
***************
*** 891,895 ****
      int mysqlreturn;
      MYSQL *conn = aDbConnection->mhandle;
!    
      return returnvalue;
  }
--- 1023,1037 ----
      int mysqlreturn;
      MYSQL *conn = aDbConnection->mhandle;
!     // check type of schema to use (XASTIR simple, full or APRSWorld) 
!     switch (aDbConnection->descriptor.database_schema_type) {
!         case XASTIR_SCHEMA_SIMPLE : 
!             returnvalue = storeStationSimplePointToGisDbMysql(aDbConnection,aStation);
!             break;
!         case XASTIR_SCHEMA_APRSWORLD : 
!             break;
!         case XASTIR_SCHEMA_COMPLEX :
!             break;
!         // otherwise error message
!     }
      return returnvalue;
  }
***************
*** 952,956 ****
      int test;
      int ok;    // variable to store results of tests preparatory to firing query
!     char wkt[100];  // well know text representation of latitude and longitude of point
      char timestring[100+1];
      MYSQL *conn = aDbConnection->mhandle;
--- 1094,1098 ----
      int test;
      int ok;    // variable to store results of tests preparatory to firing query
!     char wkt[MAX_WKT];  // well know text representation of latitude and longitude of point
      char timestring[100+1];
      MYSQL *conn = aDbConnection->mhandle;
***************
*** 1081,1089 ****
       symbol varchar(1),     # aprs symbol character
       overlay varchar(1),    # aprs overlay table character
!      aprstype varchar(1)    # aprs type, required???
       transmit_time datetime not null default now(),  # transmission time, if available, otherwise storage time
       position POINT   # position of station or null if latitude and longitude are not available
    );
  
    grant select, insert on simpleStation to xastir_user@localhost;
  
--- 1223,1233 ----
       symbol varchar(1),     # aprs symbol character
       overlay varchar(1),    # aprs overlay table character
!      aprstype varchar(1),    # aprs type, required???
       transmit_time datetime not null default now(),  # transmission time, if available, otherwise storage time
       position POINT   # position of station or null if latitude and longitude are not available
    );
  
+                                                                           
+ 
    grant select, insert on simpleStation to xastir_user@localhost;
  
***************
*** 1161,1167 ****
      // Next three variables are one character in two bytes plus one character for
      // filling by mysql_real_escape_string().  
!     char aprs_symbol[3];  
!     char aprs_type[3];
!     char special_overlay[3];
      float longitude;
      float latitude;
--- 1305,1312 ----
      // Next three variables are one character in two bytes plus one character for
      // filling by mysql_real_escape_string().  
!     char aprs_symbol[3];  // temporary holding for escaped aprs symbol
!     char aprs_type[3];    // temporary holding for escaped aprs type
!     char special_overlay[3];  // temporary holding for escaped overlay
!     char call_sign[(MAX_CALLSIGN)*2+1];   // temporary holding for escaped callsign
      float longitude;
      float latitude;
***************
*** 1198,1203 ****
              } else { 
                  mysql_real_escape_string(conn,&special_overlay,&(aStation->aprs_symbol.special_overlay),3);
!             }   
              xastir_snprintf(sql,sizeof(sql),"insert into simpleStation (station, symbol, overlay, aprstype, transmit_time, latitude, longitude) values ('%s','%s','%s','%s','%s','%3.6f','%3.6f')", aStation->call_sign, aprs_symbol, special_overlay, aprs_type,timestring,latitude,longitude);
              // send query
              fprintf(stderr,"MySQL Query:\n%s\n",sql);
--- 1343,1355 ----
              } else { 
                  mysql_real_escape_string(conn,&special_overlay,&(aStation->aprs_symbol.special_overlay),3);
!             } 
!   
!             // Need to escape call sign - may contain special characters: 
!             // insert into simpleStation (station, symbol, overlay, aprstype, transmit_time, latitude, longitude) 
!             // values ('Fry's','/\0\0',' ','//\0','2007-08-07 21:55:43 -0400','47.496834','-122.198166')
!             mysql_real_escape_string(conn,&call_sign,&(aStation->call_sign),(MAX_CALLSIGN*2+1));
!         
              xastir_snprintf(sql,sizeof(sql),"insert into simpleStation (station, symbol, overlay, aprstype, transmit_time, latitude, longitude) values ('%s','%s','%s','%s','%s','%3.6f','%3.6f')", aStation->call_sign, aprs_symbol, special_overlay, aprs_type,timestring,latitude,longitude);
+ 
              // send query
              fprintf(stderr,"MySQL Query:\n%s\n",sql);
***************
*** 1211,1215 ****
              }
          } else { 
!             xastir_snprintf(aDbConnection->errormessage, sizeof(aDbConnection->errormessage), "Error converting latitude or longitude from xastir coordinates: %d,%d",aStation->coord_lat,aStation->coord_lon);
          } 
      } else { 
--- 1363,1367 ----
              }
          } else { 
!             xastir_snprintf(aDbConnection->errormessage, sizeof(aDbConnection->errormessage), "Error converting latitude or longitude from xastir coordinates: %ld,%ld",aStation->coord_lat,aStation->coord_lon);
          } 
      } else { 
***************
*** 1246,1249 ****
--- 1398,1412 ----
  int storeStationToDbMysql(Connection *aDbConnection, DataRow *aStation){
      int returnvalue = 0;
+     // check type of schema to use (XASTIR simple, full or APRSWorld) 
+     switch (aDbConnection->descriptor.database_schema_type) {
+         case XASTIR_SCHEMA_SIMPLE : 
+             returnvalue = storeStationSimplePointToDbMysql(aDbConnection,aStation);
+             break;
+         case XASTIR_SCHEMA_APRSWORLD : 
+             break;
+         case XASTIR_SCHEMA_COMPLEX :
+             break;
+         // otherwise error message
+     }
      return returnvalue;
  }
***************
*** 1263,1269 ****
  int getAllSimplePositionsMysql(Connection *aDbConnection) {
      int returnvalue = 0;
!     char sql[] = "select call, transmit_time, latitude, longitude from simpleStation";
      MYSQL *conn = aDbConnection->mhandle;
  
      return returnvalue;
  }
--- 1426,1455 ----
  int getAllSimplePositionsMysql(Connection *aDbConnection) {
      int returnvalue = 0;
!     char sql[] = "select call, transmit_time, latitude, longitude, symbol, overlay, aprstype from simpleStation";
      MYSQL *conn = aDbConnection->mhandle;
+     MYSQL_RES *result;
+     MYSQL_ROW *row;
+     if (mysql_query(conn,sql)==0) { 
+         result = mysql_use_result(conn);
+         if (result!=NULL) { 
+             // with mysql_use_result each call to mysql_fetch_row retrieves
+             // a row of data from the server.  Mysql_store_result might use
+             // too much memory in retrieving a large result set all at once.
+             row = mysql_fetch_row(result);
+             while (row != NULL) { 
+                // retrieve data from the row
  
+                // try to fetch the next row
+                row = mysql_fetch_row(result);
+             }
+         } else { 
+             // error fetching the result set
+             mysql_interpret_error(conn,aDbConnection);
+         }
+         mysql_free_result(result);
+     } else { 
+        // query didn't execute correctly
+        mysql_interpret_error(conn,aDbConnection);
+     }
      return returnvalue;
  }
***************
*** 1341,1344 ****
--- 1527,1560 ----
  
  
+ /* Function  xastirCoordToLatLongPostgresPoint
+  * converts a point in xastir coordinates to a native postgres representation 
+  * of a point using latitude and longitude in decimal degrees in the WGS84 
+  * projection EPSG:4326. Format is similar to WKT, but without leading POINT.
+  * @param x longitude in xastir coordinates = decimal 100ths of a second.  
+  * @param y latitude in xastir coordinates = decimal 100ths of a second.  
+  * @param pointer to a char[ at least 24] string to hold point representation.
+  * returns 1 on success, 0 on failure.
+  */
+ int xastirCoordToLatLongPostgresPoint(long x, long y, char *wkt) { 
+     // 1 xastir coordinate = 1/100 of a second
+     // 100*60*60 xastir coordinates (=360000 xastir coordinates) = 1 degree
+     // 360000   xastir coordinates = 1 degree
+     // conversion to string decimal degrees handled by utility fuctions
+     int returnvalue = 0;  // defaults to failure
+     float latitude;
+     float longitude;
+     int ok;
+     ok = convert_from_xastir_coordinates (&longitude,&latitude, x, y);
+     if (ok>0) { 
+        xastir_snprintf(wkt, MAX_WKT, "(%3.6f, %3.6f)", latitude, longitude);
+        returnvalue = 1;
+     } 
+     return returnvalue;
+ }
+ 
+ 
+ 
+ 
+ 
  
  /* Function  xastirCoordToLatLongWKT
***************
*** 1362,1366 ****
      ok = convert_from_xastir_coordinates (&longitude,&latitude, x, y);
      if (ok>0) { 
!        xastir_snprintf(wkt, sizeof(wkt), "POINT(%3.6f %3.6f)", latitude, longitude);
         returnvalue = 1;
      } 
--- 1578,1582 ----
      ok = convert_from_xastir_coordinates (&longitude,&latitude, x, y);
      if (ok>0) { 
!        xastir_snprintf(wkt, MAX_WKT, "POINT(%3.6f %3.6f)", latitude, longitude);
         returnvalue = 1;
      } 

Index: db_gis.h
===================================================================
RCS file: /cvsroot/xastir/xastir/src/db_gis.h,v
retrieving revision 1.3
retrieving revision 1.4
diff -C2 -d -r1.3 -r1.4
*** db_gis.h	8 Aug 2007 01:55:45 -0000	1.3
--- db_gis.h	12 Aug 2007 02:00:40 -0000	1.4
***************
*** 27,30 ****
--- 27,35 ----
  
  extern int xastirCoordToLatLongWKT(long x, long y, char *wkt);
+ extern int xastirCoordToLatLongPoint(long x, long y, char *wkt);
+ 
+ // maximum size of a well known text representation of a geometry
+ // 100 should be fine for points, will need to be longer for other geometries.
+ #define MAX_WKT 100
  
  #ifdef HAVE_DB
***************
*** 51,54 ****
--- 56,60 ----
  #include <libpq-fe.h>
  #define DB_POSTGIS 2
+ #define POSTGRES_RESULTFORMAT_TEXT 0
  #endif /* HAVE_POSTGIS */
  
***************
*** 135,140 ****
  extern int testConnection(Connection *aDbConnection);
  
! extern char xastir_dbms_type[3][51];
! extern char xastir_schema_type[4][51];
  
  // storing and retrieving data from a database
--- 141,146 ----
  extern int testConnection(Connection *aDbConnection);
  
! extern char xastir_dbms_type[4][51];
! extern char xastir_schema_type[5][51];
  
  // storing and retrieving data from a database