Thursday, 18 June 2015

Reading data from CSV file and writing it into the PostgreSQL database in C++

Reading data from CSV file and writing it into the PostgreSQl database in Cpp with examples:

In this we are going to learn how we are using Postgres database using the languages C/C++.

First of all we need to understand the procedure and the functions used in Postgress database creation and manipulation etc.

APIS that are used to work with tPOSTGresSql data base:

To access the database from your C/C++ program, we are going to use a library called libpq-fe. The most basic functions that we'll need in order to access the database are the following:

PQconnectdb – makes the connection to the database
PQexec – executes the required queries
PQgetvalue – get the required values

#include <postgresql/libpq-fe.h>
,we need to include the header file into the application, where we are going to use the Postgres database related functions

Declare the connection with the PostGreSql:


Make the connection to the database using the below syntax
PGconn *m_pcDBConn;


Initiate the connection
   m_pcDBConn = PQconnectdb ("user=postgres password=postgres dbname=postgis hostaddr=172.0.0.1 port=5432");

Of course, mydatabase can be any database you choose. This is only a sample database we're using.

Step 4 Test the connection
Check to see that the backend connection was successfully made

  if (PQstatus (m_pcDBConn) != CONNECTION_OK) {

    

    printf ("Connection to database failed");

    CloseConn (m_pcDBConn); return;

  }


Declare a query result holder

Now the whole point of accessing the database in your application is to get or store information to and from the database (with the application as a "front-end"). When retrieving data from the database, these data are stored in a variable of the type PGresult*

 PGresult *res = NULL;


Query execution:
So now we execute a simple SELECT statement and store the data in query:
    std::string ocSQLVals;

    sSQL.append ("INSERT INTO ");

    sSQL.append (ocFilename);

   sSQL.append ("  (ObjectCode, geom) VALUES (");



    outSTR.str ("");



    outSTR <<  (*usIndex).usID << ",";

  

    /* For Points*/

  sSQL.append (outSTR.str ()); 

    outSTR.str("");



  sSQL.append ("(SELECT ST_GeomFromText('POINT(");



    long double lat1  = (long double )(*usIndex).dLat;

    long double lon1  = (long double )(*usIndex).dLong;



    outSTR.precision (16);

   outSTR << lon1 ;

   outSTR<< " " ;

   outSTR.precision (16);

   outSTR<<lat1;



    sSQL.append (outSTR.str ()); 



  ocSQLVals.append (")')));");

  sSQL.append (ocSQLVals.c_str ());



    ocSQLVals.empty ();    ocSQLVals.clear ();



  res  = PQexec (m_pcDBConn, sSQL.c_str ());



Connection closing:
Weshould close the connection, after our database usage is completed.
;

void CSV2PostGres::CloseConn (PGconn *conn)

  { PQfinish (conn); }


Example code that is used us to understand the total usage of the PostgrreSQL database and their API’s
The below is the header file that contains the functions to read the CSV file and write the data into the PostgrreSQL database
#ifndef _CSV_2_POSTGRES_H_

#define _CSV_2_POSTGRES_H_

#include <vector>

#include "IOFile.h"

#include <string>

#include <libpq-fe.h>

#define PI 3.141592653589793

Using namespace std;

/* this structure is used to hold the   information like primary keys and their    corresponding geometric values from the GPS file */

typedef struct stgeom {

  ULONG64 usID;  // Holds the index

  Dbl dLat,         // holds the latitude value

      dLong;        // holds the longitude value

  }stGEOM;



class CSV2PostGres{

public:

           CSV2PostGres (); 

           CSV2PostGres (CChar* pcchFilePath, CChar* pcchOutPath);

  virtual ~CSV2PostGres (); 

  void Getdistance ();

private:

  char **m_pcchGeom;  // holds the Geometry from the data       

  CChar *m_pcchOutPath;

  PGconn* m_pcDBConn;

  vector<stGEOM> m_vGeom;  // holds the data from the GPS file

    /* This function is used to open the input     file to read the data inside that file */

  _BOOL Open (CChar* pcchFilePath);

    /* This function is used to read the CSV file */

  _BOOL ReadCSV (IOFile *pcFile);

  /* connect to the Postgres database */

  void  ConnectDB ();

  void CreateTable (PGconn *conn);

  void Dump2Postgres ();

  void CloseConn (PGconn *conn);

