Logo Search packages:      
Sourcecode: qgis version File versions

QgsPostgresProvider::QgsPostgresProvider ( QString  uri = 0  ) 

Constructor for the provider. The uri must be in the following format: host=localhost user=gsherman dbname=test password=xxx table=test.alaska (the_geom)

Parameters:
uri String containing the required parameters to connect to the database and query the table.

Definition at line 36 of file qgspostgresprovider.cpp.

References connection, QgsDataSourceURI::database, endianString(), QgsDataSourceURI::geometryColumn, geometryColumn, geomType, getPrimaryKey(), hasGEOS(), QgsDataSourceURI::host, layerExtent, mUri, numberFeatures, QgsDataSourceURI::password, QgsDataSourceURI::port, QgsDataSourceURI::schema, schemaTableName, selectSQL, QgsRect::setXmax(), QgsRect::setXmin(), QgsRect::setYmax(), QgsRect::setYmin(), QgsDataSourceURI::sql, sqlWhereClause, srid, swapEndian, QgsDataSourceURI::table, tableName, QgsDataSourceURI::username, valid, QgsRect::xMax(), QgsRect::yMax(), and QgsRect::yMin().

                                                   :dataSourceUri(uri)
{
  // assume this is a valid layer until we determine otherwise
  valid = true;
  /* OPEN LOG FILE */

  // Make connection to the data source
  // For postgres, the connection information is passed as a space delimited
  // string:
  //  host=192.168.1.5 dbname=test port=5342 user=gsherman password=xxx table=tablename
  //--std::cout << "Data source uri is " << uri << std::endl;
  
  // Strip the table and sql statement name off and store them
  int sqlStart = uri.find(" sql");
  int tableStart = uri.find("table=");
#ifdef QGISDEBUG
  qDebug( "****************************************");
  qDebug(  "****   Postgresql Layer Creation   *****" );
  qDebug(  "****************************************");
  qDebug(  "URI: " + uri );
  QString msg;
  
  qDebug(  "tableStart: " + msg.setNum(tableStart) );
  qDebug(  "sqlStart: " + msg.setNum(sqlStart));
#endif 
  tableName = uri.mid(tableStart + 6, sqlStart - tableStart -6);
 if(sqlStart > -1)
 { 
    sqlWhereClause = uri.mid(sqlStart + 5);
 }
 else
 {
   sqlWhereClause = QString::null;
 }
  QString connInfo = uri.left(uri.find("table="));
#ifdef QGISDEBUG
  qDebug( "Table name is " + tableName);
  qDebug( "SQL is " + sqlWhereClause );
  qDebug( "Connection info is " + connInfo);
#endif
  // calculate the schema if specified
  QString schema = "";
  if (tableName.find(".") > -1) {
    schema = tableName.left(tableName.find("."));
  }
  geometryColumn = tableName.mid(tableName.find(" (") + 2);
  geometryColumn.truncate(geometryColumn.length() - 1);
  tableName = tableName.mid(tableName.find(".") + 1, tableName.find(" (") - (tableName.find(".") + 1));
  
  /* populate the uri structure */
  mUri.schema = schema;
  mUri.table = tableName;
  mUri.geometryColumn = geometryColumn;
  mUri.sql = sqlWhereClause;
  // parse the connection info
  QStringList conParts = QStringList::split(" ", connInfo);
  QStringList parm = QStringList::split("=", conParts[0]);
  if(parm.size() == 2)
  {
    mUri.host = parm[1];
  }
  parm = QStringList::split("=", conParts[1]);
  if(parm.size() == 2)
  {
    mUri.database = parm[1];
  }
  parm = QStringList::split("=", conParts[2]);
  if(parm.size() == 2)
  {
    mUri.port = parm[1];
  }

  parm = QStringList::split("=", conParts[3]);
  if(parm.size() == 2)
  {
    mUri.username = parm[1];
  }
  parm = QStringList::split("=", conParts[4]);
  if(parm.size() == 2)
  {
    mUri.password = parm[1];
  }
  /* end uri structure */

#ifdef QGISDEBUG
  std::cerr << "Geometry column is: " << geometryColumn << std::endl;
  std::cerr << "Schema is: " + schema << std::endl;
  std::cerr << "Table name is: " + tableName << std::endl;
#endif
  //QString logFile = "./pg_provider_" + tableName + ".log";
  //pLog.open((const char *)logFile);
#ifdef QGISDEBUG
  std::cerr << "Opened log file for " << tableName << std::endl;
#endif
  PGconn *pd = PQconnectdb((const char *) connInfo);
  // check the connection status
  if (PQstatus(pd) == CONNECTION_OK) {
    /* Check to see if we have GEOS support and if not, warn the user about
       the problems they will see :) */
#ifdef QGISDEBUG
    std::cerr << "Checking for GEOS support" << std::endl;
#endif
    if(!hasGEOS(pd)){
      QApplication::restoreOverrideCursor();
      QMessageBox::warning(0, "No GEOS Support!",
          "Your PostGIS installation has no GEOS support.\nFeature selection and "
          "identification will not work properly.\nPlease install PostGIS with " 
          "GEOS support (http://geos.refractions.net)");
      QApplication::setOverrideCursor(Qt::waitCursor);
    }
    //--std::cout << "Connection to the database was successful\n";
    // set the schema

    PQexec(pd,(const char *)QString("set search_path = '%1','public'").arg(schema));
    // store the connection for future use
    connection = pd;
    // check the geometry column
    QString sql = "select f_geometry_column,type,srid from geometry_columns where f_table_name='"
      + tableName + "' and f_geometry_column = '" + geometryColumn + "' and f_table_schema = '" + schema + "'";
#ifdef QGISDEBUG
    std::cerr << "Getting geometry column: " + sql << std::endl;
#endif
    PGresult *result = PQexec(pd, (const char *) sql);
    if (PQresultStatus(result) == PGRES_TUPLES_OK) {
      // this is a valid layer
      valid = true;

      //--std::cout << "geometry column query returned " << PQntuples(result) << std::endl;
      // store the srid 
      //--std::cout << "column number of srid is " << PQfnumber(result, "srid") << std::endl;
      srid = PQgetvalue(result, 0, PQfnumber(result, "srid"));
      //--std::cout << "SRID is " << srid << std::endl;

      // need to store the PostgreSQL endian format used in binary cursors
      // since it appears that starting with
      // version 7.4, binary cursors return data in XDR whereas previous versions
      // return data in the endian of the server
      QString firstOid = "select oid from " + tableName + " limit 1";
      PGresult * oidResult = PQexec(pd, firstOid);
      // get the int value from a "normal" select
      QString oidValue = PQgetvalue(oidResult,0,0);
#ifdef QGISDEBUG
      std::cerr << "Creating binary cursor" << std::endl;
#endif
      // get the same value using a binary cursor
      PQexec(pd,"begin work");
      QString oidDeclare = QString("declare oidcursor binary cursor for select oid from %1 where oid = %2").arg(tableName).arg(oidValue);
      // set up the cursor
      PQexec(pd, (const char *)oidDeclare);
      QString fetch = "fetch forward 1 from oidcursor";
#ifdef QGISDEBUG
      std::cerr << "Fecthing a record and attempting to get check endian-ness" << std::endl;
#endif
      PGresult *fResult = PQexec(pd, (const char *)fetch);
      if(PQntuples(fResult) > 0){
        // get the oid value from the binary cursor
        int oid = *(int *)PQgetvalue(fResult,0,0);

        //--std::cout << "Got oid of " << oid << " from the binary cursor" << std::endl;
        //--std::cout << "First oid is " << oidValue << std::endl;
        // compare the two oid values to determine if we need to do an endian swap
        if(oid == oidValue.toInt()){
          swapEndian = false;
        }else{
          swapEndian = true;
        }
        PQclear(fResult);

        // end the cursor transaction
        PQexec(pd, "end work");
#ifdef QGISDEBUG
        std::cerr << "Setting layer type" << std::endl;
#endif
        // set the type
        // set the simple type for use with symbology operations
        QString fType = PQgetvalue(result, 0, PQfnumber(result, "type"));
        if (fType == "POINT" || fType == "MULTIPOINT")
          geomType = QGis::WKBPoint;
        else if (fType == "LINESTRING" || fType == "MULTILINESTRING")
          geomType = QGis::WKBLineString;
        else if (fType == "POLYGON" || fType == "MULTIPOLYGON")
          geomType = QGis::WKBPolygon;
        //--std::cout << "Feature type is " << geomType << std::endl;
        //--std::cout << "Feature type name is " << QGis::qgisFeatureTypes[geomType] << std::endl;
        // free the result
        PQclear(result);
        // get the extents

        sql = "select extent(" + geometryColumn + ") from " + tableName;
        if(sqlWhereClause.length() > 0)
        {
          sql += " where " + sqlWhereClause;
        }

#if WASTE_TIME
        sql = "select xmax(extent(" + geometryColumn + ")) as xmax,"
          "xmin(extent(" + geometryColumn + ")) as xmin,"
          "ymax(extent(" + geometryColumn + ")) as ymax," "ymin(extent(" + geometryColumn + ")) as ymin" " from " + tableName;
#endif

#ifdef QGISDEBUG 
        std::cerr << "Getting extents using schema.table: " + sql << std::endl;
#endif
        result = PQexec(pd, (const char *) sql);
        std::string box3d = PQgetvalue(result, 0, 0);
        std::string s;

        box3d = box3d.substr(box3d.find_first_of("(")+1);
        box3d = box3d.substr(box3d.find_first_not_of(" "));
        s = box3d.substr(0, box3d.find_first_of(" "));
        double minx = strtod(s.c_str(), NULL);

        box3d = box3d.substr(box3d.find_first_of(" ")+1);
        s = box3d.substr(0, box3d.find_first_of(" "));
        double miny = strtod(s.c_str(), NULL);

        box3d = box3d.substr(box3d.find_first_of(",")+1);
        box3d = box3d.substr(box3d.find_first_not_of(" "));
        s = box3d.substr(0, box3d.find_first_of(" "));
        double maxx = strtod(s.c_str(), NULL);

        box3d = box3d.substr(box3d.find_first_of(" ")+1);
        s = box3d.substr(0, box3d.find_first_of(" "));
        double maxy = strtod(s.c_str(), NULL);

        layerExtent.setXmax(maxx);
        layerExtent.setXmin(minx);
        layerExtent.setYmax(maxy);
        layerExtent.setYmin(miny);
        QString xMsg;
        QTextOStream(&xMsg).precision(18);
        QTextOStream(&xMsg).width(18);
        QTextOStream(&xMsg) << "Set extents to: " << layerExtent.
          xMin() << ", " << layerExtent.yMin() << " " << layerExtent.xMax() << ", " << layerExtent.yMax();
#ifdef QGISDEBUG
        std::cerr << xMsg << std::endl;
#endif
        // clear query result
        PQclear(result);
        // get total number of features
        sql = "select count(*) from " + tableName;
        if(sqlWhereClause.length() > 0)
        {
          sql += " where " + sqlWhereClause;
        }
        result = PQexec(pd, (const char *) sql);
        numberFeatures = QString(PQgetvalue(result, 0, 0)).toLong();
        //--std::cout << "Feature count is " << numberFeatures << std::endl;
        PQclear(result);
        // selectSQL stores the select sql statement. This has to include each attribute
        // plus the geometry column in binary form
        selectSQL = "select ";
        // Populate the field vector for this layer. The field vector contains
        // field name, type, length, and precision (if numeric)
        sql = "select * from " + tableName + " limit 1";
        result = PQexec(pd, (const char *) sql);
        //--std::cout << "Field: Name, Type, Size, Modifier:" << std::endl;
        for (int i = 0; i < PQnfields(result); i++) {

          QString fieldName = PQfname(result, i);
          int fldtyp = PQftype(result, i);
          QString typOid = QString().setNum(fldtyp);
          int fieldModifier = PQfmod(result, i);
          QString sql = "select typelem from pg_type where typelem = " + typOid + " and typlen = -1";
          //  //--std::cout << sql << std::endl;
          PGresult *oidResult = PQexec(pd, (const char *) sql);
          // get the oid of the "real" type
          QString poid = PQgetvalue(oidResult, 0, PQfnumber(oidResult, "typelem"));
          PQclear(oidResult);
          sql = "select typname, typlen from pg_type where oid = " + poid;
          // //--std::cout << sql << std::endl;
          oidResult = PQexec(pd, (const char *) sql);

          QString fieldType = PQgetvalue(oidResult, 0, 0);
          QString fieldSize = PQgetvalue(oidResult, 0, 1);
          PQclear(oidResult);
        sql = "select oid from pg_class where relname = '" + tableName + "'";
        PGresult *tresult= PQexec(pd, (const char *)sql);
        QString tableoid = PQgetvalue(tresult, 0, 0);
        PQclear(tresult);
        sql = "select attnum from pg_attribute where attrelid = " + tableoid + " and attname = '" + fieldName + "'";
        tresult = PQexec(pd, (const char *)sql);
        QString attnum = PQgetvalue(tresult, 0, 0);
        PQclear(tresult);
#ifdef QGISDEBUG
          std::cerr << "Field: " << attnum << " maps to " << i << " " << fieldName << ", " 
                  << fieldType << " (" << fldtyp << "),  " << fieldSize << ", "  
                  << fieldModifier << std::endl;
#endif
        attributeFieldsIdMap[attnum.toInt()] = i;
          attributeFields.push_back(QgsField(fieldName, fieldType, fieldSize.toInt(), fieldModifier));
          // add to the select sql statement
          if(i > 0){
            selectSQL += ", ";
          }
          if(fieldType == "geometry"){
            selectSQL += "asbinary(" + geometryColumn + ",'" + endianString() + "') as qgs_feature_geometry";
          }else{
            selectSQL += fieldName;
          }
        }
        // set the primary key
        getPrimaryKey();
        selectSQL += " from " + tableName;
        //--std::cout << "selectSQL: " << (const char *)selectSQL << std::endl;
        PQclear(result);
        // get the total number of features in the layer
        sql = "select count(*) from " + tableName;
        if(sqlWhereClause.length() > 0)
        {
          sql += " where " + sqlWhereClause;
        }
        result = PQexec(pd, (const char *) sql);
        numberFeatures = QString(PQgetvalue(result, 0, 0)).toLong();
#ifdef QGISDEBUG
        std::cerr << "Number of features: " << numberFeatures << std::endl;
#endif
        PQclear(result);
      }else{
        numberFeatures = 0;
        valid = false;
      }//--std::cout << "Number of features in " << (const char *) tableName << ": " << numberFeatures << std::endl;
    } else {
      // the table is not a geometry table
      valid = false;
#ifdef QGISDEBUG
      std::cerr << "Invalid Postgres layer" << std::endl;
#endif
    }
    //      reset tableName to include schema
    schemaTableName += schema + "." + tableName;


    ready = false; // not ready to read yet cuz the cursor hasn't been created
  } else {
    valid = false;
    //--std::cout << "Connection to database failed\n";
  }
  //create a boolean vector and set every entry to false

  /*  if (valid) {
      selected = new std::vector < bool > (ogrLayer->GetFeatureCount(), false);
      } else {
      selected = 0;
      } */
  //  tabledisplay=0;
  //draw the selected features in yellow
  //  selectionColor.setRgb(255,255,0);

}


Generated by  Doxygen 1.6.0   Back to index