TerraLib - Development Source: TeAdoSqlServerSpatial.cpp Source File

00001 /************************************************************************************
00002 TerraLib - a library for developing GIS applications.
00003 Copyright � 2001-2004 INPE and Tecgraf/PUC-Rio.
00004 
00005 This code is part of the TerraLib library.
00006 This library is free software; you can redistribute it and/or
00007 modify it under the terms of the GNU Lesser General Public
00008 License as published by the Free Software Foundation; either
00009 version 2.1 of the License, or (at your option) any later version.
00010 
00011 You should have received a copy of the GNU Lesser General Public
00012 License along with this library.
00013 
00014 The authors reassure the license terms regarding the warranties.
00015 They specifically disclaim any warranties, including, but not limited to,
00016 the implied warranties of merchantability and fitness for a particular purpose.
00017 The library provided hereunder is on an "as is" basis, and the authors have no
00018 obligation to provide maintenance, support, updates, enhancements, or modifications.
00019 In no event shall INPE and Tecgraf / PUC-Rio be held liable to any party for direct,
00020 indirect, special, incidental, or consequential damages arising out of the use
00021 of this library and its documentation.
00022 *************************************************************************************/
00023 
00024 #include <stdio.h>
00025 #include <direct.h>
00026 #include <sys/stat.h>
00027 #include <TeAdoSqlServerSpatial.h>
00028 #include <TeWKBGeometryDecoder.h>
00029 #include <sys/stat.h>
00030 
00031 #include <TeUtils.h>
00032 
00033 #define CHUNKSIZE       240
00034 
00035 typedef map<int,TeNode> TeNodeMap;
00036 
00037 
00038 inline void TESTHR( HRESULT hr )
00039 {
00040         if( FAILED(hr) ) _com_issue_error( hr );
00041 }
00042 
00043 TeSqlServerSpatial::TeSqlServerSpatial()  
00044 {
00045     HRESULT hr = CoInitialize(0);
00046         dbmsName_ = "SqlServerAdoSpatial";
00047     if(FAILED(hr))
00048     {
00049         cout << "Can't start COM!? " << endl;
00050     }
00051 }
00052 
00053 TeSqlServerSpatial::~TeSqlServerSpatial()
00054 {
00055 }
00056 
00057 
00058 bool 
00059 TeSqlServerSpatial::connect (const string& host, const string& user, const string& password, const string& database, int /* port */)
00060 {
00061         
00062         std::string  connectionString;
00063         
00064         connectionString = "Provider=SQLNCLI10.1;Integrated Security='';Persist Security Info=False;User ID=";
00065         connectionString+=user + ";Initial Catalog=" + database + ";Data Source=" + host + ";Initial File Name='';Server SPN=''";
00066 
00067         connectionString = "Provider=SQLOLEDB.1;Password=" + password + ";Persist Security Info=True;User ID=" + user + ";Initial Catalog=" + database + ";Data Source=" + host;
00068 
00069         try
00070         {
00071                 connection_.CreateInstance(__uuidof(ADODB::Connection));
00072                 HRESULT hr  = connection_->Open (connectionString.c_str(),"","",-1);
00073                 TESTHR( hr );
00074         }
00075         catch(_com_error &e)
00076         {
00077                 isConnected_ = false;
00078                 errorMessage_ = e.Description();
00079                 return false;
00080         }
00081         catch(...)
00082         {
00083                 isConnected_ = false;
00084                 errorMessage_ = "Oppps !";
00085                 return false;
00086         }
00087         isConnected_ = true;
00088         database_ = database;
00089         host_ = host;
00090         user_ = user;
00091         password_ = password;
00092         return true;
00093 }
00094 
00095 
00096 bool 
00097 TeSqlServerSpatial::getMBRSelectedObjects(string /* geomTable */,string /* colGeom */, string fromClause, string whereClause, string afterWhereClause, TeGeomRep repType,TeBox &bout, const double& tol)
00098 {
00099         string  fields;
00100         string  query;
00101         bool    status = false;
00102 
00103         TeBox   box;
00104         bout = box;
00105 
00106         TeDatabasePortal* portal = this->getPortal();
00107         fields = " MIN(spatial_data.MakeValid().STEnvelope().STPointN(1).STX) as X1,";
00108         fields+= "MIN(spatial_data.MakeValid().STEnvelope().STPointN(1).STY) as Y1,";
00109         fields+= "MAX(spatial_data.MakeValid().STEnvelope().STPointN(3).STX) as X2,";
00110         fields+= "MAX(spatial_data.MakeValid().STEnvelope().STPointN(3).STY) as Y2 ";
00111         query =  " SELECT " + fields;
00112         query += " FROM " + fromClause; 
00113         if (!whereClause.empty())                       query += " WHERE " + whereClause;
00114         if (!afterWhereClause.empty())          query += afterWhereClause;
00115         if (portal->query (query))
00116                         {
00117                                 bool b = portal->fetchRow();
00118                                 while(b)
00119                                 {
00120                                         string vxmin = portal->getData(0);
00121                                         string vymin = portal->getData(1);
00122                                         string vxmax = portal->getData(2);
00123                                         string vymax = portal->getData(3);
00124                                         if(vxmin.empty() || vymin.empty() || vxmax.empty() || vymax.empty())
00125                                         {
00126                                                 b = portal->fetchRow();
00127                                                 continue;
00128                                         }
00129                                         double xmin = atof(vxmin.c_str());
00130                                         double ymin = atof(vymin.c_str());
00131                                         double xmax = atof(vxmax.c_str());
00132                                         double ymax = atof(vymax.c_str());
00133                                         TeBox   ibox(xmin, ymin, xmax, ymax);
00134                                         updateBox (bout, ibox);
00135                                         b = portal->fetchRow();
00136                                         status = true;
00137                                 }
00138                         }
00139         delete portal;
00140         return status;
00141 }
00142 
00143 
00144 string
00145 TeSqlServerSpatial::getSQLBoxWhere(const TeBox& box, const TeGeomRep rep, const std::string& )
00146 {
00147         std::string whereBox;
00148 
00149         whereBox=" spatial_data.STIsValid() = 1 and spatial_data.STIntersects (geometry::STGeomFromText('POLYGON((";
00150         whereBox+=Te2String(box.x1());
00151         whereBox+=" ";
00152         whereBox+=Te2String(box.y1());
00153         whereBox+=",";
00154         whereBox+=Te2String(box.x2());
00155         whereBox+=" ";
00156         whereBox+=Te2String(box.y1());
00157         whereBox+=",";
00158         whereBox+=Te2String(box.x2());
00159         whereBox+=" ";
00160         whereBox+=Te2String(box.y2());
00161         whereBox+=",";
00162         whereBox+=Te2String(box.x1());
00163         whereBox+=" ";
00164         whereBox+=Te2String(box.y2());
00165         whereBox+=",";
00166         whereBox+=Te2String(box.x1());
00167         whereBox+=" ";
00168         whereBox+=Te2String(box.y1());
00169         whereBox+="))',0))=1 ";
00170         return whereBox;
00171 }
00172 
00173 std::string TeSqlServerSpatial::getSQLOrderBy(const TeGeomRep& rep) const
00174 {
00175         std::string orderBy = "object_id DESC";
00176         return orderBy;
00177 }
00178 
00179 
00180 bool 
00181 TeSqlServerSpatial::columnExist(const string& table, const string& column, TeAttribute& attr)
00182 {
00183         TeSqlServerSpatialPortal *sqlp = (TeSqlServerSpatialPortal*)getPortal();
00184         if(!sqlp)
00185                 return false;
00186 
00187         string exist ="select name,xtype,length,xscale  from syscolumns where name='" + column + "' and ";
00188         exist+=" id in(select id from sysobjects where xtype='U' and name='" + table + "')";
00189 
00190         
00191         if(!sqlp->query(exist))
00192         {
00193                 delete sqlp;
00194                 return false;
00195         }
00196 
00197         if(sqlp->fetchRow())
00198         {       
00199                 attr.rep_.name_ = column;
00200                 
00201                 int     dataType = atoi(sqlp->getData(1)); 
00202                 int             dataLength = atoi(sqlp->getData(2)); 
00203                 attr.rep_.numChar_ = dataLength;
00204                 if(dataType == 167 && dataLength < 256)
00205                 {
00206                         attr.rep_.type_ = TeSTRING;
00207                 }
00208                 else if (dataType == 167 && dataLength >= 256)
00209                 {
00210                         attr.rep_.type_ = TeBLOB;
00211                 }
00212                 else if (dataType == 34)
00213                 {
00214                         attr.rep_.type_ = TeBLOB;
00215                 }
00216                 else if (dataType == 56)
00217                 {
00218                         attr.rep_.type_ = TeREAL;
00219                 }
00220                 else if (dataType == 62)
00221                 {
00222                         attr.rep_.type_ = TeINT;
00223                 }
00224                 else if (dataType == 167 && dataLength == 0)
00225                 {       
00226                         attr.rep_.type_ = TeCHARACTER;
00227                 }
00228                 else if (dataType == 61)
00229                 {
00230                         attr.rep_.type_ = TeDATETIME;
00231                 }
00232                 else if (dataType == 240)
00233                 {
00234                         attr.rep_.type_ = TeOBJECT;
00235                 }
00236                 else
00237                 {
00238                         attr.rep_.type_ = TeSTRING;
00239                         attr.rep_.numChar_ = dataLength;
00240                 }
00241                 delete sqlp;
00242                 return true;
00243         }
00244         delete sqlp;
00245         return false;
00246 }
00247 
00248 
00249 
00250 bool
00251 TeSqlServerSpatial::createTable(const string& table, TeAttributeList &attr)
00252 {
00253         bool first = true;
00254         string pkeys ="";
00255 
00256         if(tableExist(table))
00257         {
00258                 errorMessage_= "Table already exist!";
00259                 return false;
00260         }
00261 
00262         TeAttributeList::iterator it = attr.begin();
00263         string tablec;
00264         tablec = "CREATE TABLE dbo." + table +" (";
00265         
00266         while ( it != attr.end())
00267         {
00268                 if (first == false)
00269                 {
00270                         tablec += ", ";
00271                 }
00272                         
00273                 switch ((*it).rep_.type_)
00274                 {
00275                         case TeSTRING:
00276                                 if ( (*it).rep_.numChar_ > 0 && (*it).rep_.numChar_ < 256)
00277                                    tablec += (*it).rep_.name_ + " VARCHAR(" + Te2String((*it).rep_.numChar_) + ") ";
00278                                 else
00279                                    tablec += (*it).rep_.name_ + " TEXT "; 
00280                         break;
00281                         
00282                         case TeREAL:
00283                                 tablec += (*it).rep_.name_ + " DOUBLE PRECISION ";
00284                         break;
00285                         
00286                         case TeINT:
00287                         case TeUNSIGNEDINT:
00288                                 tablec += (*it).rep_.name_ + " INT ";
00289                         break; 
00290 
00291                         case TeDATETIME:
00292                                 tablec += (*it).rep_.name_ + " DATETIME ";
00293                         break;
00294 
00295                         case TeCHARACTER:
00296                                 tablec += (*it).rep_.name_ + " CHAR ";
00297                         break;
00298 
00299                         case TeBOOLEAN:
00300                                 tablec += (*it).rep_.name_ + " BIT ";
00301                         break;
00302 
00303                         case TeBLOB:
00304                                 tablec += (*it).rep_.name_ + " IMAGE ";
00305                         break;
00306 
00307                         case TePOINTTYPE:
00308                         case TePOINTSETTYPE:
00309                         case TeNODETYPE:
00310                         case TeNODESETTYPE:
00311                         case TeLINE2DTYPE:
00312                         case TeLINESETTYPE:
00313                         case TePOLYGONTYPE:
00314                         case TePOLYGONSETTYPE:
00315                         case TeCELLTYPE:
00316                         case TeCELLSETTYPE:
00317                                 tablec += " spatial_data geometry";
00318                                 ++it;
00319                                 continue;
00320 
00321                         case TeRASTERTYPE:
00322                                 tablec += " lower_x DOUBLE PRECISION NOT NULL, ";
00323                                 tablec += " lower_y DOUBLE PRECISION NOT NULL, ";
00324                                 tablec += " upper_x DOUBLE PRECISION NOT NULL, ";
00325                                 tablec += " upper_y DOUBLE PRECISION NOT NULL, ";
00326                                 tablec += " band_id INT NOT NULL, ";
00327                                 tablec += " resolution_factor INT NOT NULL , ";
00328                                 tablec += " subband INT ,";
00329                                 tablec += " spatial_data IMAGE, ";
00330                                 tablec += " block_size INT NOT NULL ";
00331                                 ++it;
00332                                 continue;
00333 
00334             case TeTEXTTYPE:
00335                         case TeTEXTSETTYPE:
00336                         default:
00337                                 tablec += (*it).rep_.name_ + " VARCHAR(255) ";
00338                         break;
00339                 }
00340 
00341                 //default value
00342                 if(!((*it).rep_.defaultValue_.empty()))
00343             tablec += " DEFAULT '" + (*it).rep_.defaultValue_ +"' " ;
00344 
00345                 //not null
00346                 if(!((*it).rep_.null_))
00347                         tablec += " NOT NULL ";
00348                 
00349                 // auto number
00350                 if((*it).rep_.isAutoNumber_ && ((*it).rep_.type_==TeINT || (*it).rep_.type_==TeUNSIGNEDINT))  
00351                         tablec += " IDENTITY(1,1) "; 
00352 
00353                 // check if column is part of primary key
00354                 if ((*it).rep_.isPrimaryKey_ && (*it).rep_.type_ != TeBLOB )
00355                 {
00356                         if (!pkeys.empty())
00357                                 pkeys += ", ";
00358                         pkeys += (*it).rep_.name_;
00359                 }
00360 
00361                 ++it;
00362                 first = false;
00363         }
00364 
00365         if(!pkeys.empty())
00366                 tablec += ", PRIMARY KEY (" + pkeys + ") ";
00367 
00368         tablec += ")";
00369 
00370         if(!execute(tablec))
00371         {
00372                 if(errorMessage_.empty())
00373                         errorMessage_ = "Error creating table " + table;
00374 
00375                 return false;
00376         }
00377         return true;
00378 }
00379 
00380 bool 
00381 TeSqlServerSpatial::alterTable (const string& table, TeAttributeRep &rep, const string& oldColName)
00382 {
00383         if(!tableExist(table))
00384                 return false;
00385 
00386         if(!oldColName.empty() && oldColName != rep.name_)
00387         {
00388                 HRESULT hr = S_OK;
00389                 ADOX::_CatalogPtr m_pCatalog   = NULL;
00390                 ADOX::_TablePtr m_pTable  = NULL;
00391                 ADOX::_ColumnPtr m_pColumn  = NULL;
00392                 try
00393                 {
00394                         TESTHR(hr = m_pCatalog.CreateInstance(__uuidof (ADOX::Catalog)));
00395                         m_pCatalog->PutActiveConnection(variant_t((IDispatch *)connection_));
00396 
00397                         m_pTable= m_pCatalog->Tables->GetItem(table.c_str());
00398                         m_pColumn = m_pTable->Columns->GetItem(oldColName.c_str()); 
00399                         m_pColumn->Name = rep.name_.c_str();
00400                         m_pCatalog->Tables->Refresh();                                                          // Refresh the database.
00401                         m_pCatalog = NULL;
00402                         m_pTable  = NULL;
00403                 }
00404                 catch(_com_error &e)
00405                 {
00406                         errorMessage_ = e.Description();
00407                         m_pCatalog   = NULL;
00408                         m_pTable  = NULL;
00409                         return false;
00410                 }
00411 
00412                 catch(...)
00413                 {
00414                         errorMessage_ = "Error alter table";
00415                         m_pCatalog   = NULL;
00416                         m_pTable  = NULL;
00417                         return false;
00418                 }
00419         }
00420 
00421         string tab = " ALTER TABLE " + table + " ALTER COLUMN ";
00422         tab += rep.name_ + "  ";
00423         switch (rep.type_)
00424         {
00425                 case TeSTRING:
00426                         if (rep.numChar_ > 255 || rep.numChar_ == 0)
00427                                 tab += "TEXT ";
00428                         else
00429                                 tab += "VARCHAR(" + Te2String(rep.numChar_) + ") ";
00430                         break;
00431                 
00432                 case TeREAL:
00433                         tab += "FLOAT"; 
00434                         break;
00435                 
00436                 case TeINT:
00437                         tab += "INT";
00438                         break;
00439 
00440                 case TeDATETIME:
00441                         tab += "DATE";
00442                         break;
00443 
00444                 case TeCHARACTER:
00445                         tab += "CHAR";
00446                         break;
00447 
00448                 case TeBOOLEAN:
00449                         tab += "BIT";
00450                         break;
00451                 
00452                 default:
00453                         if (rep.numChar_ > 255 || rep.numChar_ == 0)
00454                                 tab += "TEXT ";
00455                         else
00456                                 tab += "VARCHAR(" + Te2String(rep.numChar_) + ") ";
00457                         break;
00458         }
00459 
00460         tab += " NULL ";
00461 
00462         if(!execute(tab))
00463         {
00464                 if(errorMessage_.empty())
00465                         errorMessage_ = "Error alter table " + table + " !";
00466                 return false;
00467         }
00468                 
00469         string tableId;
00470         TeDatabasePortal* portal = getPortal();
00471         string sql = "SELECT table_id FROM te_layer_table WHERE attr_table = '" + table + "'";
00472         if(portal->query(sql) && portal->fetchRow())
00473                 tableId = portal->getData(0);
00474 
00475         delete portal;
00476 
00477         if(tableId.empty() == false)
00478         {
00479                 if(oldColName.empty() == false) // column name changed
00480                 {
00481                          // update relation
00482                         sql = "UPDATE te_tables_relation SET related_attr = '" + rep.name_ + "'";
00483                         sql += " WHERE related_table_id = " + tableId;
00484                         sql += " AND related_attr = '" + oldColName + "'";
00485                         if(execute(sql) == false)
00486                                 return false;
00487 
00488                         sql = "UPDATE te_tables_relation SET external_attr = '" + rep.name_ + "'";
00489                         sql += " WHERE external_table_name = '" + table + "'";
00490                         sql += " AND external_attr = '" + oldColName + "'";
00491                         if(execute(sql) == false)
00492                                 return false;
00493 
00494                          // update grouping
00495                         sql = "UPDATE te_grouping SET grouping_attr = '" + table + "." + rep.name_ + "'";
00496                         sql += " WHERE grouping_attr = '" + table + "." + oldColName + "'";
00497                         if(execute(sql) == false)
00498                                 return false;
00499                 }
00500                 else // column type changed
00501                 {
00502                         // delete relation
00503                         sql = "DELETE FROM te_tables_relation WHERE (related_table_id = " + tableId;
00504                         sql += " AND related_attr = '" + rep.name_ + "')";
00505                         sql += " OR (external_table_name = '" + table + "'";
00506                         sql += " AND external_attr = '" + rep.name_ + "')";
00507                         if(execute(sql) == false)
00508                                 return false;
00509 
00510                         // delete grouping
00511                         TeDatabasePortal* portal = getPortal();
00512                         sql = "SELECT theme_id FROM te_grouping WHERE grouping_attr = '" + table + "." + oldColName + "'";
00513                         if(portal->query(sql) && portal->fetchRow())
00514                         {
00515                                 string themeId = portal->getData(0);
00516 
00517                                 sql = "DELETE FROM te_legend WHERE theme_id = " + themeId + " AND group_id >= 0";
00518                                 if(execute(sql) == false)
00519                                 {
00520                                         delete portal;
00521                                         return false;
00522                                 }
00523                         }
00524                         delete portal;
00525 
00526                         sql = "DELETE FROM te_grouping";
00527                         sql += " WHERE grouping_attr = '" + table + "." + oldColName + "'";
00528                         if(execute(sql) == false)
00529                                 return false;
00530                 }
00531         }
00532         alterTableInfoInMemory(table);
00533         return true;
00534 }
00535 
00536 
00537 
00538 
00539 
00540 TeDatabasePortal*  
00541 TeSqlServerSpatial::getPortal ()
00542 {
00543         TeSqlServerSpatialPortal* portal = new TeSqlServerSpatialPortal (this);
00544         return portal;
00545 }
00546 
00547 
00548 
00549 
00550 bool 
00551 TeSqlServerSpatial::generateLabelPositions (TeTheme *theme, const std::string& objectId )
00552 {
00553         string  geomTable, upd;
00554         string  collTable = theme->collectionTable();
00555         
00556         if((collTable.empty()) || (!tableExist(collTable)))
00557                 return false;
00558 
00559         if(theme->layer()->hasGeometry(TePOLYGONS))             geomTable = theme->layer()->tableName(TePOLYGONS);
00560         else if(theme->layer()->hasGeometry(TeLINES))   geomTable = theme->layer()->tableName(TeLINES);
00561         else if(theme->layer()->hasGeometry(TePOINTS))  geomTable = theme->layer()->tableName(TePOINTS);
00562         else if(theme->layer()->hasGeometry(TeCELLS))   geomTable = theme->layer()->tableName(TeCELLS);
00563 
00564         upd="update " + collTable +" set label_x=(select MAX(spatial_data.MakeValid().STCentroid ( ).STX) from ";
00565         upd+=geomTable + " where spatial_data.STIsValid()=1 and object_id=c_object_id), ";
00566         upd+=" label_y=(select MAX(spatial_data.MakeValid().STCentroid ( ).STY) from ";
00567         upd+=geomTable + " where spatial_data.STIsValid()=1 and object_id=c_object_id) ";
00568         upd += " WHERE label_x IS NULL OR label_y IS NULL";
00569 
00570         if (!upd.empty())
00571         {
00572                 if (!objectId.empty())
00573                 {
00574                         upd += " AND c_object_id='"+objectId+"'";
00575                 }
00576                 if(!execute(upd))
00577                         return false;
00578         }
00579 
00580         return true;
00581 }
00582 
00583 
00584 
00585 std::string TeSqlServerSpatial::STGeomFromText(const TePolygon &polygon)
00586 {
00587         std::string                                     sql;
00588         TeLine2D                                        lnePolygon;
00589         TeLine2D::iterator                      it;
00590 
00591         lnePolygon.copyElements(polygon[0]);
00592 
00593         sql=" geometry::STGeomFromText('POLYGON ((";
00594         for(it=lnePolygon.begin();it!=lnePolygon.end();it++)
00595         {
00596                 if(it!=lnePolygon.begin()) sql+=",";
00597                 sql+=Te2String((*it).x());
00598                 sql+=" ";
00599                 sql+=Te2String((*it).y());
00600         }
00601         sql+="))',0)";
00602         return sql;
00603 }
00604 
00605 std::string TeSqlServerSpatial::STGeomFromWkb(const TePolygon &polygon)
00606 {
00607         std::string                                     sql;
00608         TeLine2D                                        lnePolygon;
00609         TeLine2D::iterator                      it;
00610         unsigned int                            size;
00611         char                                            *wkbPol;
00612 
00613         
00614 
00615         TeWKBGeometryDecoder::encodePolygon(polygon,wkbPol,size);
00616 
00617         sql=" geometry::STGeomFromWKB(";
00618         sql+=wkbPol;
00619         sql+="),0)";
00620         return sql;
00621 }
00622 
00623 
00624 bool
00625 TeSqlServerSpatial::createSpatialIndex(const string& table, const string& columns, TeSpatialIndexType /*type*/, short /*level*/, short /*tile*/)
00626 {
00627         std::string                     SQL;
00628         double                          x1=0,y1=0;
00629         double                          x2=0,y2=0;
00630         TeDatabasePortal        *dbPortal=0;
00631 
00632         SQL=" SELECT MIN(spatial_data.MakeValid().STEnvelope().STPointN(1).STX) as X1,";
00633         SQL+=" MIN(spatial_data.MakeValid().STEnvelope().STPointN(1).STY) as Y1,";
00634         SQL+=" MAX(spatial_data.MakeValid().STEnvelope().STPointN(3).STX) as X2,";
00635         SQL+=" MAX(spatial_data.MakeValid().STEnvelope().STPointN(3).STY) as Y2 ";
00636         SQL+=" FROM " + table;
00637 
00638         dbPortal= this->getPortal();
00639         if(dbPortal)
00640         {
00641                 if( dbPortal->query(SQL) && dbPortal->fetchRow())
00642                 {
00643                         x1=dbPortal->getDouble("X1");
00644                         y1=dbPortal->getDouble("Y1");
00645                         x2=dbPortal->getDouble("X2");
00646                         y2=dbPortal->getDouble("Y2");
00647                 }
00648                 delete dbPortal;
00649         }
00650 
00651         SQL="CREATE SPATIAL INDEX SP_IDX_SPATIAL_" + table;
00652         SQL+=" ON " + table + "(spatial_data)";
00653         SQL+=" USING GEOMETRY_GRID WITH (";
00654         SQL+=" BOUNDING_BOX = ( xmin=" + Te2String(x1);
00655         SQL+=", ymin=" + Te2String(y1);
00656         SQL+=", xmax=" + Te2String(x2);
00657         SQL+=", ymax=" + Te2String(y2);
00658         SQL+="), GRIDS = (MEDIUM, MEDIUM, MEDIUM, MEDIUM),";
00659         SQL+=" CELLS_PER_OBJECT = 64,  PAD_INDEX  = ON );";
00660         return this->execute(SQL);
00661 }
00662 
00663 
00664 bool 
00665 TeSqlServerSpatial::PutBinaryIntoVariant(VARIANT &ovData, BYTE * pBuf,unsigned long cBufLen)
00666 {
00667         bool fRetVal = false;
00668         
00669         VariantInit(&ovData); 
00670 
00671         ovData.vt = VT_ARRAY | VT_UI1;
00672         SAFEARRAYBOUND rgsabound[1];
00673         rgsabound[0].cElements = cBufLen;
00674         rgsabound[0].lLbound = 0;
00675 
00676         ovData.parray = SafeArrayCreate(VT_UI1,1,rgsabound);
00677         if(ovData.parray != NULL)
00678                 {
00679                         void * pArrayData = NULL;
00680                         SafeArrayAccessData(ovData.parray,&pArrayData);
00681                         memcpy(pArrayData, pBuf, cBufLen);
00682                         SafeArrayUnaccessData(ovData.parray);
00683                         fRetVal = true;
00684                 }
00685         return fRetVal;
00686 }
00687 
00688 bool 
00689 TeSqlServerSpatial::insertPolygonSet (const string& table, TePolygonSet &ps)
00690 {
00691         string                  sql;
00692         unsigned int    i;
00693         unsigned int    size=0;
00694         int                             geom_id;
00695         char                    *wkb;
00696 
00697         ADODB::_RecordsetPtr recset;
00698         recset.CreateInstance(__uuidof(ADODB::Recordset));
00699 
00700 try
00701 {
00702         if (ps.empty()) return true;
00703         for(i=0;i<ps.size(); i++)
00704         {
00705                 sql=" insert into " + table;
00706                 sql+=" (object_id,spatial_data) values('";
00707                 sql+=ps[i].objectId();
00708                 sql+="',geometry::STPolyFromWKB(?,0))";
00709 
00710                 
00711                 TeWKBGeometryDecoder::encodePolygon(ps[i],wkb,size);
00712                 VARIANT varWKB;
00713                 
00714                 if(PutBinaryIntoVariant(varWKB,(BYTE*)wkb,size))
00715                 {
00716                         ADODB::_CommandPtr              comm;
00717                         ADODB::_ParameterPtr    param;
00718 
00719                         comm.CreateInstance(__uuidof(ADODB::Command)); 
00720                         comm->ActiveConnection = connection_;
00721                         comm->CommandType=ADODB::adCmdText;
00722                         comm->CommandText=sql.c_str();
00723 
00724                         param = comm->CreateParameter(_bstr_t(L""),ADODB::adVarBinary,ADODB::adParamInput,-1);
00725                         param->Value=varWKB;
00726                         comm->Parameters->Append(param);
00727                         comm->Execute(0,0,ADODB::adCmdText);
00728                         SafeArrayDestroy (varWKB.parray);
00729                         delete wkb;
00730                 }
00731                 sql="select @@IDENTITY as id from " + table;
00732                 recset->Open(_bstr_t(sql.c_str()), _variant_t((IDispatch*)connection_, true),
00733             ADODB::adOpenForwardOnly,ADODB::adLockReadOnly,ADODB::adCmdText);
00734                 geom_id=recset->GetCollect("id").intVal;
00735                 ps[i].geomId(geom_id);
00736                 recset->Close();
00737         }
00738 }catch(_com_error &e)
00739 {
00740                 errorMessage_ = e.Description();
00741                 if(recset->State == 1)  recset->Close();
00742                 return false;
00743 }
00744 catch(...)
00745 {
00746         errorMessage_ = "Oppps !";
00747         recset->Close();
00748         return false;
00749 }
00750         return true;
00751 }
00752 
00753 
00754 bool 
00755 TeSqlServerSpatial::insertPolygon (const string& table, TePolygon &pol)
00756 {
00757         TePolygonSet    pls;
00758         pls.add(pol);
00759         return this->insertPolygonSet(table,pls);
00760 }
00761 
00762 
00763 std::string TeSqlServerSpatial::STGeomFromText(const TeLine2D &line)
00764 {
00765         std::string                                     sql;
00766         TeLine2D::iterator                      it;
00767 
00768 
00769         sql=" geometry::STGeomFromText('LINESTRING (";
00770         for(it=line.begin();it!=line.end();it++)
00771         {
00772                 if(it!=line.begin()) sql+=",";
00773                 sql+=Te2String((*it).x());
00774                 sql+=" ";
00775                 sql+=Te2String((*it).y());
00776         }
00777         sql+=")',0)";
00778         return sql;
00779 }
00780 
00781 //std::string TeSqlServerSpatial::STGeomFromWkb(const TePolygon &polygon)
00782 //{
00783 //
00784 //
00785 //}
00786 
00787 bool
00788 TeSqlServerSpatial::insertLineSet(const string& table, TeLineSet &ls)
00789 {
00790         string                  sql;
00791         unsigned int    i;
00792         unsigned int    size=0;
00793         int                             geom_id;
00794         char                    *wkb;
00795 
00796         ADODB::_RecordsetPtr recset;
00797         recset.CreateInstance(__uuidof(ADODB::Recordset));
00798 
00799 try
00800 {
00801         if (ls.empty()) return true;
00802         for(i=0;i<ls.size(); i++)
00803         {
00804                 sql=" insert into " + table;
00805                 sql+=" (object_id,spatial_data) values('";
00806                 sql+=ls[i].objectId();
00807                 sql+="',geometry::STLineFromWKB(?,0))";
00808 
00809                 
00810                 TeWKBGeometryDecoder::encodeLine(ls[i],wkb,size);
00811                 VARIANT varWKB;
00812                 
00813                 if(PutBinaryIntoVariant(varWKB,(BYTE*)wkb,size))
00814                 {
00815                         ADODB::_CommandPtr              comm;
00816                         ADODB::_ParameterPtr    param;
00817 
00818                         comm.CreateInstance(__uuidof(ADODB::Command)); 
00819                         comm->ActiveConnection = connection_;
00820                         comm->CommandType=ADODB::adCmdText;
00821                         comm->CommandText=sql.c_str();
00822 
00823                         param = comm->CreateParameter(_bstr_t(L""),ADODB::adVarBinary,ADODB::adParamInput,-1);
00824                         param->Value=varWKB;
00825                         comm->Parameters->Append(param);
00826                         comm->Execute(0,0,ADODB::adCmdText);
00827                         SafeArrayDestroy (varWKB.parray);
00828                         delete wkb;
00829                 }
00830                 sql="select @@IDENTITY as id from " + table;
00831                 recset->Open(_bstr_t(sql.c_str()), _variant_t((IDispatch*)connection_, true),
00832             ADODB::adOpenForwardOnly,ADODB::adLockReadOnly,ADODB::adCmdText);
00833                 geom_id=recset->GetCollect("id").intVal;
00834                 ls[i].geomId(geom_id);
00835                 recset->Close();
00836         }
00837 }catch(_com_error &e)
00838 {
00839                 errorMessage_ = e.Description();
00840                 if(recset->State == 1)  recset->Close();
00841                 return false;
00842 }
00843 catch(...)
00844 {
00845         errorMessage_ = "Oppps !";
00846         recset->Close();
00847         return false;
00848 }
00849         return true;
00850 }
00851 
00852 bool
00853 TeSqlServerSpatial::insertLine(const string& table, TeLine2D &l)
00854 {
00855     TeLineSet   lns;
00856         lns.add(l);
00857         return this->insertLineSet(table,lns);
00858 }
00859 
00860 std::string TeSqlServerSpatial::STGeomFromText(const TePoint &point)
00861 {
00862         std::string                                     sql;
00863         sql=" geometry::STGeomFromText('POINT (";
00864         sql+=Te2String(point.location().x());
00865         sql+=" ";
00866         sql+=Te2String(point.location().y());
00867         sql+=")',0)";
00868         return sql;
00869 }
00870 
00871 bool
00872 TeSqlServerSpatial::insertPointSet(const string& table, TePointSet &ps)
00873 {
00874         string                  sql;
00875         unsigned int    i;
00876         unsigned int    size=0;
00877         int                             geom_id;
00878         char                    *wkb;
00879 
00880         ADODB::_RecordsetPtr recset;
00881         recset.CreateInstance(__uuidof(ADODB::Recordset));
00882 
00883 try
00884 {
00885         if (ps.empty()) return true;
00886         for(i=0;i<ps.size(); i++)
00887         {
00888                 sql=" insert into " + table;
00889                 sql+=" (object_id,spatial_data) values('";
00890                 sql+=ps[i].objectId();
00891                 sql+="',geometry::STPointFromWKB(?,0))";
00892 
00893                 
00894                 TeWKBGeometryDecoder::encodePoint(ps[i].location(),wkb,size);
00895                 VARIANT varWKB;
00896                 
00897                 if(PutBinaryIntoVariant(varWKB,(BYTE*)wkb,size))
00898                 {
00899                         ADODB::_CommandPtr              comm;
00900                         ADODB::_ParameterPtr    param;
00901 
00902                         comm.CreateInstance(__uuidof(ADODB::Command)); 
00903                         comm->ActiveConnection = connection_;
00904                         comm->CommandType=ADODB::adCmdText;
00905                         comm->CommandText=sql.c_str();
00906 
00907                         param = comm->CreateParameter(_bstr_t(L""),ADODB::adVarBinary,ADODB::adParamInput,-1);
00908                         param->Value=varWKB;
00909                         comm->Parameters->Append(param);
00910                         comm->Execute(0,0,ADODB::adCmdText);
00911                         SafeArrayDestroy (varWKB.parray);
00912                         delete wkb;
00913                 }
00914                 sql="select @@IDENTITY as id from " + table;
00915                 recset->Open(_bstr_t(sql.c_str()), _variant_t((IDispatch*)connection_, true),
00916             ADODB::adOpenForwardOnly,ADODB::adLockReadOnly,ADODB::adCmdText);
00917                 geom_id=recset->GetCollect("id").intVal;
00918                 ps[i].geomId(geom_id);
00919                 recset->Close();
00920         }
00921 }catch(_com_error &e)
00922 {
00923                 errorMessage_ = e.Description();
00924                 if(recset->State == 1)  recset->Close();
00925                 return false;
00926 }
00927 catch(...)
00928 {
00929         errorMessage_ = "Oppps !";
00930         recset->Close();
00931         return false;
00932 }
00933         return true;
00934 }
00935 
00936 bool
00937 TeSqlServerSpatial::insertPoint(const string& table, TePoint &p)
00938 {
00939    
00940     TePointSet  pts;
00941         pts.add(p);
00942         return this->insertPointSet(table,pts);
00943 }
00944 
00945 
00946 bool 
00947 TeSqlServerSpatial::createTextGeometry(const string& table)
00948 {
00949         if(table.empty())
00950                 return false;
00951 
00952         TeAttributeList attList;
00953 
00954         {TeAttribute attGeomId;
00955         attGeomId.rep_.name_ = "geom_id";
00956         attGeomId.rep_.type_ = TeUNSIGNEDINT;
00957         attGeomId.rep_.isAutoNumber_ = true;
00958         attGeomId.rep_.isPrimaryKey_ = true;
00959         attGeomId.rep_.null_ = false;
00960         attList.push_back(attGeomId);}
00961 
00962         {TeAttribute attObjId;
00963         attObjId.rep_.name_ = "object_id";
00964         attObjId.rep_.type_ = TeSTRING;
00965         attObjId.rep_.numChar_ = 255;
00966         attObjId.rep_.null_ = false;
00967         attList.push_back(attObjId);}
00968 
00969 
00970         {TeAttribute attSpatial;
00971         attSpatial.rep_.name_ = "spatial_data";
00972         attSpatial.rep_.type_ = TePOINTTYPE;
00973         attList.push_back(attSpatial);}
00974 
00975         {TeAttribute attTextValue;
00976         attTextValue.rep_.name_ = "text_value";
00977         attTextValue.rep_.type_ = TeSTRING;
00978         attTextValue.rep_.numChar_ = 255;
00979         attList.push_back(attTextValue);}
00980 
00981         {TeAttribute attAngle;
00982         attAngle.rep_.name_ = "angle";
00983         attAngle.rep_.type_ = TeREAL;
00984         attAngle.rep_.decimals_ = 15;
00985         attAngle.rep_.defaultValue_ = "0.0";
00986         attList.push_back(attAngle);}
00987 
00988         {TeAttribute attHeight;
00989         attHeight.rep_.name_ = "height";
00990         attHeight.rep_.type_ = TeREAL;
00991         attHeight.rep_.decimals_ = 15;
00992         attHeight.rep_.defaultValue_ = "0.0";
00993         attList.push_back(attHeight);}
00994 
00995         {TeAttribute attAlignVert;
00996         attAlignVert.rep_.name_ = "alignment_vert";
00997         attAlignVert.rep_.type_ = TeREAL;
00998         attAlignVert.rep_.decimals_ = 15;
00999         attList.push_back(attAlignVert);}
01000 
01001         {TeAttribute attAlignHoriz;
01002         attAlignHoriz.rep_.name_ = "alignment_horiz";
01003         attAlignHoriz.rep_.type_ = TeREAL;
01004         attAlignHoriz.rep_.decimals_ = 15;
01005         attList.push_back(attAlignHoriz);}
01006 
01007         if(!createTable(table, attList))
01008                 return false;
01009 
01010         string idxName = "te_idx_"  + table + "_obj";
01011 
01012         if(!createIndex(table, idxName, "object_id"))
01013                 return false;
01014 
01015         idxName = "te_idx_"  + table + "_pos";
01016 
01017         return createIndex(table, idxName, "x, y");
01018 }
01019 
01020 bool
01021 TeSqlServerSpatial::insertTextSet(const string& table, TeTextSet &ts)
01022 {
01023     if (ts.empty())
01024         return true;
01025 
01026     string sql = "select * from ";
01027     sql += table;
01028     sql += " where 1=0";        
01029 
01030     ADODB::_RecordsetPtr recset_;
01031     recset_.CreateInstance(__uuidof(ADODB::Recordset));
01032     try
01033     {
01034         recset_->Open(_bstr_t(sql.c_str()),
01035             _variant_t((IDispatch*)connection_, true),
01036             ADODB::adOpenKeyset,
01037             ADODB::adLockOptimistic,
01038             ADODB::adCmdText);
01039 
01040         for (unsigned int i = 0; i < ts.size(); ++i)
01041         {
01042             recset_->AddNew();
01043             recset_->Fields->GetItem("object_id")->Value = (_bstr_t) (ts[i].objectId().c_str());
01044             recset_->Fields->GetItem("x")->Value = (_variant_t) (TeRoundD(ts[i].location().x_,15));
01045             recset_->Fields->GetItem("y")->Value = (_variant_t) (TeRoundD(ts[i].location().y_,15));
01046             recset_->Fields->GetItem("text_value")->Value = (_bstr_t) (ts[i].textValue().c_str());
01047             recset_->Fields->GetItem("angle")->Value = (_variant_t) (ts[i].angle());
01048             recset_->Fields->GetItem("height")->Value = (_variant_t) (ts[i].height());
01049             recset_->Fields->GetItem("alignment_vert")->Value = (_variant_t) (ts[i].alignmentVert());
01050             recset_->Fields->GetItem("alignment_horiz")->Value = (_variant_t) (ts[i].alignmentHoriz());
01051                         recset_->Update();
01052             ts[i].geomId (recset_->GetCollect("geom_id").intVal);
01053         }
01054         recset_->Close();
01055     }
01056     catch(_com_error &e)
01057     {
01058         errorMessage_ = e.Description();
01059         recset_->Close();
01060         return 0;
01061     }
01062     catch(...)
01063     {
01064         errorMessage_ = "Oppps !";
01065         recset_->Close();
01066         return 0;
01067     }
01068     return true;
01069 }
01070 
01071 bool
01072 TeSqlServerSpatial::insertText(const string& table, TeText &t)
01073 {
01074     TeTextSet   txs;
01075         txs.add(t);
01076         return this->insertTextSet(table,txs);
01077 }
01078 
01079 bool 
01080 TeSqlServerSpatial::locatePoint (const string& table, TeCoord2D &pt, TePoint &point, const double& tol)
01081 {
01082         TePointSet                              ps;
01083         int                                             k;
01084         TeDatabasePortal                *portal = this->getPortal();
01085         bool                                    flag;
01086 
01087         TeBox box (pt.x()-tol,pt.y()-tol,pt.x()+tol,pt.y()+tol);
01088         string q ="SELECT geom_id, object_id, spatial_data.STAsBinary() as spatial_data FROM " + table;
01089         q+=" where ";
01090         q+=this->getSQLBoxWhere(box,TePOINTS,table);
01091         if (!portal->query(q) || !portal->fetchRow())
01092         {
01093                 delete portal;
01094                 return false;
01095         }
01096         
01097         do 
01098         {
01099                 TePoint point;
01100                 flag = portal->fetchGeometry (point);
01101                 ps.add ( point );
01102         }while (flag);
01103         delete portal;
01104         if (TeNearest (pt, ps, k, tol))
01105         {
01106                 point = ps[k];
01107                 return true;
01108         }
01109         return false;
01110 }
01111 
01112 
01113 bool 
01114 TeSqlServerSpatial::locatePolygon (const string& table, TeCoord2D &pt, TePolygon &polygon, const double& tol)
01115 {
01116         bool                                    flag;
01117         TeDatabasePortal                *portal = this->getPortal();
01118 
01119         if (!portal)                    return false;
01120 
01121         std::string q;
01122         q="select geom_id, object_id, spatial_data.STAsBinary() as spatial_data from ";
01123         q+=table;
01124         q+=" where spatial_data.STContains(geometry::STGeomFromText('POINT(";
01125         q+=Te2String(pt.x());
01126         q+=" ";
01127         q+=Te2String(pt.y());
01128         q+=")',0))=1";
01129         
01130         if (!portal->query(q) || !portal->fetchRow())
01131         {       
01132                 delete portal;
01133                 return false;
01134         }
01135         
01136         do
01137         {
01138                 flag = portal->fetchGeometry(polygon);
01139         }while (flag);
01140         delete portal;
01141         return true;
01142 }
01143 
01144 bool 
01145 TeSqlServerSpatial::locatePolygonSet (const string& table, TeCoord2D &pt, double tol, TePolygonSet &polygons)
01146 {
01147 
01148         
01149         bool                                    flag;
01150         TeDatabasePortal                *portal = this->getPortal();
01151 
01152         if (!portal)                    return false;
01153 
01154         std::string q;
01155         q="select geom_id, object_id, spatial_data.STAsBinary() as spatial_data from ";
01156         q+=table;
01157         q+=" where spatial_data.STContains(geometry::STGeomFromText('POINT(";
01158         q+=Te2String(pt.x());
01159         q+=" ";
01160         q+=Te2String(pt.y());
01161         q+=")',0))=1";
01162         
01163         if (!portal->query(q) || !portal->fetchRow())
01164         {       
01165                 delete portal;
01166                 return false;
01167         }
01168         
01169         do
01170         {
01171                 TePolygon pol;
01172                 flag = portal->fetchGeometry(pol);
01173                 polygons.add(pol);
01174         }while (flag);
01175         delete portal;
01176         return !polygons.empty();
01177 }
01178 
01179 
01180 bool 
01181 TeSqlServerSpatial::locateLine (const string& table, TeCoord2D &pt, TeLine2D &line, const double& tol)
01182 {
01183         TeLineSet                               ls;
01184         int                                             k;
01185         bool                                    flag;
01186         TeDatabasePortal                *portal = this->getPortal();
01187         TeCoord2D                               paux;
01188 
01189         TeBox box (pt.x()-tol,pt.y()-tol,pt.x()+tol,pt.y()+tol);
01190         string q ="SELECT geom_id, object_id, spatial_data.STAsBinary() as spatial_data FROM " + table;
01191         q+=" where ";
01192         q+=this->getSQLBoxWhere(box,TeLINES,table);
01193 
01194         if (!portal->query(q) || !portal->fetchRow())
01195         {
01196                 delete portal;
01197                 return false;
01198         }
01199 
01200         do 
01201         {
01202                 TeLine2D l;
01203                 flag = portal->fetchGeometry( l );
01204                 ls.add ( l );
01205         } while (flag);
01206         delete portal;
01207         if (TeNearest (pt, ls, k, paux, tol))
01208         {
01209                 line = ls[k];
01210                 return true;
01211         }
01212         return false;
01213 }
01214 
01215 bool 
01216 TeSqlServerSpatial::locateLineSet (const string& table, TeCoord2D &pt, TeLineSet &ls, const double& tol)
01217 {
01218         TeDatabasePortal                *portal = this->getPortal();
01219         TeBox                                   box (pt.x()-tol,pt.y()-tol,pt.x()+tol,pt.y()+tol);
01220         string                                  q ="SELECT geom_id, object_id, spatial_data.STAsBinary() as spatial_data FROM " + table;
01221 
01222         q+=" where ";
01223         q+=this->getSQLBoxWhere(box,TeLINES,table);
01224 
01225         if (!portal->query(q) || !portal->fetchRow())
01226         {
01227                 delete portal;
01228                 return false;
01229         }
01230 
01231         // Get all lines
01232         bool flag = true;
01233         do 
01234         {
01235                 TeLine2D l;
01236                 flag = portal->fetchGeometry( l );
01237                 ls.add(l);
01238         } while (flag);
01239 
01240         delete portal;
01241         return !ls.empty();
01242 }
01243 
01244 //----- TeSqlServerSpatialPortal methods ---
01245 
01246 TeSqlServerSpatialPortal::TeSqlServerSpatialPortal ( TeDatabase*  pDatabase) 
01247 {
01248         db_ = pDatabase;
01249         connection_ = ((TeSqlServerSpatial*)pDatabase)->connection_;
01250         recset_.CreateInstance(__uuidof(ADODB::Recordset));
01251 }
01252 
01253 
01254 TeSqlServerSpatialPortal::TeSqlServerSpatialPortal ()
01255 {
01256         recset_.CreateInstance(__uuidof(ADODB::Recordset));
01257         curRow_ = 0;
01258 }
01259 
01260 TeSqlServerSpatialPortal::~TeSqlServerSpatialPortal ()
01261 {
01262         freeResult();
01263 }
01264 
01265 TeTime
01266 TeSqlServerSpatialPortal::getDate (const string& name)
01267 {
01268         _variant_t value;
01269 
01270         TeTime t;
01271         try
01272         {
01273                 value = recset_->GetCollect(name.c_str());
01274         }
01275         catch(_com_error &e)
01276         {
01277                 string field = TeGetExtension(name.c_str());
01278                 if (!field.empty())
01279                 {
01280                         try
01281                         {
01282                                 value = recset_->GetCollect(field.c_str());
01283                         }
01284                         catch(_com_error &e)
01285                         {
01286                                 errorMessage_ = e.Description();
01287                                 return t;
01288                         }
01289                         catch(...)
01290                         {
01291                                 errorMessage_ = "Error getDate!";
01292                                 return t;
01293                         }
01294                 }
01295                 else
01296                 {
01297                         errorMessage_ = e.Description();
01298                         return t;
01299                 }
01300         }
01301         catch(...)
01302         {
01303                 errorMessage_ = "Oppps !";
01304                 return t;
01305         }
01306 
01307         if (value.vt != VT_NULL)
01308         {
01309                 bvalue_ = _bstr_t(value);
01310                 TeAdo* adoDb = (TeAdo*) this->getDatabase();
01311                 t = TeTime (string((char*)bvalue_), TeSECOND, adoDb->systemDateTimeFormat_, 
01312                         adoDb->systemDateSep_, adoDb->systemTimeSep_, adoDb->systemIndPM_);
01313                 return t;
01314         }
01315         else
01316                 return t;
01317 }
01318 
01319 
01320 TeTime
01321 TeSqlServerSpatialPortal::getDate (int i)
01322 {
01323     _variant_t vtIndex;
01324         _variant_t value;
01325 
01326     vtIndex.vt = VT_I4;
01327         vtIndex.lVal = i;
01328         
01329         TeTime t;
01330         try
01331         {
01332                 value = recset_->GetFields()->GetItem(vtIndex)->Value;
01333         }
01334         catch(_com_error &e)
01335         {
01336                 errorMessage_ = e.Description();
01337                 return t;
01338         }
01339         catch(...)
01340         {
01341                 errorMessage_ = "Oppps !";
01342                 return t;
01343         }
01344         if (value.vt != VT_NULL)
01345         {
01346                 bvalue_ = _bstr_t(value);
01347                 TeAdo* adoDb = (TeAdo*) this->getDatabase();
01348                 t = TeTime (string((char*)bvalue_), TeSECOND, adoDb->systemDateTimeFormat_, 
01349                         adoDb->systemDateSep_, adoDb->systemTimeSep_, adoDb->systemIndPM_);
01350                 return t;
01351         }
01352         else
01353                 return t;
01354 }
01355 
01356 
01357 string
01358 TeSqlServerSpatialPortal::getDateAsString(int i)
01359 {
01360         TeTime t = this->getDate(i);
01361         string date = t.getDateTime ();
01362 
01363         if (!date.empty())
01364         {               string tval = " TO_DATE ('"+ date +"','DDsMMsYYYYsHHsmmsSS') ";
01365                 return tval;
01366         }
01367         else
01368                 return "";
01369 }
01370 
01371 string 
01372 TeSqlServerSpatialPortal::getDateAsString(const string& s)
01373 {
01374         TeTime t = this->getDate(s);
01375         string date = t.getDateTime ();
01376 
01377         if (!date.empty())
01378         {               string tval = " TO_DATE ('"+ date +"','DDsMMsYYYYsHHsmmsSS') ";
01379                 return tval;
01380         }
01381         else
01382                 return "";
01383 }
01384 
01385 
01386 bool 
01387 TeSqlServerSpatialPortal::fetchGeometry(TePolygon& pol)
01388 {
01389         int                             geom_id;
01390         std::string             object_id;
01391         unsigned int    readBytes;      
01392         unsigned char   *wkb=0;
01393         const char              *wkb2Decoder;
01394         long                    size;
01395 
01396         try 
01397         {
01398                 this->getBlob("spatial_data",wkb,size);
01399                 if(wkb !=0)
01400                 {
01401                         wkb2Decoder=(const char*)wkb;
01402                         TeWKBGeometryDecoder::decodePolygon(wkb2Decoder,pol,readBytes);
01403                         geom_id=this->getInt("geom_id");
01404                         object_id=this->getData("object_id");
01405 
01406                         pol.geomId(geom_id);
01407                         pol.objectId(object_id);
01408                         delete wkb;
01409                 }
01410                 fetchRow(); 
01411         }
01412         catch(_com_error &e)
01413         {
01414                 errorMessage_ = e.Description();
01415                 return false;
01416         }
01417         catch(...)
01418         {
01419                 errorMessage_ = "Error fetchGeometry!";
01420                 return false;
01421         }
01422         if(recset_->IsEOF)      return false;
01423         return true;
01424 }
01425 
01426 
01427 bool 
01428 TeSqlServerSpatialPortal::fetchGeometry(TeLine2D& line)
01429 {
01430         int                             geom_id;
01431         std::string             object_id;
01432         unsigned int    readBytes;      
01433         unsigned char   *wkb=0;
01434         const char              *wkb2Decoder;
01435         long                    size;
01436 
01437         try 
01438         {
01439                 this->getBlob("spatial_data",wkb,size);
01440                 if(wkb !=0)
01441                 {
01442                         wkb2Decoder=(const char*)wkb;
01443                         TeWKBGeometryDecoder::decodeLine(wkb2Decoder,line,readBytes);
01444                         geom_id=this->getInt("geom_id");
01445                         object_id=this->getData("object_id");
01446 
01447                         line.geomId(geom_id);
01448                         line.objectId(object_id);
01449                         delete wkb;
01450                 }
01451                 fetchRow(); 
01452         }
01453         catch(_com_error &e)
01454         {
01455                 errorMessage_ = e.Description();
01456                 return false;
01457         }
01458         catch(...)
01459         {
01460                 errorMessage_ = "Error fetchGeometry!";
01461                 return false;
01462         }
01463         if(recset_->IsEOF)      return false;
01464         return true;
01465 }
01466 
01467 bool 
01468 TeSqlServerSpatialPortal::fetchGeometry(TePoint& p)
01469 {
01470         TeCoord2D               coord;
01471         unsigned int    readBytes;      
01472         unsigned char   *wkb=0;
01473         const char              *wkb2Decoder;
01474         long                    size;
01475 
01476         try {
01477                         p.geomId( atoi(getData("geom_id")));
01478                         p.objectId( string(getData("object_id")));
01479                         this->getBlob("spatial_data",wkb,size);
01480                         if(wkb !=0)
01481                         {
01482                                 wkb2Decoder=(const char*)wkb;
01483                                 TeWKBGeometryDecoder::decodePoint(wkb2Decoder,coord,readBytes);
01484                                 delete wkb;
01485                                 p.add(coord);
01486                 }
01487                 return(this->fetchRow());
01488         } 
01489         catch (_com_error &e)
01490         {
01491                 errorMessage_ = e.Description();
01492                 return false;
01493         }
01494         catch(...)
01495         {
01496                 errorMessage_ = "Error fetchGeometry!";
01497                 return false;
01498         }
01499 }
01500 
01501 bool 
01502 TeSqlServerSpatialPortal::fetchGeometry(TePoint& p, const unsigned int& initIndex)
01503 {
01504         TeCoord2D               coord;
01505         unsigned int    readBytes;      
01506         unsigned char   *wkb=0;
01507         const char              *wkb2Decoder;
01508         long                    size;
01509 
01510         try 
01511         {
01512                 p.geomId( atoi(getData(initIndex)));
01513                 p.objectId( string(getData(initIndex+1)));
01514                 this->getBlob("spatial_data",wkb,size);
01515                 if(wkb !=0)
01516                         {
01517                                 wkb2Decoder=(const char*)wkb;
01518                                 TeWKBGeometryDecoder::decodePoint(wkb2Decoder,coord,readBytes);
01519                                 delete wkb;
01520                                 p.add(coord);
01521                         }
01522                 return(this->fetchRow());
01523         } 
01524         catch (_com_error &e)
01525         {
01526                 errorMessage_ = e.Description();
01527                 return false;
01528         }
01529         catch(...)
01530         {
01531                 errorMessage_ = "Error fetchGeometry!";
01532                 return false;
01533         }
01534 }
01535 
01536 
01537 int getLastGeomId ( TeSqlServerSpatial* db, const string& table )
01538 {
01539         TeSqlServerSpatialPortal *sqlp = NULL;
01540         try
01541         {
01542                 int lastGeomId = 0;
01543 
01544                 if (db == NULL)
01545                         return lastGeomId;
01546                 //gets the last geom id
01547         
01548                 sqlp = (TeSqlServerSpatialPortal*)db->getPortal();
01549                 if(!sqlp)
01550                         return false;
01551                 sqlp->freeResult();
01552                 std::string sql= "SELECT MAX(geom_id) AS MaxGeomId FROM " + table;
01553                 
01554                 if(!sqlp->query(sql))
01555                 {
01556                         delete sqlp;
01557                         return false;
01558                 }               
01559                 
01560                 if( sqlp->fetchRow() )
01561                 {
01562                         lastGeomId = atoi( sqlp->getData("MaxGeomId") ); //the string must be empty
01563                 }
01564                 
01565                 delete sqlp;
01566                 sqlp = NULL;
01567                 
01568                 return lastGeomId;
01569         }
01570         catch(...)
01571         {
01572                 if (sqlp)
01573                 {
01574                         delete sqlp;
01575                         sqlp = NULL;
01576                 }
01577                 return 0;
01578         }
01579 }
01580 
01581 
01582