  double inline ToRadians (Dbl dDiff)

  {  dDiff = (dDiff * PI) / 180; return dDiff; }

double inline ToDegress (Dbl dDiff)

  {  dDiff = (dDiff *180) / PI; return dDiff; }

};#endif //_CSV_2_POSTGRES_H_


The below is the implementation
#include "stdafx.h"

#include "CSV2PostGres.h"

#include "afxwin.h"

#include <iostream>

#include <fstream>

#include <sstream>


Constructor and parameterized constructors of the class, in the constructors we have initialized all the member variables to their default values.
m_pcDBConn to null value
Open function is called in the parameterized constructor. That open function reads the CSV file data and stores the required information into the vector m_vGeom.
CSV2PostGres::CSV2PostGres () : m_pcchGeom (NULL), m_pcchOutPath (NULL), m_vGeom (NULL), m_pcDBConn (NULL)

  { }





CSV2PostGres::CSV2PostGres (CChar* pcchFilePath, CChar* pcchOutPath) : m_pcchGeom (NULL),

                                                                  m_pcchOutPath (pcchOutPath),

                                                                       m_vGeom (NULL), m_pcDBConn (NULL)

  { Open (pcchFilePath); }





Destructor of the class the connection close funciton CloseConn is called in the destructor.
CSV2PostGres::~CSV2PostGres ()

{



  if (m_pcchGeom)

    { Destroy (m_pcchGeom); m_pcchGeom = NULL; }

  if (m_pcDBConn)

    { CloseConn (m_pcDBConn); m_pcDBConn = NULL;  }

}



PQfininsh API is ued to Close connction
void CSV2PostGres::CloseConn (PGconn *conn)

  { PQfinish (conn); }


This function is used to open the input CSV file and pushing the information into the vector of stGeom structure
    m_vGeom.push_back (ostGeom);



_BOOL CSV2PostGres::Open (CChar* pcchFilePath)

{



  IOFile *pcFile = new IOFile ();



  if (!pcFile->Open (pcchFilePath, "r"))

    { delete pcFile; pcFile = NULL; return _FALSE; }


    if (!ReadCSV (pcFile))

      { pcFile->Close (); delete pcFile; pcFile = NULL; return _FALSE; };


  pcFile->Close (); delete pcFile; pcFile = NULL;

  return _TRUE;

}

_BOOL CSV2PostGres::ReadCSV (IOFile *pcFile)

{



  CChar *pcchStr = NULL; char** ppchToks = NULL;

  Int32 usInd = 0;



  while (!pcFile->Eof ()) {

    stGEOM ostGeom;

    pcchStr = pcFile->ReadLine ();

    cout << "data from the file" << pcchStr << endl;

    if  (StrEqual (pcchStr, "") == _TRUE ||

        StrEqual (pcchStr , NULL) == _TRUE)

      { continue; }

    ppchToks = StrTokenizeCmplx (pcchStr, ",", _FALSE, _FALSE);

    UInt16 usToksCnt = GetCount (ppchToks);

    if (usToksCnt < 0 )

      { Destroy (ppchToks); ppchToks = NULL; return _FALSE; }


    ostGeom.usID       = atof (ppchToks[0]);

    ostGeom.dLong      = atof (ppchToks[8]);

    ostGeom.dLat       = atof (ppchToks[9]);

  
    m_vGeom.push_back (ostGeom);

    Destroy (ppchToks); ppchToks = NULL; usInd++;

  }

  printf ("Total number of rows in the file: %d", usInd);

  return _TRUE;

}


Connecting To Database

Establish connection to database, which is already available in your schema and can be accessed using user postgres ,password postgres and the dbname is postgis.
If you want to connect to the server you need to provide the host address and port number.

void  CSV2PostGres::ConnectDB ()

{

  /* Make a connection to the database */

   m_pcDBConn = PQconnectdb ("user=postgres password=postgres dbname=postgis hostaddr=172.0.0.1 port=5432");

  // m_pcDBConn = PQconnectdb("dbname=postgis host=localhost user=postgres password=postgres");



  /* Check to see that the backend connection was successfully made */

  if (PQstatus (m_pcDBConn) != CONNECTION_OK) {

    

    printf ("Connection to database failed");

    CloseConn (m_pcDBConn); return;

  }

  printf ("Connection to database - OK\n");

}


