1
/******************************************************************************
2
* $Id: ogrmssqlspatialdatasource.cpp 23571 2011-12-14 11:06:58Z tamas $
4
* Project: MSSQL Spatial driver
5
* Purpose: Implements OGRMSSQLSpatialDataSource class..
6
* Author: Tamas Szekeres, szekerest at gmail.com
8
******************************************************************************
9
* Copyright (c) 2010, Tamas Szekeres
11
* Permission is hereby granted, free of charge, to any person obtaining a
12
* copy of this software and associated documentation files (the "Software"),
13
* to deal in the Software without restriction, including without limitation
14
* the rights to use, copy, modify, merge, publish, distribute, sublicense,
15
* and/or sell copies of the Software, and to permit persons to whom the
16
* Software is furnished to do so, subject to the following conditions:
18
* The above copyright notice and this permission notice shall be included
19
* in all copies or substantial portions of the Software.
21
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
22
* OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
23
* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL
24
* THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
25
* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
26
* FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER
27
* DEALINGS IN THE SOFTWARE.
28
****************************************************************************/
30
#include "ogr_mssqlspatial.h"
32
CPL_CVSID("$Id: ogrmssqlspatialdatasource.cpp 23571 2011-12-14 11:06:58Z tamas $");
34
/************************************************************************/
35
/* OGRMSSQLSpatialDataSource() */
36
/************************************************************************/
38
OGRMSSQLSpatialDataSource::OGRMSSQLSpatialDataSource()
50
nGeometryFormat = MSSQLGEOMETRY_NATIVE;
53
/************************************************************************/
54
/* ~OGRMSSQLSpatialDataSource() */
55
/************************************************************************/
57
OGRMSSQLSpatialDataSource::~OGRMSSQLSpatialDataSource()
63
CPLFree( pszCatalog );
65
for( i = 0; i < nLayers; i++ )
68
CPLFree( papoLayers );
70
for( i = 0; i < nKnownSRID; i++ )
72
if( papoSRS[i] != NULL )
73
papoSRS[i]->Release();
79
/************************************************************************/
80
/* TestCapability() */
81
/************************************************************************/
83
int OGRMSSQLSpatialDataSource::TestCapability( const char * pszCap )
86
if( EQUAL(pszCap,ODsCCreateLayer) )
92
/************************************************************************/
94
/************************************************************************/
96
OGRLayer *OGRMSSQLSpatialDataSource::GetLayer( int iLayer )
99
if( iLayer < 0 || iLayer >= nLayers )
102
return papoLayers[iLayer];
105
/************************************************************************/
107
/************************************************************************/
109
int OGRMSSQLSpatialDataSource::DeleteLayer( int iLayer )
112
if( iLayer < 0 || iLayer >= nLayers )
113
return OGRERR_FAILURE;
115
/* -------------------------------------------------------------------- */
116
/* Blow away our OGR structures related to the layer. This is */
117
/* pretty dangerous if anything has a reference to this layer! */
118
/* -------------------------------------------------------------------- */
119
const char* pszLayerName = papoLayers[iLayer]->GetTableName();
120
const char* pszSchemaName = papoLayers[iLayer]->GetSchemaName();
122
CPLODBCStatement oStmt( &oSession );
123
oStmt.Appendf( "DELETE FROM geometry_columns WHERE f_table_schema = '%s' AND f_table_name = '%s'\n",
124
pszSchemaName, pszLayerName );
125
oStmt.Appendf("DROP TABLE [%s].[%s]", pszSchemaName, pszLayerName );
127
CPLDebug( "MSSQLSpatial", "DeleteLayer(%s)", pszLayerName );
129
delete papoLayers[iLayer];
130
memmove( papoLayers + iLayer, papoLayers + iLayer + 1,
131
sizeof(void *) * (nLayers - iLayer - 1) );
134
if ( strlen(pszLayerName) == 0 )
137
/* -------------------------------------------------------------------- */
138
/* Remove from the database. */
139
/* -------------------------------------------------------------------- */
141
oSession.BeginTransaction();
143
if( !oStmt.ExecuteSQL() )
145
CPLError( CE_Failure, CPLE_AppDefined,
146
"Error deleting layer: %s", GetSession()->GetLastError() );
148
return OGRERR_FAILURE;
151
oSession.CommitTransaction();
156
/************************************************************************/
158
/************************************************************************/
160
OGRLayer * OGRMSSQLSpatialDataSource::CreateLayer( const char * pszLayerName,
161
OGRSpatialReference *poSRS,
162
OGRwkbGeometryType eType,
163
char ** papszOptions )
166
char *pszTableName = NULL;
167
char *pszSchemaName = NULL;
168
const char *pszGeomType = NULL;
169
const char *pszGeomColumn = NULL;
170
int nCoordDimension = 3;
172
/* determine the dimension */
173
if( eType == wkbFlatten(eType) )
176
if( CSLFetchNameValue( papszOptions, "DIM") != NULL )
177
nCoordDimension = atoi(CSLFetchNameValue( papszOptions, "DIM"));
179
/* MSSQL Schema handling:
180
Extract schema name from input layer name or passed with -lco SCHEMA.
181
Set layer name to "schema.table" or to "table" if schema is not
184
const char* pszDotPos = strstr(pszLayerName,".");
185
if ( pszDotPos != NULL )
187
int length = pszDotPos - pszLayerName;
188
pszSchemaName = (char*)CPLMalloc(length+1);
189
strncpy(pszSchemaName, pszLayerName, length);
190
pszSchemaName[length] = '\0';
192
if( CSLFetchBoolean(papszOptions,"LAUNDER", TRUE) )
193
pszTableName = LaunderName( pszDotPos + 1 ); //skip "."
195
pszTableName = CPLStrdup( pszDotPos + 1 ); //skip "."
199
pszSchemaName = NULL;
200
if( CSLFetchBoolean(papszOptions,"LAUNDER", TRUE) )
201
pszTableName = LaunderName( pszLayerName ); //skip "."
203
pszTableName = CPLStrdup( pszLayerName ); //skip "."
206
if( CSLFetchNameValue( papszOptions, "SCHEMA" ) != NULL )
208
CPLFree(pszSchemaName);
209
pszSchemaName = CPLStrdup(CSLFetchNameValue( papszOptions, "SCHEMA" ));
212
if (pszSchemaName == NULL)
213
pszSchemaName = CPLStrdup("dbo");
215
/* -------------------------------------------------------------------- */
216
/* Do we already have this layer? If so, should we blow it */
218
/* -------------------------------------------------------------------- */
221
for( iLayer = 0; iLayer < nLayers; iLayer++ )
223
if( EQUAL(pszLayerName,papoLayers[iLayer]->GetTableName()) )
225
if( CSLFetchNameValue( papszOptions, "OVERWRITE" ) != NULL
226
&& !EQUAL(CSLFetchNameValue(papszOptions,"OVERWRITE"),"NO") )
229
pszSchemaName = CPLStrdup(papoLayers[iLayer]->GetSchemaName());
231
DeleteLayer( iLayer );
235
CPLError( CE_Failure, CPLE_AppDefined,
236
"Layer %s already exists, CreateLayer failed.\n"
237
"Use the layer creation option OVERWRITE=YES to "
241
CPLFree( pszSchemaName );
242
CPLFree( pszTableName );
248
/* -------------------------------------------------------------------- */
249
/* Handle the GEOM_TYPE option. */
250
/* -------------------------------------------------------------------- */
251
pszGeomType = CSLFetchNameValue( papszOptions, "GEOM_TYPE" );
254
pszGeomType = "geometry";
256
if( !EQUAL(pszGeomType, "geometry")
257
&& !EQUAL(pszGeomType, "geography"))
259
CPLError( CE_Failure, CPLE_AppDefined,
260
"FORMAT=%s not recognised or supported.",
263
CPLFree( pszSchemaName );
264
CPLFree( pszTableName );
268
/* determine the geometry column name */
269
pszGeomColumn = CSLFetchNameValue( papszOptions, "GEOM_NAME");
271
pszGeomColumn = "ogr_geometry";
273
/* -------------------------------------------------------------------- */
274
/* Initialize the metadata tables */
275
/* -------------------------------------------------------------------- */
277
if (InitializeMetadataTables() != OGRERR_NONE)
279
CPLFree( pszSchemaName );
280
CPLFree( pszTableName );
284
/* -------------------------------------------------------------------- */
285
/* Try to get the SRS Id of this spatial reference system, */
286
/* adding to the srs table if needed. */
287
/* -------------------------------------------------------------------- */
290
if( CSLFetchNameValue( papszOptions, "SRID") != NULL )
291
nSRSId = atoi(CSLFetchNameValue( papszOptions, "SRID"));
293
if( nSRSId == 0 && poSRS != NULL )
294
nSRSId = FetchSRSId( poSRS );
296
/* -------------------------------------------------------------------- */
297
/* Create a new table and create a new entry in the geometry, */
298
/* geometry_columns metadata table. */
299
/* -------------------------------------------------------------------- */
301
if( eType != wkbNone )
303
const char *pszGeometryType = OGRToOGCGeomType(eType);
305
CPLODBCStatement oStmt( &oSession );
307
oStmt.Appendf( "DELETE FROM geometry_columns WHERE f_table_schema = '%s' "
308
"AND f_table_name = '%s'\n", pszSchemaName, pszTableName );
310
oStmt.Appendf("INSERT INTO [geometry_columns] ([f_table_catalog], [f_table_schema] ,[f_table_name], "
311
"[f_geometry_column],[coord_dimension],[srid],[geometry_type]) VALUES ('%s', '%s', '%s', '%s', %d, %d, '%s')\n",
312
pszCatalog, pszSchemaName, pszTableName, pszGeomColumn, nCoordDimension, nSRSId, pszGeometryType );
314
oStmt.Appendf("CREATE TABLE [%s].[%s] ([ogr_fid] [int] IDENTITY(1,1) NOT NULL, "
315
"[%s] [%s] NULL, CONSTRAINT [PK_%s] PRIMARY KEY CLUSTERED ([ogr_fid] ASC))",
316
pszSchemaName, pszTableName, pszGeomColumn, pszGeomType, pszTableName);
318
oSession.BeginTransaction();
320
if( !oStmt.ExecuteSQL() )
322
CPLError( CE_Failure, CPLE_AppDefined,
323
"Error creating layer: %s", GetSession()->GetLastError() );
328
oSession.CommitTransaction();
331
CPLFree( pszSchemaName );
332
CPLFree( pszTableName );
334
/* -------------------------------------------------------------------- */
335
/* Create the layer object. */
336
/* -------------------------------------------------------------------- */
337
OGRMSSQLSpatialTableLayer *poLayer;
339
poLayer = new OGRMSSQLSpatialTableLayer( this );
341
poLayer->SetLaunderFlag( CSLFetchBoolean(papszOptions,"LAUNDER",TRUE) );
342
poLayer->SetPrecisionFlag( CSLFetchBoolean(papszOptions,"PRECISION",TRUE));
344
if (poLayer->Initialize("dbo", pszLayerName, pszGeomColumn, nCoordDimension, nSRSId, eType) == OGRERR_FAILURE)
349
/* -------------------------------------------------------------------- */
350
/* Add layer to data source layer list. */
351
/* -------------------------------------------------------------------- */
352
papoLayers = (OGRMSSQLSpatialTableLayer **)
353
CPLRealloc( papoLayers, sizeof(OGRMSSQLSpatialTableLayer *) * (nLayers+1) );
355
papoLayers[nLayers++] = poLayer;
361
/************************************************************************/
363
/************************************************************************/
365
int OGRMSSQLSpatialDataSource::OpenTable( const char *pszSchemaName, const char *pszTableName,
366
const char *pszGeomCol, int nCoordDimension,
367
int nSRID, OGRwkbGeometryType eType, int bUpdate )
370
/* -------------------------------------------------------------------- */
371
/* Create the layer object. */
372
/* -------------------------------------------------------------------- */
373
OGRMSSQLSpatialTableLayer *poLayer = new OGRMSSQLSpatialTableLayer( this );
375
if( poLayer->Initialize( pszSchemaName, pszTableName, pszGeomCol, nCoordDimension, nSRID, eType ) )
381
/* -------------------------------------------------------------------- */
382
/* Add layer to data source layer list. */
383
/* -------------------------------------------------------------------- */
384
papoLayers = (OGRMSSQLSpatialTableLayer **)
385
CPLRealloc( papoLayers, sizeof(OGRMSSQLSpatialTableLayer *) * (nLayers+1) );
386
papoLayers[nLayers++] = poLayer;
392
/************************************************************************/
393
/* GetLayerCount() */
394
/************************************************************************/
396
int OGRMSSQLSpatialDataSource::GetLayerCount()
401
/************************************************************************/
403
/************************************************************************/
405
int OGRMSSQLSpatialDataSource::ParseValue(char** pszValue, char* pszSource, const char* pszKey, int nStart, int nNext, int nTerm, int bRemove)
407
int nLen = strlen(pszKey);
408
if ((*pszValue) == NULL && nStart + nLen < nNext &&
409
EQUALN(pszSource + nStart, pszKey, nLen))
411
*pszValue = (char*)CPLMalloc( sizeof(char) * (nNext - nStart - nLen + 1) );
413
strncpy(*pszValue, pszSource + nStart + nLen, nNext - nStart - nLen);
414
(*pszValue)[nNext - nStart - nLen] = 0;
418
// remove the value from the source string
419
if (pszSource[nNext] == ';')
420
memmove( pszSource + nStart, pszSource + nNext + 1, nTerm - nNext);
422
memmove( pszSource + nStart, pszSource + nNext, nTerm - nNext + 1);
430
/************************************************************************/
432
/************************************************************************/
434
int OGRMSSQLSpatialDataSource::Open( const char * pszNewName, int bUpdate,
438
CPLAssert( nLayers == 0 );
440
if( !EQUALN(pszNewName,"MSSQL:",6) )
443
CPLError( CE_Failure, CPLE_AppDefined,
444
"%s does not conform to MSSSQLSpatial naming convention,"
445
" MSSQL:*\n", pszNewName );
449
/* Determine if the connection string contains specific values */
450
char* pszTableSpec = NULL;
451
char* pszGeometryFormat = NULL;
452
char* pszConnectionName = CPLStrdup(pszNewName + 6);
453
char* pszDriver = NULL;
454
int nCurrent, nNext, nTerm;
455
nCurrent = nNext = nTerm = strlen(pszConnectionName);
460
if (pszConnectionName[nCurrent] == ';')
466
if (ParseValue(&pszCatalog, pszConnectionName, "database=",
467
nCurrent, nNext, nTerm, FALSE))
470
if (ParseValue(&pszTableSpec, pszConnectionName, "tables=",
471
nCurrent, nNext, nTerm, TRUE))
474
if (ParseValue(&pszDriver, pszConnectionName, "driver=",
475
nCurrent, nNext, nTerm, FALSE))
478
if (ParseValue(&pszGeometryFormat, pszConnectionName,
479
"geometryformat=", nCurrent, nNext, nTerm, TRUE))
481
if (EQUALN(pszGeometryFormat, "wkb",3))
482
nGeometryFormat = MSSQLGEOMETRY_WKB;
483
else if (EQUALN(pszGeometryFormat,"wkt",3))
484
nGeometryFormat = MSSQLGEOMETRY_WKT;
485
else if (EQUALN(pszGeometryFormat,"native",3))
486
nGeometryFormat = MSSQLGEOMETRY_NATIVE;
489
CPLError( CE_Failure, CPLE_AppDefined,
490
"Invalid geometry type specified: %s,"
491
" MSSQL:*\n", pszGeometryFormat );
493
CPLFree(pszTableSpec);
494
CPLFree(pszGeometryFormat);
495
CPLFree(pszConnectionName);
500
CPLFree(pszGeometryFormat);
501
pszGeometryFormat = NULL;
506
/* Determine if the connection string contains the catalog portion */
507
if( pszCatalog == NULL )
509
CPLError( CE_Failure, CPLE_AppDefined,
510
"'%s' does not contain the 'database' portion\n", pszNewName );
512
CPLFree(pszTableSpec);
513
CPLFree(pszGeometryFormat);
514
CPLFree(pszConnectionName);
519
pszName = CPLStrdup(pszNewName);
521
char **papszTableNames=NULL;
522
char **papszSchemaNames=NULL;
523
char **papszGeomColumnNames=NULL;
524
char **papszCoordDimensions=NULL;
525
char **papszSRIds=NULL;
527
/* Determine if the connection string contains the TABLES portion */
528
if( pszTableSpec != NULL )
530
char **papszTableList;
533
papszTableList = CSLTokenizeString2( pszTableSpec, ",", 0 );
535
for( i = 0; i < CSLCount(papszTableList); i++ )
537
char **papszQualifiedParts;
539
// Get schema and table name
540
papszQualifiedParts = CSLTokenizeString2( papszTableList[i],
543
/* Find the geometry column name if specified */
544
if( CSLCount( papszQualifiedParts ) >= 1 )
546
char* pszGeomColumnName = NULL;
547
char* pos = strchr(papszQualifiedParts[CSLCount( papszQualifiedParts ) - 1], '(');
551
pszGeomColumnName = pos+1;
552
int len = strlen(pszGeomColumnName);
554
pszGeomColumnName[len - 1] = '\0';
556
papszGeomColumnNames = CSLAddString( papszGeomColumnNames,
557
pszGeomColumnName ? pszGeomColumnName : "");
560
if( CSLCount( papszQualifiedParts ) == 2 )
562
papszSchemaNames = CSLAddString( papszSchemaNames,
563
papszQualifiedParts[0] );
564
papszTableNames = CSLAddString( papszTableNames,
565
papszQualifiedParts[1] );
567
else if( CSLCount( papszQualifiedParts ) == 1 )
569
papszSchemaNames = CSLAddString( papszSchemaNames, "dbo");
570
papszTableNames = CSLAddString( papszTableNames,
571
papszQualifiedParts[0] );
574
CSLDestroy(papszQualifiedParts);
577
CSLDestroy(papszTableList);
580
CPLFree(pszTableSpec);
582
/* Initialize the SQL Server connection. */
584
if ( pszDriver != NULL )
586
/* driver has been specified */
587
CPLDebug( "OGR_MSSQLSpatial", "EstablishSession(Connection:\"%s\")", pszConnectionName);
588
nResult = oSession.EstablishSession( pszConnectionName, "", "" );
592
/* no driver has been specified, defautls to SQL Server */
593
CPLDebug( "OGR_MSSQLSpatial", "EstablishSession(Connection:\"%s\")", pszConnectionName);
594
nResult = oSession.EstablishSession( CPLSPrintf("DRIVER=SQL Server;%s", pszConnectionName), "", "" );
601
CPLError( CE_Failure, CPLE_AppDefined,
602
"Unable to initialize connection to the server for %s,\n"
603
"%s", pszNewName, oSession.GetLastError() );
605
CSLDestroy( papszTableNames );
606
CSLDestroy( papszSchemaNames );
607
CSLDestroy( papszGeomColumnNames );
608
CSLDestroy( papszCoordDimensions );
609
CSLDestroy( papszSRIds );
610
CPLFree(pszGeometryFormat);
611
CPLFree(pszConnectionName);
615
char** papszTypes = NULL;
617
/* Determine the available tables if not specified. */
618
if (papszTableNames == NULL)
620
CPLODBCStatement oStmt( &oSession );
622
oStmt.Append( "SELECT f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, geometry_type FROM dbo.geometry_columns");
624
if( oStmt.ExecuteSQL() )
626
while( oStmt.Fetch() )
629
CSLAddString( papszSchemaNames, oStmt.GetColData(0) );
631
CSLAddString( papszTableNames, oStmt.GetColData(1) );
632
papszGeomColumnNames =
633
CSLAddString( papszGeomColumnNames, oStmt.GetColData(2) );
634
papszCoordDimensions =
635
CSLAddString( papszCoordDimensions, oStmt.GetColData(3) );
637
CSLAddString( papszSRIds, oStmt.GetColData(4) );
639
CSLAddString( papszTypes, oStmt.GetColData(5) );
644
int nSRId, nCoordDimension;
645
OGRwkbGeometryType eType;
648
papszTableNames != NULL && papszTableNames[iTable] != NULL;
651
if (papszSRIds != NULL)
652
nSRId = atoi(papszSRIds[iTable]);
656
if (papszCoordDimensions != NULL)
657
nCoordDimension = atoi(papszCoordDimensions[iTable]);
661
if (papszTypes != NULL)
662
eType = OGRFromOGCGeomType(papszTypes[iTable]);
666
if( strlen(papszGeomColumnNames[iTable]) > 0 )
667
OpenTable( papszSchemaNames[iTable], papszTableNames[iTable], papszGeomColumnNames[iTable],
668
nCoordDimension, nSRId, eType, bUpdate );
670
OpenTable( papszSchemaNames[iTable], papszTableNames[iTable], NULL,
671
nCoordDimension, nSRId, eType, bUpdate );
674
CSLDestroy( papszTableNames );
675
CSLDestroy( papszSchemaNames );
676
CSLDestroy( papszGeomColumnNames );
677
CSLDestroy( papszCoordDimensions );
678
CSLDestroy( papszSRIds );
679
CSLDestroy( papszTypes );
681
CPLFree(pszGeometryFormat);
682
CPLFree(pszConnectionName);
689
/************************************************************************/
691
/************************************************************************/
693
OGRLayer * OGRMSSQLSpatialDataSource::ExecuteSQL( const char *pszSQLCommand,
694
OGRGeometry *poSpatialFilter,
695
const char *pszDialect )
698
/* -------------------------------------------------------------------- */
699
/* Use generic imlplementation for OGRSQL dialect. */
700
/* -------------------------------------------------------------------- */
701
if( pszDialect != NULL && EQUAL(pszDialect,"OGRSQL") )
702
return OGRDataSource::ExecuteSQL( pszSQLCommand,
706
/* -------------------------------------------------------------------- */
707
/* Special case DELLAYER: command. */
708
/* -------------------------------------------------------------------- */
709
if( EQUALN(pszSQLCommand,"DELLAYER:",9) )
711
const char *pszLayerName = pszSQLCommand + 9;
713
while( *pszLayerName == ' ' )
716
for( int iLayer = 0; iLayer < nLayers; iLayer++ )
718
if( EQUAL(papoLayers[iLayer]->GetName(),
721
DeleteLayer( iLayer );
728
CPLDebug( "MSSQLSpatial", "ExecuteSQL(%s) called.", pszSQLCommand );
730
if( EQUALN(pszSQLCommand, "DROP SPATIAL INDEX ON ", 22) )
732
/* Handle command to drop a spatial index. */
733
OGRMSSQLSpatialTableLayer *poLayer = new OGRMSSQLSpatialTableLayer( this );
737
if( poLayer->Initialize( "dbo", pszSQLCommand + 22, NULL, 0, 0, wkbUnknown ) != CE_None )
739
CPLError( CE_Failure, CPLE_AppDefined,
740
"Failed to initialize layer '%s'", pszSQLCommand + 22 );
742
poLayer->DropSpatialIndex();
747
else if( EQUALN(pszSQLCommand, "CREATE SPATIAL INDEX ON ", 24) )
749
/* Handle command to create a spatial index. */
750
OGRMSSQLSpatialTableLayer *poLayer = new OGRMSSQLSpatialTableLayer( this );
754
if( poLayer->Initialize( "dbo", pszSQLCommand + 24, NULL, 0, 0, wkbUnknown ) != CE_None )
756
CPLError( CE_Failure, CPLE_AppDefined,
757
"Failed to initialize layer '%s'", pszSQLCommand + 24 );
759
poLayer->CreateSpatialIndex();
765
/* Execute the command natively */
766
CPLODBCStatement *poStmt = new CPLODBCStatement( &oSession );
767
poStmt->Append( pszSQLCommand );
769
if( !poStmt->ExecuteSQL() )
771
CPLError( CE_Failure, CPLE_AppDefined,
772
"%s", oSession.GetLastError() );
777
/* -------------------------------------------------------------------- */
778
/* Are there result columns for this statement? */
779
/* -------------------------------------------------------------------- */
780
if( poStmt->GetColCount() == 0 )
787
/* -------------------------------------------------------------------- */
788
/* Create a results layer. It will take ownership of the */
790
/* -------------------------------------------------------------------- */
792
OGRMSSQLSpatialSelectLayer *poLayer = NULL;
794
poLayer = new OGRMSSQLSpatialSelectLayer( this, poStmt );
796
if( poSpatialFilter != NULL )
797
poLayer->SetSpatialFilter( poSpatialFilter );
802
/************************************************************************/
803
/* ReleaseResultSet() */
804
/************************************************************************/
806
void OGRMSSQLSpatialDataSource::ReleaseResultSet( OGRLayer * poLayer )
812
/************************************************************************/
814
/************************************************************************/
816
char *OGRMSSQLSpatialDataSource::LaunderName( const char *pszSrcName )
819
char *pszSafeName = CPLStrdup( pszSrcName );
822
for( i = 0; pszSafeName[i] != '\0'; i++ )
824
pszSafeName[i] = (char) tolower( pszSafeName[i] );
825
if( pszSafeName[i] == '-' || pszSafeName[i] == '#' )
826
pszSafeName[i] = '_';
832
/************************************************************************/
833
/* InitializeMetadataTables() */
835
/* Create the metadata tables (SPATIAL_REF_SYS and */
836
/* GEOMETRY_COLUMNS). */
837
/************************************************************************/
839
OGRErr OGRMSSQLSpatialDataSource::InitializeMetadataTables()
842
CPLODBCStatement oStmt( &oSession );
844
oStmt.Append( "IF NOT EXISTS (SELECT * FROM sys.objects WHERE "
845
"object_id = OBJECT_ID(N'[dbo].[geometry_columns]') AND type in (N'U')) "
846
"CREATE TABLE geometry_columns (f_table_catalog varchar(128) not null, "
847
"f_table_schema varchar(128) not null, f_table_name varchar(256) not null, "
848
"f_geometry_column varchar(256) not null, coord_dimension integer not null, "
849
"srid integer not null, geometry_type varchar(30) not null, "
850
"CONSTRAINT geometry_columns_pk PRIMARY KEY (f_table_catalog, "
851
"f_table_schema, f_table_name, f_geometry_column));\n" );
853
oStmt.Append( "IF NOT EXISTS (SELECT * FROM sys.objects "
854
"WHERE object_id = OBJECT_ID(N'[dbo].[spatial_ref_sys]') AND type in (N'U')) "
855
"CREATE TABLE spatial_ref_sys (srid integer not null "
856
"PRIMARY KEY, auth_name varchar(256), auth_srid integer, srtext varchar(2048), proj4text varchar(2048))" );
858
oSession.BeginTransaction();
860
if( !oStmt.ExecuteSQL() )
862
CPLError( CE_Failure, CPLE_AppDefined,
863
"Error initializing the metadata tables : %s", GetSession()->GetLastError() );
864
return OGRERR_FAILURE;
867
oSession.CommitTransaction();
873
/************************************************************************/
876
/* Return a SRS corresponding to a particular id. Note that */
877
/* reference counting should be honoured on the returned */
878
/* OGRSpatialReference, as handles may be cached. */
879
/************************************************************************/
881
OGRSpatialReference *OGRMSSQLSpatialDataSource::FetchSRS( int nId )
887
/* -------------------------------------------------------------------- */
888
/* First, we look through our SRID cache, is it there? */
889
/* -------------------------------------------------------------------- */
892
for( i = 0; i < nKnownSRID; i++ )
894
if( panSRID[i] == nId )
898
/* -------------------------------------------------------------------- */
899
/* Try looking up in spatial_ref_sys table */
900
/* -------------------------------------------------------------------- */
901
OGRSpatialReference *poSRS = NULL;
903
CPLODBCStatement oStmt( GetSession() );
904
oStmt.Appendf( "SELECT srtext FROM spatial_ref_sys WHERE srid = %d", nId );
906
if( !oStmt.ExecuteSQL() || !oStmt.Fetch() )
911
if ( oStmt.GetColData( 0 ) )
913
poSRS = new OGRSpatialReference();
914
char* pszWKT = (char*)oStmt.GetColData( 0 );
915
if( poSRS->importFromWkt( &pszWKT ) != OGRERR_NONE )
921
/* -------------------------------------------------------------------- */
922
/* Add to the cache. */
923
/* -------------------------------------------------------------------- */
924
panSRID = (int *) CPLRealloc(panSRID,sizeof(int) * (nKnownSRID+1) );
925
papoSRS = (OGRSpatialReference **)
926
CPLRealloc(papoSRS, sizeof(void*) * (nKnownSRID + 1) );
927
panSRID[nKnownSRID] = nId;
928
papoSRS[nKnownSRID] = poSRS;
935
/************************************************************************/
938
/* Fetch the id corresponding to an SRS, and if not found, add */
939
/* it to the table. */
940
/************************************************************************/
942
int OGRMSSQLSpatialDataSource::FetchSRSId( OGRSpatialReference * poSRS)
947
const char* pszAuthorityName;
952
OGRSpatialReference oSRS(*poSRS);
955
pszAuthorityName = oSRS.GetAuthorityName(NULL);
957
if( pszAuthorityName == NULL || strlen(pszAuthorityName) == 0 )
959
/* -------------------------------------------------------------------- */
960
/* Try to identify an EPSG code */
961
/* -------------------------------------------------------------------- */
962
oSRS.AutoIdentifyEPSG();
964
pszAuthorityName = oSRS.GetAuthorityName(NULL);
965
if (pszAuthorityName != NULL && EQUAL(pszAuthorityName, "EPSG"))
967
const char* pszAuthorityCode = oSRS.GetAuthorityCode(NULL);
968
if ( pszAuthorityCode != NULL && strlen(pszAuthorityCode) > 0 )
970
/* Import 'clean' SRS */
971
oSRS.importFromEPSG( atoi(pszAuthorityCode) );
973
pszAuthorityName = oSRS.GetAuthorityName(NULL);
977
/* -------------------------------------------------------------------- */
978
/* Check whether the EPSG authority code is already mapped to a */
980
/* -------------------------------------------------------------------- */
981
int nAuthorityCode = 0;
982
if( pszAuthorityName != NULL && EQUAL( pszAuthorityName, "EPSG" ) )
984
/* For the root authority name 'EPSG', the authority code
985
* should always be integral
987
nAuthorityCode = atoi( oSRS.GetAuthorityCode(NULL) );
989
CPLODBCStatement oStmt( &oSession );
990
oStmt.Appendf("SELECT srid FROM spatial_ref_sys WHERE "
991
"auth_name = '%s' AND auth_srid = %d",
995
if( oStmt.ExecuteSQL() && oStmt.Fetch() && oStmt.GetColData( 0 ) )
997
nSRSId = atoi(oStmt.GetColData( 0 ));
1002
/* -------------------------------------------------------------------- */
1003
/* Translate SRS to WKT. */
1004
/* -------------------------------------------------------------------- */
1005
if( oSRS.exportToWkt( &pszWKT ) != OGRERR_NONE )
1011
/* -------------------------------------------------------------------- */
1012
/* Try to find in the existing table. */
1013
/* -------------------------------------------------------------------- */
1014
CPLODBCStatement oStmt( &oSession );
1016
oStmt.Append( "SELECT srid FROM spatial_ref_sys WHERE srtext = ");
1017
OGRMSSQLAppendEscaped(&oStmt, pszWKT);
1019
/* -------------------------------------------------------------------- */
1020
/* We got it! Return it. */
1021
/* -------------------------------------------------------------------- */
1022
if( oStmt.ExecuteSQL() )
1024
if ( oStmt.Fetch() && oStmt.GetColData( 0 ) )
1026
nSRSId = atoi(oStmt.GetColData( 0 ));
1033
/* probably the table is missing at all */
1034
if( InitializeMetadataTables() != OGRERR_NONE )
1041
/* -------------------------------------------------------------------- */
1042
/* Try adding the SRS to the SRS table. */
1043
/* -------------------------------------------------------------------- */
1044
char *pszProj4 = NULL;
1045
if( oSRS.exportToProj4( &pszProj4 ) != OGRERR_NONE )
1047
CPLFree( pszProj4 );
1052
/* -------------------------------------------------------------------- */
1053
/* Check whether the auth_code can be used as srid. */
1054
/* -------------------------------------------------------------------- */
1055
nSRSId = nAuthorityCode;
1058
oSession.BeginTransaction();
1059
if (nAuthorityCode > 0)
1061
oStmt.Appendf("SELECT srid FROM spatial_ref_sys where srid = %d", nAuthorityCode);
1062
if ( oStmt.ExecuteSQL() && oStmt.Fetch())
1068
/* -------------------------------------------------------------------- */
1069
/* Get the current maximum srid in the srs table. */
1070
/* -------------------------------------------------------------------- */
1075
oStmt.Append("SELECT COALESCE(MAX(srid) + 1, 32768) FROM spatial_ref_sys where srid between 32768 and 65536");
1077
if ( oStmt.ExecuteSQL() && oStmt.Fetch() && oStmt.GetColData( 0 ) )
1079
nSRSId = atoi(oStmt.GetColData( 0 ));
1085
/* unable to allocate srid */
1086
oSession.RollbackTransaction();
1087
CPLFree( pszProj4 );
1093
if( nAuthorityCode > 0 )
1096
"INSERT INTO spatial_ref_sys (srid, auth_srid, auth_name, srtext, proj4text) "
1097
"VALUES (%d, %d, ", nSRSId, nAuthorityCode );
1098
OGRMSSQLAppendEscaped(&oStmt, pszAuthorityName);
1100
OGRMSSQLAppendEscaped(&oStmt, pszWKT);
1102
OGRMSSQLAppendEscaped(&oStmt, pszProj4);
1108
"INSERT INTO spatial_ref_sys (srid,srtext,proj4text) VALUES (%d, ", nSRSId);
1109
OGRMSSQLAppendEscaped(&oStmt, pszWKT);
1111
OGRMSSQLAppendEscaped(&oStmt, pszProj4);
1115
/* Free everything that was allocated. */
1116
CPLFree( pszProj4 );
1119
if ( oStmt.ExecuteSQL() )
1120
oSession.CommitTransaction();
1122
oSession.RollbackTransaction();