Creating the table in the database

void CSV2PostGres::CreateTable (PGconn *conn)

{

  // Execute with sql statement

    PGresult *res = PQexec (conn, "CREATE TABLE test (ObjectCode real, geom geometry)");

 afxDump << PQresultStatus(res);

  

  if (PQresultStatus(res) != PGRES_COMMAND_OK) {



    printf ("Create test table failed");

    PQclear (res); CloseConn (conn);

  }



  printf ("Create test table - OK\n");

  // Clear result

  PQclear(res);

}

In the Getdistance function the required files are inserted in to the database in the form of geometry



void CSV2PostGres::Getdistance ()

{



  ConnectDB ();



  CreateTable (m_pcDBConn);

  

 Dbl earthRadiusmiles = 3958.75,

     meterConversion = 1609.00;



  Dbl dLat1 = 0.0, dLat2 = 0.0, dLong1 = 0.0,

      dLong2 = 0.0, dLat = 0.0, dLong = 0.0,

      dA = 0.0, dC = 0.0, dDist = 0.0;



 std::filebuf ocFileBuff;

  ocFileBuff.open (m_pcchOutPath, std::ios::out);

 std::ostream outstream (&ocFileBuff);



  PGresult *res = NULL;



  std::string sSQL;  std::stringstream  outSTR;



  for (UINT16 usIndex = 0; usIndex < m_vGeom.size (); usIndex++) {

  

    std::string ocSQLVals;

    sSQL.append ("INSERT INTO");

    sSQL.append ("test ");

   sSQL.append (" (ObjectCode, geom) VALUES (");



    outSTR.str ("");



    outSTR << m_vGeom.at (usIndex).usID << ",";

  

    /* For Points*/

  sSQL.append (outSTR.str ()); 

    outSTR.str("");



  sSQL.append ("(SELECT ST_GeomFromText('POINT(");



    long double lat1  = (long double )m_vGeom.at (usIndex).dLat;

    long double lon1  = (long double )m_vGeom.at (usIndex).dLong;



  outSTR.precision (16);

 outSTR << lon1 ;

 outSTR<< " " ;

 outSTR.precision (16);

 outSTR<<lat1;



    sSQL.append (outSTR.str ()); 



  ocSQLVals.append (")')));");

  sSQL.append (ocSQLVals.c_str ());



  afxDump << usIndex <<"\n";



    ocSQLVals.empty ();



  res  = PQexec (m_pcDBConn, sSQL.c_str ());



   if (PQresultStatus (res) != PGRES_COMMAND_OK)

      { PQclear (res);  CloseConn (m_pcDBConn);  return; }



  ocSQLVals.clear (); outSTR.str (""); sSQL.clear ();



    #if 0

    if (usIndex == 0) {



      outstream << "ID" << "\t";   

      outstream << "Latutude" << "\t";

      outstream << "Longitude" << "\t";

      outstream.precision (16); outstream << "Distance"<< "\n";



      outstream << m_vGeom.at (usIndex).usID << "\t";   

      outstream.precision (16); outstream << m_vGeom.at (usIndex).dLat << "\t";

      outstream.precision (16); outstream << m_vGeom.at (usIndex).dLong << "\t";

      outstream.precision (16); outstream << 0.0 << "\n";



    }

    else {



      dLat1 = m_vGeom.at (usIndex - 1).dLat;      dLong1 = m_vGeom.at (usIndex - 1).dLong;



      dLat2 = m_vGeom.at (usIndex).dLat;          dLong2 = m_vGeom.at (usIndex).dLong;



      outstream << m_vGeom.at (usIndex).usID << "\t";   

      outstream.precision(16) ; outstream << dLat2 << "\t";

      outstream.precision(16) ; outstream << dLong2 << "\t";



      dLat  =  ToRadians (dLat2 - dLat1);

      dLong =  ToRadians (dLong2 - dLong1);



      dA = sin (dLat / 2) * sin (dLat / 2) +

         cos (ToRadians (dLat1) * cos (ToRadians (dLat2))) *

          sin (dLong / 2) * sin (dLong / 2);



     dC    = 2 * atan2 (sqrt (dA), sqrt (1 - dA));

     dDist = earthRadiusmiles * dC * meterConversion;

      outstream.precision(16) ;  outstream << dDist << "\n";

    }

#endif //0

  }

}



No comments: