1 /***************************************************************************
2 qgspostgresrasterprovider.cpp - QgsPostgresRasterProvider
3
4 ---------------------
5 begin : 20.12.2019
6 copyright : (C) 2019 by Alessandro Pasotti
7 email : elpaso at itopen dot it
8 ***************************************************************************
9 * *
10 * This program is free software; you can redistribute it and/or modify *
11 * it under the terms of the GNU General Public License as published by *
12 * the Free Software Foundation; either version 2 of the License, or *
13 * (at your option) any later version. *
14 * *
15 ***************************************************************************/
16
17 #include <cstring>
18 #include "qgspostgresrasterprovider.h"
19 #include "qgspostgrestransaction.h"
20 #include "qgsmessagelog.h"
21 #include "qgsrectangle.h"
22 #include "qgspolygon.h"
23 #include "qgspostgresprovider.h"
24 #include "qgsgdalutils.h"
25 #include "qgsstringutils.h"
26
27 #include <QRegularExpression>
28
29 const QString QgsPostgresRasterProvider::PG_RASTER_PROVIDER_KEY = QStringLiteral( "postgresraster" );
30 const QString QgsPostgresRasterProvider::PG_RASTER_PROVIDER_DESCRIPTION = QStringLiteral( "Postgres raster provider" );
31
32
QgsPostgresRasterProvider(const QString & uri,const QgsDataProvider::ProviderOptions & providerOptions,QgsDataProvider::ReadFlags flags)33 QgsPostgresRasterProvider::QgsPostgresRasterProvider( const QString &uri, const QgsDataProvider::ProviderOptions &providerOptions, QgsDataProvider::ReadFlags flags )
34 : QgsRasterDataProvider( uri, providerOptions, flags )
35 , mShared( new QgsPostgresRasterSharedData )
36 {
37
38 mUri = uri;
39
40 // populate members from the uri structure
41 mSchemaName = mUri.schema();
42 if ( mSchemaName.isEmpty() )
43 {
44 mSchemaName = QStringLiteral( "public" );
45 }
46 mTableName = mUri.table();
47
48 mRasterColumn = mUri.geometryColumn();
49 mSqlWhereClause = mUri.sql();
50 mRequestedSrid = mUri.srid();
51
52 /* do not support queries for now
53 if ( mSchemaName.isEmpty() && mTableName.startsWith( '(' ) && mTableName.endsWith( ')' ) )
54 {
55 mIsQuery = true;
56 mQuery = mTableName;
57 mTableName.clear();
58 }
59 else
60 */
61 {
62 mIsQuery = false;
63
64 if ( !mSchemaName.isEmpty() )
65 {
66 mQuery += quotedIdentifier( mSchemaName ) + '.';
67 }
68
69 if ( !mTableName.isEmpty() )
70 {
71 mQuery += quotedIdentifier( mTableName );
72 }
73 }
74
75 // TODO: for now always true
76 // mUseEstimatedMetadata = mUri.useEstimatedMetadata();
77
78 QgsDebugMsgLevel( QStringLiteral( "Connection info is %1" ).arg( mUri.connectionInfo( false ) ), 4 );
79 QgsDebugMsgLevel( QStringLiteral( "Schema is: %1" ).arg( mSchemaName ), 4 );
80 QgsDebugMsgLevel( QStringLiteral( "Table name is: %1" ).arg( mTableName ), 4 );
81 QgsDebugMsgLevel( QStringLiteral( "Query is: %1" ).arg( mQuery ), 4 );
82 QgsDebugMsgLevel( QStringLiteral( "Where clause is: %1" ).arg( mSqlWhereClause ), 4 );
83
84 // no table/query passed, the provider could be used to get tables
85 if ( mQuery.isEmpty() )
86 {
87 return;
88 }
89
90 mConnectionRO = QgsPostgresConn::connectDb( mUri.connectionInfo( false ), true );
91 if ( !mConnectionRO )
92 {
93 return;
94 }
95
96 if ( !hasSufficientPermsAndCapabilities() ) // check permissions and set capabilities
97 {
98 disconnectDb();
99 return;
100 }
101
102 if ( !init() ) // gets srid and data type
103 {
104 // the table is not a raster table
105 QgsMessageLog::logMessage( tr( "Invalid PostgreSQL raster layer" ), tr( "PostGIS" ) );
106 disconnectDb();
107 return;
108 }
109
110 // Check if requested srid and detected srid match
111 if ( ! mDetectedSrid.isEmpty() && ! mRequestedSrid.isEmpty() && mRequestedSrid != mDetectedSrid )
112 {
113 QgsMessageLog::logMessage( tr( "Requested SRID (%1) and detected SRID (%2) differ" )
114 .arg( mRequestedSrid )
115 .arg( mDetectedSrid ),
116 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
117 }
118
119 mLayerMetadata.setType( QStringLiteral( "dataset" ) );
120 mLayerMetadata.setCrs( crs() );
121
122 mValid = true;
123 }
124
QgsPostgresRasterProvider(const QgsPostgresRasterProvider & other,const QgsDataProvider::ProviderOptions & providerOptions,QgsDataProvider::ReadFlags flags)125 QgsPostgresRasterProvider::QgsPostgresRasterProvider( const QgsPostgresRasterProvider &other, const QgsDataProvider::ProviderOptions &providerOptions, QgsDataProvider::ReadFlags flags )
126 : QgsRasterDataProvider( other.dataSourceUri(), providerOptions, flags )
127 , mValid( other.mValid )
128 , mCrs( other.mCrs )
129 , mUri( other.mUri )
130 , mIsQuery( other.mIsQuery )
131 , mTableName( other.mTableName )
132 , mQuery( other.mQuery )
133 , mRasterColumn( other.mRasterColumn )
134 , mSchemaName( other.mSchemaName )
135 , mSqlWhereClause( other.mSqlWhereClause )
136 , mUseEstimatedMetadata( other.mUseEstimatedMetadata )
137 , mDataTypes( other.mDataTypes )
138 , mDataSizes( other.mDataSizes )
139 , mOverViews( other.mOverViews )
140 , mBandCount( other.mBandCount )
141 , mIsTiled( other.mIsTiled )
142 , mIsOutOfDb( other.mIsOutOfDb )
143 , mHasSpatialIndex( other.mHasSpatialIndex )
144 , mWidth( other.mWidth )
145 , mHeight( other.mHeight )
146 , mTileWidth( other.mTileWidth )
147 , mTileHeight( other.mTileHeight )
148 , mScaleX( other.mScaleX )
149 , mScaleY( other.mScaleY )
150 , mTemporalFieldIndex( other.mTemporalFieldIndex )
151 , mTemporalDefaultTime( other.mTemporalDefaultTime )
152 , mAttributeFields( other.mAttributeFields )
153 , mIdentityFields( other.mIdentityFields )
154 , mDefaultValues( other.mDefaultValues )
155 , mDataComment( other.mDataComment )
156 , mDetectedSrid( other.mDetectedSrid )
157 , mRequestedSrid( other.mRequestedSrid )
158 , mConnectionRO( other.mConnectionRO )
159 , mConnectionRW( other.mConnectionRW )
160 , mPrimaryKeyType( other.mPrimaryKeyType )
161 , mPrimaryKeyAttrs( other.mPrimaryKeyAttrs )
162 , mShared( other.mShared )
163 {
164 }
165
166
hasSufficientPermsAndCapabilities()167 bool QgsPostgresRasterProvider::hasSufficientPermsAndCapabilities()
168 {
169 QgsDebugMsgLevel( QStringLiteral( "Checking for permissions on the relation" ), 4 );
170
171 QgsPostgresResult testAccess;
172 if ( !mIsQuery )
173 {
174 // Check that we can read from the table (i.e., we have select permission).
175 QString sql = QStringLiteral( "SELECT * FROM %1 LIMIT 1" ).arg( mQuery );
176 QgsPostgresResult testAccess( connectionRO()->PQexec( sql ) );
177 if ( testAccess.PQresultStatus() != PGRES_TUPLES_OK )
178 {
179 QgsMessageLog::logMessage( tr( "Unable to access the %1 relation.\nThe error message from the database was:\n%2.\nSQL: %3" )
180 .arg( mQuery,
181 testAccess.PQresultErrorMessage(),
182 sql ), tr( "PostGIS" ) );
183 return false;
184 }
185
186 if ( connectionRO()->pgVersion() >= 90000 )
187 {
188 testAccess = connectionRO()->PQexec( QStringLiteral( "SELECT pg_is_in_recovery()" ) );
189 if ( testAccess.PQresultStatus() != PGRES_TUPLES_OK || testAccess.PQgetvalue( 0, 0 ) == QLatin1String( "t" ) )
190 {
191 QgsMessageLog::logMessage( tr( "PostgreSQL is still in recovery after a database crash\n(or you are connected to a (read-only) standby server).\nWrite accesses will be denied." ), tr( "PostGIS" ) );
192 }
193 }
194 }
195 return true;
196 }
197
crs() const198 QgsCoordinateReferenceSystem QgsPostgresRasterProvider::crs() const
199 {
200 return mCrs;
201 }
202
extent() const203 QgsRectangle QgsPostgresRasterProvider::extent() const
204 {
205 return mExtent;
206 }
207
isValid() const208 bool QgsPostgresRasterProvider::isValid() const
209 {
210 return mValid;
211 }
212
name() const213 QString QgsPostgresRasterProvider::name() const
214 {
215 return QgsPostgresRasterProvider::PG_RASTER_PROVIDER_KEY;
216 }
217
description() const218 QString QgsPostgresRasterProvider::description() const
219 {
220 return QgsPostgresRasterProvider::PG_RASTER_PROVIDER_DESCRIPTION;
221 }
222
readBlock(int bandNo,const QgsRectangle & viewExtent,int width,int height,void * data,QgsRasterBlockFeedback *)223 bool QgsPostgresRasterProvider::readBlock( int bandNo, const QgsRectangle &viewExtent, int width, int height, void *data, QgsRasterBlockFeedback * )
224 {
225 if ( bandNo > mBandCount )
226 {
227 QgsMessageLog::logMessage( tr( "Invalid band number '%1" ).arg( bandNo ), QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Critical );
228 return false;
229 }
230
231 const QgsRectangle rasterExtent = viewExtent.intersect( mExtent );
232 if ( rasterExtent.isEmpty() )
233 {
234 QgsMessageLog::logMessage( tr( "Requested extent is not valid" ), QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Critical );
235 return false;
236 }
237
238 const bool isSingleValue { width == 1 && height == 1 };
239 QString tableToQuery { mQuery };
240
241 QString whereAnd { subsetStringWithTemporalRange() };
242 if ( ! whereAnd.isEmpty() )
243 {
244 whereAnd = whereAnd.append( QStringLiteral( " AND " ) );
245 }
246
247 // Identify
248 if ( isSingleValue )
249 {
250 QString sql;
251 sql = QStringLiteral( "SELECT ST_Value( ST_Band( %1, %2), ST_GeomFromText( %3, %4 ), FALSE ) "
252 "FROM %5 "
253 "WHERE %6 %1 && ST_GeomFromText( %3, %4 )" )
254 .arg( quotedIdentifier( mRasterColumn ) )
255 .arg( bandNo )
256 .arg( quotedValue( viewExtent.center().asWkt() ) )
257 .arg( mCrs.postgisSrid() )
258 .arg( mQuery )
259 .arg( whereAnd );
260
261 QgsPostgresResult result( connectionRO()->PQexec( sql ) );
262 if ( result.PQresultStatus() != PGRES_TUPLES_OK )
263 {
264 QgsMessageLog::logMessage( tr( "Unable to access the %1 relation.\nThe error message from the database was:\n%2.\nSQL: %3" )
265 .arg( mQuery,
266 result.PQresultErrorMessage(),
267 sql ), tr( "PostGIS" ) );
268 return false;
269 }
270
271 bool ok;
272 const QString val { result.PQgetvalue( 0, 0 ) };
273 const Qgis::DataType dataType { mDataTypes[ static_cast<unsigned int>( bandNo - 1 ) ] };
274 switch ( dataType )
275 {
276 case Qgis::DataType::Byte:
277 {
278 const unsigned short byte { val.toUShort( &ok ) };
279 if ( ! ok )
280 {
281 QgsMessageLog::logMessage( tr( "Cannot convert identified value to byte" ), QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
282 return false;
283 }
284 std::memcpy( data, &byte, sizeof( unsigned short ) );
285 break;
286 }
287 case Qgis::DataType::UInt16:
288 {
289 const unsigned int uint { val.toUInt( &ok ) };
290 if ( ! ok )
291 {
292 QgsMessageLog::logMessage( tr( "Cannot convert identified value to unsigned int" ), QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
293 return false;
294 }
295 std::memcpy( data, &uint, sizeof( unsigned int ) );
296 break;
297 }
298 case Qgis::DataType::UInt32:
299 {
300 const unsigned long ulong { val.toULong( &ok ) };
301 if ( ! ok )
302 {
303 QgsMessageLog::logMessage( tr( "Cannot convert identified value to unsigned long" ), QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
304 return false;
305 }
306 std::memcpy( data, &ulong, sizeof( unsigned long ) );
307 break;
308 }
309 case Qgis::DataType::Int16:
310 {
311 const int intVal { val.toInt( &ok ) };
312 if ( ! ok )
313 {
314 QgsMessageLog::logMessage( tr( "Cannot convert identified value to int" ), QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
315 return false;
316 }
317 std::memcpy( data, &intVal, sizeof( int ) );
318 break;
319 }
320 case Qgis::DataType::Int32:
321 {
322 const long longVal { val.toLong( &ok ) };
323 if ( ! ok )
324 {
325 QgsMessageLog::logMessage( tr( "Cannot convert identified value to long" ), QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
326 return false;
327 }
328 std::memcpy( data, &longVal, sizeof( long ) );
329 break;
330 }
331 case Qgis::DataType::Float32:
332 {
333 const float floatVal { val.toFloat( &ok ) };
334 if ( ! ok )
335 {
336 QgsMessageLog::logMessage( tr( "Cannot convert identified value to float" ), QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
337 return false;
338 }
339 std::memcpy( data, &floatVal, sizeof( float ) );
340 break;
341 }
342 case Qgis::DataType::Float64:
343 {
344 const double doubleVal { val.toDouble( &ok ) };
345 if ( ! ok )
346 {
347 QgsMessageLog::logMessage( tr( "Cannot convert identified value to double" ), QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
348 return false;
349 }
350 std::memcpy( data, &doubleVal, sizeof( double ) );
351 break;
352 }
353 default:
354 {
355 QgsMessageLog::logMessage( tr( "Unknown identified data type" ), QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
356 return false;
357 }
358 }
359 }
360 else // Fetch block
361 {
362
363 const double xRes = viewExtent.width() / width;
364 const double yRes = viewExtent.height() / height;
365
366 // Find overview
367 const int minPixelSize { static_cast<int>( std::min( xRes, yRes ) ) };
368 // TODO: round?
369 const unsigned int desiredOverviewFactor { static_cast<unsigned int>( minPixelSize / std::max( std::abs( mScaleX ), std::abs( mScaleY ) ) ) };
370
371 unsigned int overviewFactor { 1 }; // no overview
372
373 // Cannot use overviews if there is a where condition
374 if ( whereAnd.isEmpty() )
375 {
376 const auto ovKeys { mOverViews.keys( ) };
377 QList<unsigned int>::const_reverse_iterator rit { ovKeys.rbegin() };
378 for ( ; rit != ovKeys.rend(); ++rit )
379 {
380 if ( *rit <= desiredOverviewFactor )
381 {
382 tableToQuery = mOverViews[ *rit ];
383 overviewFactor = *rit;
384 QgsDebugMsgLevel( QStringLiteral( "Using overview for block read: %1" ).arg( tableToQuery ), 3 );
385 break;
386 }
387 }
388 }
389
390 //qDebug() << "Overview desired: " << desiredOverviewFactor << "found:" << overviewFactor << tableToQuery;
391 //qDebug() << "View extent" << viewExtent.toString( 1 ) << width << height << minPixelSize;
392
393 // Get the the tiles we need to build the block
394 const QgsPostgresRasterSharedData::TilesRequest tilesRequest
395 {
396 bandNo,
397 rasterExtent,
398 overviewFactor,
399 pkSql(), // already quoted
400 quotedIdentifier( mRasterColumn ),
401 tableToQuery,
402 QString::number( mCrs.postgisSrid() ),
403 whereAnd,
404 connectionRO()
405 };
406
407 const QgsPostgresRasterSharedData::TilesResponse tileResponse
408 {
409 mShared->tiles( tilesRequest )
410 };
411
412 if ( tileResponse.tiles.isEmpty() )
413 {
414 QgsMessageLog::logMessage( tr( "No tiles available in table %1 for the requested extent: %2" )
415 .arg( tableToQuery, rasterExtent.toString( ) ), tr( "PostGIS" ), Qgis::MessageLevel::Critical );
416 return false;
417 }
418
419
420 // Finally merge the tiles
421 // We must have at least one tile at this point (we checked for that before)
422
423 const QgsRectangle &tilesExtent { tileResponse.extent };
424
425 // Prepare tmp output raster
426 const int tmpWidth = static_cast<int>( std::round( tilesExtent.width() / tileResponse.tiles.first().scaleX ) );
427 const int tmpHeight = static_cast<int>( std::round( tilesExtent.height() / std::fabs( tileResponse.tiles.first().scaleY ) ) );
428
429 GDALDataType gdalDataType { static_cast<GDALDataType>( sourceDataType( bandNo ) ) };
430
431 //qDebug() << "Creating output raster: " << tilesExtent.toString() << tmpWidth << tmpHeight;
432
433 gdal::dataset_unique_ptr tmpDS { QgsGdalUtils::createSingleBandMemoryDataset(
434 gdalDataType, tilesExtent, tmpWidth, tmpHeight, mCrs ) };
435 if ( ! tmpDS )
436 {
437 {
438 QgsMessageLog::logMessage( tr( "Unable to create temporary raster for tiles from %1" )
439 .arg( tableToQuery ), tr( "PostGIS" ), Qgis::MessageLevel::Critical );
440 return false;
441 }
442 }
443
444 // Write tiles to the temporary raster
445 CPLErrorReset();
446 for ( auto &tile : std::as_const( tileResponse.tiles ) )
447 {
448 // Offset in px from the base raster
449 const int xOff { static_cast<int>( std::round( ( tile.upperLeftX - tilesExtent.xMinimum() ) / tile.scaleX ) ) };
450 const int yOff { static_cast<int>( std::round( ( tilesExtent.yMaximum() - tile.extent.yMaximum() ) / std::fabs( tile.scaleY ) ) )};
451
452 //qDebug() << "Merging tile output raster: " << tile.tileId << xOff << yOff << tile.width << tile.height ;
453
454 CPLErr err = GDALRasterIO( GDALGetRasterBand( tmpDS.get(), 1 ),
455 GF_Write,
456 xOff,
457 yOff,
458 static_cast<int>( tile.width ),
459 static_cast<int>( tile.height ),
460 ( void * )( tile.data.constData() ), // old-style because of const
461 static_cast<int>( tile.width ),
462 static_cast<int>( tile.height ),
463 gdalDataType,
464 0,
465 0 );
466 if ( err != CE_None )
467 {
468 const QString lastError = QString::fromUtf8( CPLGetLastErrorMsg() ) ;
469 QgsMessageLog::logMessage( tr( "Unable to write tile to temporary raster from %1: %2" )
470 .arg( tableToQuery, lastError ), tr( "PostGIS" ), Qgis::MessageLevel::Critical );
471 return false;
472 }
473 }
474
475 #if 0
476 // Debug output raster content
477 double pdfMin;
478 double pdfMax;
479 double pdfMean;
480 double pdfStdDev;
481 GDALGetRasterStatistics( GDALGetRasterBand( tmpDS.get(), 1 ), 0, 1, &pdfMin, &pdfMax, &pdfMean, &pdfStdDev );
482 qDebug() << pdfMin << pdfMax << pdfMean << pdfStdDev;
483 #endif
484
485 // Write data to the output block
486 gdal::dataset_unique_ptr dstDS { QgsGdalUtils::createSingleBandMemoryDataset(
487 gdalDataType, viewExtent, width, height, mCrs ) };
488 if ( ! dstDS )
489 {
490 const QString lastError = QString::fromUtf8( CPLGetLastErrorMsg() ) ;
491 QgsMessageLog::logMessage( tr( "Unable to create destination raster for tiles from %1: %2" )
492 .arg( tableToQuery, lastError ), tr( "PostGIS" ), Qgis::MessageLevel::Critical );
493 return false;
494 }
495
496 // Resample the raster to the final bounds and resolution
497 if ( ! QgsGdalUtils::resampleSingleBandRaster( tmpDS.get(), dstDS.get(), GDALResampleAlg::GRA_NearestNeighbour, nullptr ) )
498 {
499 const QString lastError = QString::fromUtf8( CPLGetLastErrorMsg() ) ;
500 QgsMessageLog::logMessage( tr( "Unable to resample and transform destination raster for tiles from %1: %2" )
501 .arg( tableToQuery, lastError ), tr( "PostGIS" ), Qgis::MessageLevel::Critical );
502 return false;
503 }
504
505 // Copy to result buffer
506 CPLErrorReset();
507 CPLErr err = GDALRasterIO( GDALGetRasterBand( dstDS.get(), 1 ),
508 GF_Read,
509 0,
510 0,
511 width,
512 height,
513 data,
514 width,
515 height,
516 gdalDataType,
517 0,
518 0 );
519 if ( err != CE_None )
520 {
521 const QString lastError = QString::fromUtf8( CPLGetLastErrorMsg() ) ;
522 QgsMessageLog::logMessage( tr( "Unable to write raster to block from %1: %2" )
523 .arg( mQuery, lastError ), tr( "PostGIS" ), Qgis::MessageLevel::Critical );
524 return false;
525 }
526
527 #if 0
528 GDALGetRasterStatistics( GDALGetRasterBand( dstDS.get(), 1 ), 0, 1, &pdfMin, &pdfMax, &pdfMean, &pdfStdDev );
529 qDebug() << pdfMin << pdfMax << pdfMean << pdfStdDev;
530
531 // Spit it out float 32 data
532 for ( int i = 0; i < width * height; ++i )
533 {
534 qDebug() << reinterpret_cast<const float *>( data )[ i * 4 ];
535 }
536 #endif
537
538 }
539 return true;
540 }
541
542
QgsPostgresRasterProviderMetadata()543 QgsPostgresRasterProviderMetadata::QgsPostgresRasterProviderMetadata()
544 : QgsProviderMetadata( QgsPostgresRasterProvider::PG_RASTER_PROVIDER_KEY, QgsPostgresRasterProvider::PG_RASTER_PROVIDER_DESCRIPTION )
545 {
546
547 }
548
decodeUri(const QString & uri) const549 QVariantMap QgsPostgresRasterProviderMetadata::decodeUri( const QString &uri ) const
550 {
551 const QgsDataSourceUri dsUri { uri };
552 QVariantMap decoded;
553
554 if ( ! dsUri.database().isEmpty() )
555 {
556 decoded[ QStringLiteral( "dbname" ) ] = dsUri.database();
557 }
558 if ( ! dsUri.host().isEmpty() )
559 {
560 decoded[ QStringLiteral( "host" ) ] = dsUri.host();
561 }
562 if ( ! dsUri.port().isEmpty() )
563 {
564 decoded[ QStringLiteral( "port" ) ] = dsUri.port();
565 }
566 if ( ! dsUri.service().isEmpty() )
567 {
568 decoded[ QStringLiteral( "service" ) ] = dsUri.service();
569 }
570 if ( ! dsUri.username().isEmpty() )
571 {
572 decoded[ QStringLiteral( "username" ) ] = dsUri.username();
573 }
574 if ( ! dsUri.password().isEmpty() )
575 {
576 decoded[ QStringLiteral( "password" ) ] = dsUri.password();
577 }
578 if ( ! dsUri.authConfigId().isEmpty() )
579 {
580 decoded[ QStringLiteral( "authcfg" ) ] = dsUri.authConfigId();
581 }
582 if ( ! dsUri.schema().isEmpty() )
583 {
584 decoded[ QStringLiteral( "schema" ) ] = dsUri.schema();
585 }
586 if ( ! dsUri.table().isEmpty() )
587 {
588 decoded[ QStringLiteral( "table" ) ] = dsUri.table();
589 }
590 if ( ! dsUri.keyColumn().isEmpty() )
591 {
592 decoded[ QStringLiteral( "key" ) ] = dsUri.keyColumn();
593 }
594 if ( ! dsUri.srid().isEmpty() )
595 {
596 decoded[ QStringLiteral( "srid" ) ] = dsUri.srid();
597 }
598 if ( uri.contains( QStringLiteral( "estimatedmetadata=" ), Qt::CaseSensitivity::CaseInsensitive ) )
599 {
600 decoded[ QStringLiteral( "estimatedmetadata" ) ] = dsUri.useEstimatedMetadata();
601 }
602 if ( uri.contains( QStringLiteral( "sslmode=" ), Qt::CaseSensitivity::CaseInsensitive ) )
603 {
604 decoded[ QStringLiteral( "sslmode" ) ] = dsUri.sslMode();
605 }
606 // Do not add sql if it's empty
607 if ( ! dsUri.sql().isEmpty() )
608 {
609 decoded[ QStringLiteral( "sql" ) ] = dsUri.sql();
610 }
611 if ( ! dsUri.geometryColumn().isEmpty() )
612 {
613 decoded[ QStringLiteral( "geometrycolumn" ) ] = dsUri.geometryColumn();
614 }
615
616 // Params
617 const static QStringList params {{
618 QStringLiteral( "temporalFieldIndex" ),
619 QStringLiteral( "temporalDefaultTime" ),
620 QStringLiteral( "enableTime" )
621 }};
622
623 for ( const QString &pname : std::as_const( params ) )
624 {
625 if ( dsUri.hasParam( pname ) )
626 {
627 decoded[ pname ] = dsUri.param( pname );
628 }
629 }
630
631 return decoded;
632 }
633
634
encodeUri(const QVariantMap & parts) const635 QString QgsPostgresRasterProviderMetadata::encodeUri( const QVariantMap &parts ) const
636 {
637 QgsDataSourceUri dsUri;
638 if ( parts.contains( QStringLiteral( "dbname" ) ) )
639 dsUri.setDatabase( parts.value( QStringLiteral( "dbname" ) ).toString() );
640 if ( parts.contains( QStringLiteral( "port" ) ) )
641 dsUri.setParam( QStringLiteral( "port" ), parts.value( QStringLiteral( "port" ) ).toString() );
642 if ( parts.contains( QStringLiteral( "host" ) ) )
643 dsUri.setParam( QStringLiteral( "host" ), parts.value( QStringLiteral( "host" ) ).toString() );
644 if ( parts.contains( QStringLiteral( "service" ) ) )
645 dsUri.setParam( QStringLiteral( "service" ), parts.value( QStringLiteral( "service" ) ).toString() );
646 if ( parts.contains( QStringLiteral( "username" ) ) )
647 dsUri.setUsername( parts.value( QStringLiteral( "username" ) ).toString() );
648 if ( parts.contains( QStringLiteral( "password" ) ) )
649 dsUri.setPassword( parts.value( QStringLiteral( "password" ) ).toString() );
650 if ( parts.contains( QStringLiteral( "authcfg" ) ) )
651 dsUri.setAuthConfigId( parts.value( QStringLiteral( "authcfg" ) ).toString() );
652 if ( parts.contains( QStringLiteral( "selectatid" ) ) )
653 dsUri.setParam( QStringLiteral( "selectatid" ), parts.value( QStringLiteral( "selectatid" ) ).toString() );
654 if ( parts.contains( QStringLiteral( "table" ) ) )
655 dsUri.setTable( parts.value( QStringLiteral( "table" ) ).toString() );
656 if ( parts.contains( QStringLiteral( "schema" ) ) )
657 dsUri.setSchema( parts.value( QStringLiteral( "schema" ) ).toString() );
658 if ( parts.contains( QStringLiteral( "key" ) ) )
659 dsUri.setParam( QStringLiteral( "key" ), parts.value( QStringLiteral( "key" ) ).toString() );
660 if ( parts.contains( QStringLiteral( "srid" ) ) )
661 dsUri.setSrid( parts.value( QStringLiteral( "srid" ) ).toString() );
662 if ( parts.contains( QStringLiteral( "estimatedmetadata" ) ) )
663 dsUri.setParam( QStringLiteral( "estimatedmetadata" ), parts.value( QStringLiteral( "estimatedmetadata" ) ).toString() );
664 if ( parts.contains( QStringLiteral( "sslmode" ) ) )
665 dsUri.setParam( QStringLiteral( "sslmode" ), QgsDataSourceUri::encodeSslMode( static_cast<QgsDataSourceUri::SslMode>( parts.value( QStringLiteral( "sslmode" ) ).toInt( ) ) ) );
666 if ( parts.contains( QStringLiteral( "sql" ) ) )
667 dsUri.setSql( parts.value( QStringLiteral( "sql" ) ).toString() );
668 if ( parts.contains( QStringLiteral( "geometrycolumn" ) ) )
669 dsUri.setGeometryColumn( parts.value( QStringLiteral( "geometrycolumn" ) ).toString() );
670 if ( parts.contains( QStringLiteral( "temporalFieldIndex" ) ) )
671 dsUri.setParam( QStringLiteral( "temporalFieldIndex" ), parts.value( QStringLiteral( "temporalFieldIndex" ) ).toString() );
672 if ( parts.contains( QStringLiteral( "temporalDefaultTime" ) ) )
673 dsUri.setParam( QStringLiteral( "temporalDefaultTime" ), parts.value( QStringLiteral( "temporalDefaultTime" ) ).toString() );
674 if ( parts.contains( QStringLiteral( "enableTime" ) ) )
675 dsUri.setParam( QStringLiteral( "enableTime" ), parts.value( QStringLiteral( "enableTime" ) ).toString() );
676 return dsUri.uri( false );
677 }
678
createProvider(const QString & uri,const QgsDataProvider::ProviderOptions & options,QgsDataProvider::ReadFlags flags)679 QgsPostgresRasterProvider *QgsPostgresRasterProviderMetadata::createProvider( const QString &uri, const QgsDataProvider::ProviderOptions &options, QgsDataProvider::ReadFlags flags )
680 {
681 return new QgsPostgresRasterProvider( uri, options, flags );
682 }
683
684
dataType(int bandNo) const685 Qgis::DataType QgsPostgresRasterProvider::dataType( int bandNo ) const
686 {
687 if ( mDataTypes.size() < static_cast<unsigned long>( bandNo ) )
688 {
689 QgsMessageLog::logMessage( tr( "Data type size for band %1 could not be found: num bands is: %2 and the type size map for bands contains: %3 items" )
690 .arg( bandNo )
691 .arg( mBandCount )
692 .arg( mDataSizes.size() ),
693 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
694 return Qgis::DataType::UnknownDataType;
695 }
696 // Band is 1-based
697 return mDataTypes[ static_cast<unsigned long>( bandNo ) - 1 ];
698 }
699
bandCount() const700 int QgsPostgresRasterProvider::bandCount() const
701 {
702 return mBandCount;
703 }
704
clone() const705 QgsPostgresRasterProvider *QgsPostgresRasterProvider::clone() const
706 {
707 QgsDataProvider::ProviderOptions options;
708 options.transformContext = transformContext();
709 QgsPostgresRasterProvider *provider = new QgsPostgresRasterProvider( *this, options );
710 provider->copyBaseSettings( *this );
711 return provider;
712 }
713
714
dumpVariantMap(const QVariantMap & variantMap,const QString & title=QString ())715 static inline QString dumpVariantMap( const QVariantMap &variantMap, const QString &title = QString() )
716 {
717 QString result;
718 if ( !title.isEmpty() )
719 {
720 result += QStringLiteral( "<tr><td class=\"highlight\">%1</td><td></td></tr>" ).arg( title );
721 }
722 for ( auto it = variantMap.constBegin(); it != variantMap.constEnd(); ++it )
723 {
724 const QVariantMap childMap = it.value().toMap();
725 const QVariantList childList = it.value().toList();
726 if ( !childList.isEmpty() )
727 {
728 result += QStringLiteral( "<tr><td class=\"highlight\">%1</td><td><ul>" ).arg( it.key() );
729 for ( const QVariant &v : childList )
730 {
731 const QVariantMap grandChildMap = v.toMap();
732 if ( !grandChildMap.isEmpty() )
733 {
734 result += QStringLiteral( "<li><table>%1</table></li>" ).arg( dumpVariantMap( grandChildMap ) );
735 }
736 else
737 {
738 result += QStringLiteral( "<li>%1</li>" ).arg( QgsStringUtils::insertLinks( v.toString() ) );
739 }
740 }
741 result += QLatin1String( "</ul></td></tr>" );
742 }
743 else if ( !childMap.isEmpty() )
744 {
745 result += QStringLiteral( "<tr><td class=\"highlight\">%1</td><td><table>%2</table></td></tr>" ).arg( it.key(), dumpVariantMap( childMap ) );
746 }
747 else
748 {
749 result += QStringLiteral( "<tr><td class=\"highlight\">%1</td><td>%2</td></tr>" ).arg( it.key(), QgsStringUtils::insertLinks( it.value().toString() ) );
750 }
751 }
752 return result;
753 }
754
htmlMetadata()755 QString QgsPostgresRasterProvider::htmlMetadata()
756 {
757 // This must return the content of a HTML table starting by tr and ending by tr
758 QVariantMap overviews;
759 for ( auto it = mOverViews.constBegin(); it != mOverViews.constEnd(); ++it )
760 {
761 overviews.insert( QString::number( it.key() ), it.value() );
762 }
763
764 const QVariantMap additionalInformation
765 {
766 { tr( "Is Tiled" ), mIsTiled },
767 { tr( "Where Clause SQL" ), subsetString() },
768 { tr( "Pixel Size" ), QStringLiteral( "%1, %2" ).arg( mScaleX ).arg( mScaleY ) },
769 { tr( "Overviews" ), overviews },
770 { tr( "Primary Keys SQL" ), pkSql() },
771 { tr( "Temporal Column" ), mTemporalFieldIndex >= 0 && mAttributeFields.exists( mTemporalFieldIndex ) ? mAttributeFields.field( mTemporalFieldIndex ).name() : QString() },
772 };
773 return dumpVariantMap( additionalInformation, tr( "Additional information" ) );
774 }
775
lastErrorTitle()776 QString QgsPostgresRasterProvider::lastErrorTitle()
777 {
778 return mErrorTitle;
779 }
780
lastError()781 QString QgsPostgresRasterProvider::lastError()
782 {
783 return mError;
784 }
785
capabilities() const786 int QgsPostgresRasterProvider::capabilities() const
787 {
788 const int capability = QgsRasterDataProvider::Identify
789 | QgsRasterDataProvider::IdentifyValue
790 | QgsRasterDataProvider::Size
791 // TODO:| QgsRasterDataProvider::BuildPyramids
792 | QgsRasterDataProvider::Create
793 | QgsRasterDataProvider::Remove
794 | QgsRasterDataProvider::Prefetch;
795 return capability;
796 }
797
connectionRO() const798 QgsPostgresConn *QgsPostgresRasterProvider::connectionRO() const
799 {
800 return mConnectionRO;
801 }
802
connectionRW()803 QgsPostgresConn *QgsPostgresRasterProvider::connectionRW()
804 {
805 if ( !mConnectionRW )
806 {
807 mConnectionRW = QgsPostgresConn::connectDb( mUri.connectionInfo( false ), false );
808 }
809 return mConnectionRW;
810 }
811
subsetString() const812 QString QgsPostgresRasterProvider::subsetString() const
813 {
814 return mSqlWhereClause;
815 }
816
defaultTimeSubsetString(const QDateTime & defaultTime) const817 QString QgsPostgresRasterProvider::defaultTimeSubsetString( const QDateTime &defaultTime ) const
818 {
819 if ( defaultTime.isValid( ) &&
820 mTemporalFieldIndex >= 0 &&
821 mAttributeFields.exists( mTemporalFieldIndex ) )
822 {
823 const QgsField temporalField { mAttributeFields.field( mTemporalFieldIndex ) };
824 const QString typeCast { temporalField.type() != QVariant::DateTime ? QStringLiteral( "::timestamp" ) : QString() };
825 const QString temporalFieldName { temporalField.name() };
826 return { QStringLiteral( "%1%2 = %3" )
827 .arg( quotedIdentifier( temporalFieldName ),
828 typeCast,
829 quotedValue( defaultTime.toString( Qt::DateFormat::ISODate ) ) ) };
830 }
831 else
832 {
833 return QString();
834 }
835 }
836
setSubsetString(const QString & subset,bool updateFeatureCount)837 bool QgsPostgresRasterProvider::setSubsetString( const QString &subset, bool updateFeatureCount )
838 {
839 Q_UNUSED( updateFeatureCount )
840
841 const QString oldSql { mSqlWhereClause };
842
843 mSqlWhereClause = subset;
844 // Recalculate extent and other metadata calling init()
845 if ( !init() )
846 {
847 // Restore
848 mSqlWhereClause = oldSql;
849 init();
850 return false;
851 }
852
853 mStatistics.clear();
854 mShared->invalidateCache();
855
856 // Update datasource uri too
857 mUri.setSql( subset );
858 setDataSourceUri( mUri.uri( false ) );
859
860 return true;
861 }
862
subsetStringWithTemporalRange() const863 QString QgsPostgresRasterProvider::subsetStringWithTemporalRange() const
864 {
865 // Temporal
866 if ( mTemporalFieldIndex >= 0 && mAttributeFields.exists( mTemporalFieldIndex ) )
867 {
868 const QgsField temporalField { mAttributeFields.field( mTemporalFieldIndex ) };
869 const QString typeCast { temporalField.type() != QVariant::DateTime ? QStringLiteral( "::timestamp" ) : QString() };
870 const QString temporalFieldName { temporalField.name() };
871
872 if ( temporalCapabilities()->hasTemporalCapabilities() )
873 {
874 QString temporalClause;
875 const QgsTemporalRange<QDateTime> requestedRange { temporalCapabilities()->requestedTemporalRange() };
876 if ( ! requestedRange.isEmpty() && ! requestedRange.isInfinite() )
877 {
878 if ( requestedRange.isInstant() )
879 {
880 temporalClause = QStringLiteral( "%1%2 = %3" )
881 .arg( quotedIdentifier( temporalFieldName ),
882 typeCast,
883 quotedValue( requestedRange.begin().toString( Qt::DateFormat::ISODate ) ) );
884 }
885 else
886 {
887 if ( requestedRange.begin().isValid() )
888 {
889 temporalClause = QStringLiteral( "%1%2 %3 %4" )
890 .arg( quotedIdentifier( temporalFieldName ),
891 typeCast,
892 requestedRange.includeBeginning() ? ">=" : ">",
893 quotedValue( requestedRange.begin().toString( Qt::DateFormat::ISODate ) ) );
894 }
895 if ( requestedRange.end().isValid() )
896 {
897 if ( ! temporalClause.isEmpty() )
898 {
899 temporalClause.append( QStringLiteral( " AND " ) );
900 }
901 temporalClause.append( QStringLiteral( "%1%2 %3 %4" )
902 .arg( quotedIdentifier( temporalFieldName ),
903 typeCast,
904 requestedRange.includeEnd() ? "<=" : "<",
905 quotedValue( requestedRange.end().toString( Qt::DateFormat::ISODate ) ) ) );
906 }
907 }
908 return mSqlWhereClause.isEmpty() ? temporalClause : QStringLiteral( "%1 AND (%2)" ).arg( mSqlWhereClause, temporalClause );
909 }
910 const QString defaultTimeSubset { defaultTimeSubsetString( mTemporalDefaultTime ) };
911 if ( ! defaultTimeSubset.isEmpty() )
912 {
913 return mSqlWhereClause.isEmpty() ? defaultTimeSubset : QStringLiteral( "%1 AND (%2)" ).arg( mSqlWhereClause, defaultTimeSubset );
914 }
915 }
916 }
917 return mSqlWhereClause;
918 }
919
920
disconnectDb()921 void QgsPostgresRasterProvider::disconnectDb()
922 {
923 if ( mConnectionRO )
924 {
925 mConnectionRO->unref();
926 mConnectionRO = nullptr;
927 }
928
929 if ( mConnectionRW )
930 {
931 mConnectionRW->unref();
932 mConnectionRW = nullptr;
933 }
934 }
935
init()936 bool QgsPostgresRasterProvider::init()
937 {
938
939 // WARNING: multiple failure and return points!
940
941 if ( !determinePrimaryKey() )
942 {
943 QgsMessageLog::logMessage( tr( "PostgreSQL raster layer has no primary key." ), tr( "PostGIS" ) );
944 return false;
945 }
946
947 // We first try to collect raster information using raster_columns information
948 // unless:
949 // - it is a query layer (unsupported at the moment)
950 // - use estimated metadata is false
951 // - there is a WHERE condition (except for temporal default value )
952 // If previous conditions are not met or the first method fail try to fetch information
953 // directly from the raster data. This can be very slow.
954 // Note that a temporal filter set as temporal default value does not count as a WHERE condition
955
956 // utility to get data type from string, used in both branches
957 auto pixelTypeFromString = [ ]( const QString & t ) -> Qgis::DataType
958 {
959 /* Pixel types
960 1BB - 1-bit boolean
961 2BUI - 2-bit unsigned integer
962 4BUI - 4-bit unsigned integer
963 8BSI - 8-bit signed integer
964 8BUI - 8-bit unsigned integer
965 16BSI - 16-bit signed integer
966 16BUI - 16-bit unsigned integer
967 32BSI - 32-bit signed integer
968 32BUI - 32-bit unsigned integer
969 32BF - 32-bit float
970 64BF - 64-bit float
971 */
972 Qgis::DataType type { Qgis::DataType::UnknownDataType };
973 if ( t == QLatin1String( "8BUI" ) )
974 {
975 type = Qgis::DataType::Byte;
976 }
977 else if ( t == QLatin1String( "16BUI" ) )
978 {
979 type = Qgis::DataType::UInt16;
980 }
981 else if ( t == QLatin1String( "16BSI" ) )
982 {
983 type = Qgis::DataType::Int16;
984 }
985 else if ( t == QLatin1String( "32BSI" ) )
986 {
987 type = Qgis::DataType::Int32;
988 }
989 else if ( t == QLatin1String( "32BUI" ) )
990 {
991 type = Qgis::DataType::UInt32;
992 }
993 else if ( t == QLatin1String( "32BF" ) )
994 {
995 type = Qgis::DataType::Float32;
996 }
997 else if ( t == QLatin1String( "64BF" ) )
998 {
999 type = Qgis::DataType::Float64;
1000 }
1001 return type;
1002 };
1003
1004 // ///////////////////////////////////////////////////////////////////
1005 // First method: get information from metadata
1006 if ( ! mIsQuery && mUseEstimatedMetadata && subsetString().isEmpty() )
1007 {
1008 try
1009 {
1010 const QString sql = QStringLiteral( "SELECT r_raster_column, srid,"
1011 "num_bands, pixel_types, nodata_values, ST_AsBinary(extent), blocksize_x, blocksize_y,"
1012 "out_db, spatial_index, scale_x, scale_y, same_alignment,"
1013 "regular_blocking "
1014 "FROM raster_columns WHERE "
1015 "r_table_name = %1 AND r_table_schema = %2" )
1016 .arg( quotedValue( mTableName ), quotedValue( mSchemaName ) );
1017
1018 QgsPostgresResult result( connectionRO()->PQexec( sql ) );
1019
1020 if ( ( PGRES_TUPLES_OK == result.PQresultStatus() ) && ( result.PQntuples() > 0 ) )
1021 {
1022 mRasterColumn = result.PQgetvalue( 0, 0 );
1023 mHasSpatialIndex = result.PQgetvalue( 0, 9 ) == 't';
1024 bool ok;
1025
1026 mCrs = QgsCoordinateReferenceSystem();
1027 // FIXME: from Nyall's comment:
1028 // keep in mind that postgis crs handling is rather broken in QGIS and needs to be rethought for 4.0
1029 // (we should be retrieving the definition from the server corresponding to the reported postgis srs,
1030 // and not using any qgis internal databases for this ... which may or may not have any match to the
1031 // server's definitions.)
1032 Q_NOWARN_DEPRECATED_PUSH
1033 mCrs.createFromSrid( result.PQgetvalue( 0, 1 ).toLong( &ok ) );
1034 Q_NOWARN_DEPRECATED_PUSH
1035
1036 if ( ! ok )
1037 {
1038 throw QgsPostgresRasterProviderException( tr( "Cannot create CRS from EPSG: '%1'" ).arg( result.PQgetvalue( 0, 1 ) ) );
1039 }
1040
1041 mDetectedSrid = result.PQgetvalue( 0, 1 );
1042 mBandCount = result.PQgetvalue( 0, 2 ).toInt( &ok );
1043
1044 if ( ! ok )
1045 {
1046 throw QgsPostgresRasterProviderException( tr( "Cannot get band count from value: '%1'" ).arg( result.PQgetvalue( 0, 2 ) ) );
1047 }
1048
1049 QString pxTypesArray { result.PQgetvalue( 0, 3 ) };
1050 pxTypesArray.chop( 1 );
1051 const QStringList pxTypes { pxTypesArray.mid( 1 ).split( ',' ) };
1052
1053 QString noDataValuesArray { result.PQgetvalue( 0, 4 ) };
1054 noDataValuesArray.chop( 1 );
1055 const QStringList noDataValues { noDataValuesArray.mid( 1 ).split( ',' ) };
1056
1057 if ( mBandCount != pxTypes.count( ) || mBandCount != noDataValues.count() )
1058 {
1059 throw QgsPostgresRasterProviderException( tr( "Band count and nodata items count differs" ) );
1060 }
1061
1062 int i = 0;
1063 for ( const QString &t : std::as_const( pxTypes ) )
1064 {
1065 Qgis::DataType type { pixelTypeFromString( t ) };
1066 if ( type == Qgis::DataType::UnknownDataType )
1067 {
1068 throw QgsPostgresRasterProviderException( tr( "Unsupported data type: '%1'" ).arg( t ) );
1069 }
1070 mDataTypes.push_back( type );
1071 mDataSizes.push_back( QgsRasterBlock::typeSize( type ) );
1072 double nodataValue { noDataValues.at( i ).toDouble( &ok ) };
1073 if ( ! ok )
1074 {
1075 if ( noDataValues.at( i ) != QLatin1String( "NULL" ) )
1076 {
1077 QgsMessageLog::logMessage( tr( "Cannot convert nodata value '%1' to double" )
1078 .arg( noDataValues.at( i ) ),
1079 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Info );
1080 }
1081 mSrcHasNoDataValue.append( false );
1082 mUseSrcNoDataValue.append( false );
1083 nodataValue = std::numeric_limits<double>::min();
1084 }
1085 else
1086 {
1087 mSrcHasNoDataValue.append( true );
1088 mUseSrcNoDataValue.append( true );
1089 }
1090 mSrcNoDataValue.append( QgsRaster::representableValue( nodataValue, type ) );
1091 ++i;
1092 }
1093
1094 // Extent
1095 QgsPolygon p;
1096 // Strip \x
1097 const QByteArray hexAscii { result.PQgetvalue( 0, 5 ).toLatin1().mid( 2 ) };
1098 const QByteArray hexBin = QByteArray::fromHex( hexAscii );
1099 QgsConstWkbPtr ptr { hexBin };
1100
1101 if ( hexAscii.isEmpty() || ! p.fromWkb( ptr ) )
1102 {
1103 // Try to determine extent from raster
1104 const QString extentSql = QStringLiteral( "SELECT ST_Envelope( %1 ) "
1105 "FROM %2 WHERE %3" )
1106 .arg( quotedIdentifier( mRasterColumn ),
1107 mQuery,
1108 subsetString().isEmpty() ? "'t'" : subsetString() );
1109
1110 QgsPostgresResult extentResult( connectionRO()->PQexec( extentSql ) );
1111 const QByteArray extentHexAscii { extentResult.PQgetvalue( 0, 0 ).toLatin1() };
1112 const QByteArray extentHexBin = QByteArray::fromHex( extentHexAscii );
1113 QgsConstWkbPtr extentPtr { extentHexBin };
1114 if ( extentHexAscii.isEmpty() || ! p.fromWkb( extentPtr ) )
1115 {
1116 throw QgsPostgresRasterProviderException( tr( "Cannot get extent from raster" ) );
1117 }
1118 }
1119
1120 mExtent = p.boundingBox();
1121
1122 // Tile size
1123 mTileWidth = result.PQgetvalue( 0, 6 ).toInt( &ok );
1124
1125 if ( ! ok )
1126 {
1127 throw QgsPostgresRasterProviderException( tr( "Cannot convert width '%1' to int" ).arg( result.PQgetvalue( 0, 6 ) ) );
1128 }
1129
1130 mTileHeight = result.PQgetvalue( 0, 7 ).toInt( &ok );
1131
1132 if ( ! ok )
1133 {
1134 throw QgsPostgresRasterProviderException( tr( "Cannot convert height '%1' to int" ).arg( result.PQgetvalue( 0, 7 ) ) );
1135 }
1136
1137 mIsOutOfDb = result.PQgetvalue( 0, 8 ) == 't';
1138 mScaleX = result.PQgetvalue( 0, 10 ).toDouble( &ok );
1139
1140 if ( ! ok )
1141 {
1142 throw QgsPostgresRasterProviderException( tr( "Cannot convert scale X '%1' to double" ).arg( result.PQgetvalue( 0, 10 ) ) );
1143 }
1144
1145 mScaleY = result.PQgetvalue( 0, 11 ).toDouble( &ok );
1146
1147 if ( ! ok )
1148 {
1149 throw QgsPostgresRasterProviderException( tr( "Cannot convert scale Y '%1' to double" ).arg( result.PQgetvalue( 0, 11 ) ) );
1150 }
1151
1152 // Compute raster size
1153 mHeight = static_cast<long>( std::round( mExtent.height() / std::abs( mScaleY ) ) );
1154 mWidth = static_cast<long>( std::round( mExtent.width() / std::abs( mScaleX ) ) );
1155 // is tiled?
1156 mIsTiled = ( mWidth != mTileWidth ) || ( mHeight != mTileHeight );
1157
1158 // Detect overviews
1159 findOverviews();
1160 return initFieldsAndTemporal( );
1161 }
1162 else
1163 {
1164 QgsMessageLog::logMessage( tr( "An error occurred while fetching raster metadata for table %1: %2\nSQL: %3" )
1165 .arg( mQuery )
1166 .arg( result.PQresultErrorMessage() )
1167 .arg( sql ),
1168 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
1169 }
1170 }
1171 catch ( QgsPostgresRasterProviderException &ex )
1172 {
1173 QgsMessageLog::logMessage( tr( "An error occurred while fetching raster metadata for %1, proceeding with (possibly very slow) raster data analysis: %2\n"
1174 "Please consider adding raster constraints with PostGIS function AddRasterConstraints." )
1175 .arg( mQuery )
1176 .arg( ex.message ),
1177 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
1178 }
1179 }
1180
1181 // TODO: query layers + mHasSpatialIndex in case metadata are not used
1182
1183 // ///////////////////////////////////////////////////////////////////
1184 // Go the hard and slow way: fetch information directly from the layer
1185 //
1186 if ( mRasterColumn.isEmpty() )
1187 {
1188 const QString sql = QStringLiteral( "SELECT column_name FROM information_schema.columns WHERE "
1189 "table_name = %1 AND table_schema = %2 AND udt_name = 'raster'" )
1190 .arg( quotedValue( mTableName ), quotedValue( mSchemaName ) );
1191
1192 QgsPostgresResult result( connectionRO()->PQexec( sql ) );
1193
1194 if ( PGRES_TUPLES_OK == result.PQresultStatus() && result.PQntuples() > 0 )
1195 {
1196 if ( result.PQntuples() > 1 )
1197 {
1198 QgsMessageLog::logMessage( tr( "Multiple raster column detected, using the first one" ),
1199 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
1200
1201 }
1202 mRasterColumn = result.PQgetvalue( 0, 0 );
1203 }
1204 else
1205 {
1206 QgsMessageLog::logMessage( tr( "An error occurred while fetching raster column" ),
1207 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Critical );
1208 return false;
1209 }
1210 }
1211
1212 // Get the full raster and extract information
1213 // Note: this can be very slow
1214 // Use oveviews if we can, even if they are probably missing for unconstrained tables
1215
1216 findOverviews();
1217
1218 QString tableToQuery { mQuery };
1219
1220 if ( ! mOverViews.isEmpty() )
1221 {
1222 tableToQuery = mOverViews.last();
1223 }
1224
1225 QString where;
1226 if ( ! subsetString().isEmpty() )
1227 {
1228 where = QStringLiteral( "WHERE %1" ).arg( subsetString() );
1229 }
1230
1231 // Unfortunately we cannot safely assume that the raster is untiled and just LIMIT 1
1232 // Fastest SQL: fetch all metadata in one pass
1233 // 0 1 3 3 4 5 6 7 8 9 10 11 12 13 14
1234 // encode | upperleftx | upperlefty | width | height | scalex | scaley | skewx | skewy | srid | numbands | pixeltype | nodatavalue | isoutdb | path
1235 const QString sql = QStringLiteral( R"(
1236 WITH cte_filtered_raster AS ( SELECT %1 AS filtered_rast FROM %2 %3 ),
1237 cte_rast AS ( SELECT ST_Union( cte_filtered_raster.filtered_rast ) AS united_raster FROM cte_filtered_raster ),
1238 cte_bandno AS ( SELECT * FROM generate_series(1, ST_NumBands ( ( SELECT cte_rast.united_raster FROM cte_rast ) ) ) AS bandno ),
1239 cte_band AS ( SELECT ST_Band( united_raster, bandno ) AS band FROM cte_rast, cte_bandno )
1240 SELECT ENCODE( ST_AsBinary( ST_Envelope( band ) ), 'hex'),
1241 (ST_Metadata( band )).*,
1242 (ST_BandMetadata( band )).*
1243 FROM cte_band)" ).arg( quotedIdentifier( mRasterColumn ), tableToQuery, where );
1244
1245 QgsDebugMsgLevel( QStringLiteral( "Raster information sql: %1" ).arg( sql ), 4 );
1246
1247 QgsPostgresResult result( connectionRO()->PQexec( sql ) );
1248 if ( PGRES_TUPLES_OK == result.PQresultStatus() && result.PQntuples() > 0 )
1249 {
1250
1251 // These may have been filled with defaults in the fast track
1252 mSrcNoDataValue.clear();
1253 mSrcHasNoDataValue.clear();
1254 mUseSrcNoDataValue.clear();
1255 mBandCount = result.PQntuples();
1256
1257 bool ok;
1258
1259 // Extent
1260 QgsPolygon p;
1261 try
1262 {
1263 const QByteArray hexBin = QByteArray::fromHex( result.PQgetvalue( 0, 0 ).toLatin1() );
1264 QgsConstWkbPtr ptr { hexBin };
1265 if ( ! p.fromWkb( ptr ) )
1266 {
1267 QgsMessageLog::logMessage( tr( "Cannot get extent from raster" ),
1268 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Critical );
1269 return false;
1270 }
1271 }
1272 catch ( ... )
1273 {
1274 QgsMessageLog::logMessage( tr( "Cannot get metadata from raster" ),
1275 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Critical );
1276 return false;
1277 }
1278
1279 mExtent = p.boundingBox();
1280
1281 // Tile size (in this path the raster is considered untiled, so this is actually the whole size
1282 mTileWidth = result.PQgetvalue( 0, 3 ).toInt( &ok );
1283
1284 if ( ! ok )
1285 {
1286 QgsMessageLog::logMessage( tr( "Cannot convert width '%1' to int" ).arg( result.PQgetvalue( 0, 3 ) ),
1287 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Critical );
1288 return false;
1289 }
1290
1291 mTileHeight = result.PQgetvalue( 0, 4 ).toInt( &ok );
1292
1293 if ( ! ok )
1294 {
1295 QgsMessageLog::logMessage( tr( "Cannot convert height '%1' to int" ).arg( result.PQgetvalue( 0, 4 ) ),
1296 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Critical );
1297 return false;
1298 }
1299
1300 mScaleX = result.PQgetvalue( 0, 5 ).toDouble( &ok );
1301
1302 if ( ! ok )
1303 {
1304 QgsMessageLog::logMessage( tr( "Cannot convert scale X '%1' to double" ).arg( result.PQgetvalue( 0, 5 ) ),
1305 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Critical );
1306 return false;
1307 }
1308
1309 mScaleY = result.PQgetvalue( 0, 6 ).toDouble( &ok );
1310
1311 if ( ! ok )
1312 {
1313 QgsMessageLog::logMessage( tr( "Cannot convert scale Y '%1' to double" ).arg( result.PQgetvalue( 0, 6 ) ),
1314 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Critical );
1315 return false;
1316 }
1317
1318 // Compute raster size, it is untiled so just take tile dimensions
1319 mHeight = mTileHeight;
1320 mWidth = mTileWidth;
1321 mIsTiled = false;
1322
1323 mCrs = QgsCoordinateReferenceSystem();
1324 // FIXME: from Nyall's comment:
1325 // keep in mind that postgis crs handling is rather broken in QGIS and needs to be rethought for 4.0
1326 // (we should be retrieving the definition from the server corresponding to the reported postgis srs,
1327 // and not using any qgis internal databases for this ... which may or may not have any match to the
1328 // server's definitions.)
1329 Q_NOWARN_DEPRECATED_PUSH
1330 mCrs.createFromSrid( result.PQgetvalue( 0, 9 ).toLong( &ok ) );
1331 Q_NOWARN_DEPRECATED_PUSH
1332
1333 if ( ! ok )
1334 {
1335 QgsMessageLog::logMessage( tr( "Cannot create CRS from EPSG: '%1'" ).arg( result.PQgetvalue( 0, 9 ) ),
1336 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Critical );
1337 return false;
1338 }
1339
1340 mDetectedSrid = result.PQgetvalue( 0, 9 );
1341
1342 // Fetch band data types
1343 for ( int rowNumber = 0; rowNumber < result.PQntuples(); ++rowNumber )
1344 {
1345 Qgis::DataType type { pixelTypeFromString( result.PQgetvalue( rowNumber, 11 ) ) };
1346
1347 if ( type == Qgis::DataType::UnknownDataType )
1348 {
1349 QgsMessageLog::logMessage( tr( "Unsupported data type: '%1'" ).arg( result.PQgetvalue( rowNumber, 11 ) ),
1350 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Critical );
1351 return false;
1352 }
1353
1354 mDataTypes.push_back( type );
1355 mDataSizes.push_back( QgsRasterBlock::typeSize( type ) );
1356 double nodataValue { result.PQgetvalue( rowNumber, 12 ).toDouble( &ok ) };
1357
1358 if ( ! ok )
1359 {
1360 QgsMessageLog::logMessage( tr( "Cannot convert nodata value '%1' to double, default to: %2" )
1361 .arg( result.PQgetvalue( rowNumber, 2 ) )
1362 .arg( std::numeric_limits<double>::min() ), QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Info );
1363 nodataValue = std::numeric_limits<double>::min();
1364 }
1365
1366 mSrcNoDataValue.append( QgsRaster::representableValue( nodataValue, type ) );
1367 mSrcHasNoDataValue.append( true );
1368 mUseSrcNoDataValue.append( true );
1369 }
1370 mIsOutOfDb = result.PQgetvalue( 0, 13 ) == 't';
1371 }
1372 else
1373 {
1374 QgsMessageLog::logMessage( tr( "An error occurred while fetching raster metadata" ),
1375 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Critical );
1376 return false;
1377 }
1378
1379 return initFieldsAndTemporal( );
1380 }
1381
initFieldsAndTemporal()1382 bool QgsPostgresRasterProvider::initFieldsAndTemporal( )
1383 {
1384 // Populate fields
1385 if ( ! loadFields() )
1386 {
1387 QgsMessageLog::logMessage( tr( "An error occurred while fetching raster fields information" ),
1388 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Critical );
1389 return false;
1390 }
1391
1392 QString where;
1393 if ( ! subsetString().isEmpty() )
1394 {
1395 where = QStringLiteral( "WHERE %1" ).arg( subsetString() );
1396 }
1397
1398 // Temporal capabilities
1399 // Setup temporal properties for layer, do not fail if something goes wrong but log a warning
1400 if ( mUri.hasParam( QStringLiteral( "temporalFieldIndex" ) ) )
1401 {
1402 bool ok;
1403 const int temporalFieldIndex { mUri.param( QStringLiteral( "temporalFieldIndex" ) ).toInt( &ok ) };
1404 if ( ok && mAttributeFields.exists( temporalFieldIndex ) )
1405 {
1406 const QString temporalFieldName { mAttributeFields.field( temporalFieldIndex ).name() };
1407 // Calculate the range
1408 const QString sql = QStringLiteral( "SELECT MIN(%1::timestamp), MAX(%1::timestamp) "
1409 "FROM %2 %3" ).arg( quotedIdentifier( temporalFieldName ),
1410 mQuery,
1411 where );
1412
1413 QgsPostgresResult result( connectionRO()->PQexec( sql ) );
1414
1415 if ( PGRES_TUPLES_OK == result.PQresultStatus() && result.PQntuples() == 1 )
1416 {
1417 const QDateTime minTime { QDateTime::fromString( result.PQgetvalue( 0, 0 ), Qt::DateFormat::ISODate ) };
1418 const QDateTime maxTime { QDateTime::fromString( result.PQgetvalue( 0, 1 ), Qt::DateFormat::ISODate ) };
1419 if ( minTime.isValid() && maxTime.isValid() && !( minTime > maxTime ) )
1420 {
1421 mTemporalFieldIndex = temporalFieldIndex;
1422 temporalCapabilities()->setHasTemporalCapabilities( true );
1423 temporalCapabilities()->setAvailableTemporalRange( { minTime, maxTime } );
1424 temporalCapabilities()->setIntervalHandlingMethod( Qgis::TemporalIntervalMatchMethod::FindClosestMatchToStartOfRange );
1425 QgsDebugMsgLevel( QStringLiteral( "Raster temporal range for field %1: %2 - %3" ).arg( QString::number( mTemporalFieldIndex ), minTime.toString(), maxTime.toString() ), 3 );
1426
1427 if ( mUri.hasParam( QStringLiteral( "temporalDefaultTime" ) ) )
1428 {
1429 const QDateTime defaultDateTime { QDateTime::fromString( mUri.param( QStringLiteral( "temporalDefaultTime" ) ), Qt::DateFormat::ISODate ) };
1430 if ( defaultDateTime.isValid() )
1431 {
1432 mTemporalDefaultTime = defaultDateTime;
1433 }
1434 else
1435 {
1436 QgsMessageLog::logMessage( tr( "Invalid default date in raster temporal capabilities for field %1: %2" ).arg( temporalFieldName, mUri.param( QStringLiteral( "temporalDefaultTime" ) ) ),
1437 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
1438 }
1439 }
1440
1441 // Set temporal ranges
1442 QList< QgsDateTimeRange > allRanges;
1443 const QString sql = QStringLiteral( "SELECT DISTINCT %1::timestamp "
1444 "FROM %2 %3 ORDER BY %1::timestamp" ).arg( quotedIdentifier( temporalFieldName ),
1445 mQuery,
1446 where );
1447
1448 QgsPostgresResult result( connectionRO()->PQexec( sql ) );
1449 if ( PGRES_TUPLES_OK == result.PQresultStatus() && result.PQntuples() > 0 )
1450 {
1451 for ( qlonglong row = 0; row < result.PQntuples(); ++row )
1452 {
1453 const QDateTime date = QDateTime::fromString( result.PQgetvalue( row, 0 ), Qt::DateFormat::ISODate );
1454 allRanges.push_back( QgsDateTimeRange( date, date ) );
1455 }
1456 temporalCapabilities()->setAllAvailableTemporalRanges( allRanges );
1457 }
1458 else
1459 {
1460 QgsMessageLog::logMessage( tr( "No temporal ranges detected in raster temporal capabilities for field %1: %2" ).arg( temporalFieldName, mUri.param( QStringLiteral( "temporalDefaultTime" ) ) ),
1461 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Info );
1462 }
1463 }
1464 else
1465 {
1466 QgsMessageLog::logMessage( tr( "Invalid temporal range in raster temporal capabilities for field %1: %2 - %3" ).arg( temporalFieldName, minTime.toString(), maxTime.toString() ),
1467 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
1468 }
1469 }
1470 else
1471 {
1472 QgsMessageLog::logMessage( tr( "An error occurred while fetching raster temporal capabilities for field: %1" ).arg( temporalFieldName ),
1473 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
1474
1475 }
1476 }
1477 else
1478 {
1479 QgsMessageLog::logMessage( tr( "Invalid field index for raster temporal capabilities: %1" )
1480 .arg( QString::number( temporalFieldIndex ) ),
1481 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
1482 }
1483 }
1484 return true;
1485 }
1486
loadFields()1487 bool QgsPostgresRasterProvider::loadFields()
1488 {
1489
1490 if ( !mIsQuery )
1491 {
1492 QgsDebugMsgLevel( QStringLiteral( "Loading fields for table %1" ).arg( mTableName ), 2 );
1493
1494 // Get the relation oid for use in later queries
1495 QString sql = QStringLiteral( "SELECT regclass(%1)::oid" ).arg( quotedValue( mQuery ) );
1496 QgsPostgresResult tresult( connectionRO()->PQexec( sql ) );
1497 QString tableoid = tresult.PQgetvalue( 0, 0 );
1498
1499 // Get the table description
1500 sql = QStringLiteral( "SELECT description FROM pg_description WHERE objoid=%1 AND objsubid=0" ).arg( tableoid );
1501 tresult = connectionRO()->PQexec( sql );
1502 if ( tresult.PQntuples() > 0 )
1503 {
1504 mDataComment = tresult.PQgetvalue( 0, 0 );
1505 mLayerMetadata.setAbstract( mDataComment );
1506 }
1507 }
1508 else
1509 {
1510 // Not supported for now
1511 return true;
1512 }
1513
1514 // Populate the field vector for this layer. The field vector contains
1515 // field name, type, length, and precision (if numeric)
1516 QString sql = QStringLiteral( "SELECT * FROM %1 LIMIT 0" ).arg( mQuery );
1517
1518 QgsPostgresResult result( connectionRO()->PQexec( sql ) );
1519
1520 // Collect type info
1521 sql = QStringLiteral( "SELECT oid,typname,typtype,typelem,typlen FROM pg_type" );
1522 QgsPostgresResult typeResult( connectionRO()->PQexec( sql ) );
1523
1524 QMap<Oid, PGTypeInfo> typeMap;
1525 for ( int i = 0; i < typeResult.PQntuples(); ++i )
1526 {
1527 PGTypeInfo typeInfo =
1528 {
1529 /* typeName = */ typeResult.PQgetvalue( i, 1 ),
1530 /* typeType = */ typeResult.PQgetvalue( i, 2 ),
1531 /* typeElem = */ typeResult.PQgetvalue( i, 3 ),
1532 /* typeLen = */ typeResult.PQgetvalue( i, 4 ).toInt()
1533 };
1534 typeMap.insert( typeResult.PQgetvalue( i, 0 ).toUInt(), typeInfo );
1535 }
1536
1537
1538 QMap<Oid, QMap<int, QString> > fmtFieldTypeMap, descrMap, defValMap, identityMap;
1539 QMap<Oid, QMap<int, Oid> > attTypeIdMap;
1540 QMap<Oid, QMap<int, bool> > notNullMap, uniqueMap;
1541 if ( result.PQnfields() > 0 )
1542 {
1543 // Collect table oids
1544 QSet<Oid> tableoids;
1545 for ( int i = 0; i < result.PQnfields(); i++ )
1546 {
1547 Oid tableoid = result.PQftable( i );
1548 if ( tableoid > 0 )
1549 {
1550 tableoids.insert( tableoid );
1551 }
1552 }
1553
1554 if ( !tableoids.isEmpty() )
1555 {
1556 QStringList tableoidsList;
1557 const auto constTableoids = tableoids;
1558 for ( Oid tableoid : constTableoids )
1559 {
1560 tableoidsList.append( QString::number( tableoid ) );
1561 }
1562
1563 QString tableoidsFilter = '(' + tableoidsList.join( QLatin1Char( ',' ) ) + ')';
1564
1565 // Collect formatted field types
1566 sql = QStringLiteral(
1567 "SELECT attrelid, attnum, pg_catalog.format_type(atttypid,atttypmod), pg_catalog.col_description(attrelid,attnum), pg_catalog.pg_get_expr(adbin,adrelid), atttypid, attnotnull::int, indisunique::int%1"
1568 " FROM pg_attribute"
1569 " LEFT OUTER JOIN pg_attrdef ON attrelid=adrelid AND attnum=adnum"
1570
1571 // find unique constraints if present. Text cast required to handle int2vector comparison. Distinct required as multiple unique constraints may exist
1572 " LEFT OUTER JOIN ( SELECT DISTINCT indrelid, indkey, indisunique FROM pg_index WHERE indisunique ) uniq ON attrelid=indrelid AND attnum::text=indkey::text "
1573
1574 " WHERE attrelid IN %2"
1575 ).arg( connectionRO()->pgVersion() >= 100000 ? QStringLiteral( ", attidentity" ) : QString() ).arg( tableoidsFilter );
1576
1577 QgsPostgresResult fmtFieldTypeResult( connectionRO()->PQexec( sql ) );
1578 for ( int i = 0; i < fmtFieldTypeResult.PQntuples(); ++i )
1579 {
1580 Oid attrelid = fmtFieldTypeResult.PQgetvalue( i, 0 ).toUInt();
1581 int attnum = fmtFieldTypeResult.PQgetvalue( i, 1 ).toInt(); // Int2
1582 QString formatType = fmtFieldTypeResult.PQgetvalue( i, 2 );
1583 QString descr = fmtFieldTypeResult.PQgetvalue( i, 3 );
1584 QString defVal = fmtFieldTypeResult.PQgetvalue( i, 4 );
1585 Oid attType = fmtFieldTypeResult.PQgetvalue( i, 5 ).toUInt();
1586 bool attNotNull = fmtFieldTypeResult.PQgetvalue( i, 6 ).toInt();
1587 bool uniqueConstraint = fmtFieldTypeResult.PQgetvalue( i, 7 ).toInt();
1588 QString attIdentity = connectionRO()->pgVersion() >= 100000 ? fmtFieldTypeResult.PQgetvalue( i, 8 ) : " ";
1589 fmtFieldTypeMap[attrelid][attnum] = formatType;
1590 descrMap[attrelid][attnum] = descr;
1591 defValMap[attrelid][attnum] = defVal;
1592 attTypeIdMap[attrelid][attnum] = attType;
1593 notNullMap[attrelid][attnum] = attNotNull;
1594 uniqueMap[attrelid][attnum] = uniqueConstraint;
1595 identityMap[attrelid][attnum] = attIdentity.isEmpty() ? " " : attIdentity;
1596 }
1597 }
1598 }
1599
1600 QSet<QString> fields;
1601 mAttributeFields.clear();
1602 mIdentityFields.clear();
1603 for ( int i = 0; i < result.PQnfields(); i++ )
1604 {
1605 QString fieldName = result.PQfname( i );
1606 if ( fieldName == mRasterColumn )
1607 continue;
1608
1609 Oid fldtyp = result.PQftype( i );
1610 int fldMod = result.PQfmod( i );
1611 int fieldPrec = 0;
1612 Oid tableoid = result.PQftable( i );
1613 int attnum = result.PQftablecol( i );
1614 Oid atttypid = attTypeIdMap[tableoid][attnum];
1615
1616 const PGTypeInfo &typeInfo = typeMap.value( fldtyp );
1617 QString fieldTypeName = typeInfo.typeName;
1618 QString fieldTType = typeInfo.typeType;
1619 int fieldSize = typeInfo.typeLen;
1620
1621 bool isDomain = ( typeMap.value( atttypid ).typeType == QLatin1String( "d" ) );
1622
1623 QString formattedFieldType = fmtFieldTypeMap[tableoid][attnum];
1624 QString originalFormattedFieldType = formattedFieldType;
1625 if ( isDomain )
1626 {
1627 // get correct formatted field type for domain
1628 sql = QStringLiteral( "SELECT format_type(%1, %2)" ).arg( fldtyp ).arg( fldMod );
1629 QgsPostgresResult fmtFieldModResult( connectionRO()->PQexec( sql ) );
1630 if ( fmtFieldModResult.PQntuples() > 0 )
1631 {
1632 formattedFieldType = fmtFieldModResult.PQgetvalue( 0, 0 );
1633 }
1634 }
1635
1636 QString fieldComment = descrMap[tableoid][attnum];
1637
1638 QVariant::Type fieldType;
1639 QVariant::Type fieldSubType = QVariant::Invalid;
1640
1641 if ( fieldTType == QLatin1String( "b" ) )
1642 {
1643 bool isArray = fieldTypeName.startsWith( '_' );
1644
1645 if ( isArray )
1646 fieldTypeName = fieldTypeName.mid( 1 );
1647
1648 if ( fieldTypeName == QLatin1String( "int8" ) || fieldTypeName == QLatin1String( "serial8" ) )
1649 {
1650 fieldType = QVariant::LongLong;
1651 fieldSize = -1;
1652 fieldPrec = 0;
1653 }
1654 else if ( fieldTypeName == QLatin1String( "int2" ) || fieldTypeName == QLatin1String( "int4" ) ||
1655 fieldTypeName == QLatin1String( "oid" ) || fieldTypeName == QLatin1String( "serial" ) )
1656 {
1657 fieldType = QVariant::Int;
1658 fieldSize = -1;
1659 fieldPrec = 0;
1660 }
1661 else if ( fieldTypeName == QLatin1String( "real" ) || fieldTypeName == QLatin1String( "double precision" ) ||
1662 fieldTypeName == QLatin1String( "float4" ) || fieldTypeName == QLatin1String( "float8" ) )
1663 {
1664 fieldType = QVariant::Double;
1665 fieldSize = -1;
1666 fieldPrec = 0;
1667 }
1668 else if ( fieldTypeName == QLatin1String( "numeric" ) )
1669 {
1670 fieldType = QVariant::Double;
1671
1672 if ( formattedFieldType == QLatin1String( "numeric" ) || formattedFieldType.isEmpty() )
1673 {
1674 fieldSize = -1;
1675 fieldPrec = 0;
1676 }
1677 else
1678 {
1679 QRegularExpression re( QRegularExpression::anchoredPattern( QStringLiteral( "numeric\\((\\d+),(\\d+)\\)" ) ) );
1680 const QRegularExpressionMatch match = re.match( formattedFieldType );
1681 if ( match.hasMatch() )
1682 {
1683 fieldSize = match.captured( 1 ).toInt();
1684 fieldPrec = match.captured( 2 ).toInt();
1685 }
1686 else if ( formattedFieldType != QLatin1String( "numeric" ) )
1687 {
1688 QgsMessageLog::logMessage( tr( "Unexpected formatted field type '%1' for field %2" )
1689 .arg( formattedFieldType,
1690 fieldName ),
1691 tr( "PostGIS" ) );
1692 fieldSize = -1;
1693 fieldPrec = 0;
1694 }
1695 }
1696 }
1697 else if ( fieldTypeName == QLatin1String( "varchar" ) )
1698 {
1699 fieldType = QVariant::String;
1700
1701 const QRegularExpression re( QRegularExpression::anchoredPattern( QStringLiteral( "character varying\\((\\d+)\\)" ) ) );
1702 const QRegularExpressionMatch match = re.match( formattedFieldType );
1703 if ( match.hasMatch() )
1704 {
1705 fieldSize = match.captured( 1 ).toInt();
1706 }
1707 else
1708 {
1709 fieldSize = -1;
1710 }
1711 }
1712 else if ( fieldTypeName == QLatin1String( "date" ) )
1713 {
1714 fieldType = QVariant::Date;
1715 fieldSize = -1;
1716 }
1717 else if ( fieldTypeName == QLatin1String( "time" ) )
1718 {
1719 fieldType = QVariant::Time;
1720 fieldSize = -1;
1721 }
1722 else if ( fieldTypeName == QLatin1String( "timestamp" ) )
1723 {
1724 fieldType = QVariant::DateTime;
1725 fieldSize = -1;
1726 }
1727 else if ( fieldTypeName == QLatin1String( "bytea" ) )
1728 {
1729 fieldType = QVariant::ByteArray;
1730 fieldSize = -1;
1731 }
1732 else if ( fieldTypeName == QLatin1String( "text" ) ||
1733 fieldTypeName == QLatin1String( "citext" ) ||
1734 fieldTypeName == QLatin1String( "geometry" ) ||
1735 fieldTypeName == QLatin1String( "inet" ) ||
1736 fieldTypeName == QLatin1String( "money" ) ||
1737 fieldTypeName == QLatin1String( "ltree" ) ||
1738 fieldTypeName == QLatin1String( "uuid" ) ||
1739 fieldTypeName == QLatin1String( "xml" ) ||
1740 fieldTypeName.startsWith( QLatin1String( "time" ) ) ||
1741 fieldTypeName.startsWith( QLatin1String( "date" ) ) )
1742 {
1743 fieldType = QVariant::String;
1744 fieldSize = -1;
1745 }
1746 else if ( fieldTypeName == QLatin1String( "bpchar" ) )
1747 {
1748 // although postgres internally uses "bpchar", this is exposed to users as character in postgres
1749 fieldTypeName = QStringLiteral( "character" );
1750
1751 fieldType = QVariant::String;
1752
1753 const QRegularExpression re( QRegularExpression::anchoredPattern( QStringLiteral( "character\\((\\d+)\\)" ) ) );
1754 const QRegularExpressionMatch match = re.match( formattedFieldType );
1755 if ( match.hasMatch() )
1756 {
1757 fieldSize = match.captured( 1 ).toInt();
1758 }
1759 else
1760 {
1761 QgsDebugMsg( QStringLiteral( "Unexpected formatted field type '%1' for field %2" )
1762 .arg( formattedFieldType,
1763 fieldName ) );
1764 fieldSize = -1;
1765 fieldPrec = 0;
1766 }
1767 }
1768 else if ( fieldTypeName == QLatin1String( "char" ) )
1769 {
1770 fieldType = QVariant::String;
1771
1772 const QRegularExpression re( QRegularExpression::anchoredPattern( QStringLiteral( "char\\((\\d+)\\)" ) ) );
1773 const QRegularExpressionMatch match = re.match( formattedFieldType );
1774 if ( match.hasMatch() )
1775 {
1776 fieldSize = match.captured( 1 ).toInt();
1777 }
1778 else
1779 {
1780 QgsMessageLog::logMessage( tr( "Unexpected formatted field type '%1' for field %2" )
1781 .arg( formattedFieldType,
1782 fieldName ) );
1783 fieldSize = -1;
1784 fieldPrec = 0;
1785 }
1786 }
1787 else if ( fieldTypeName == QLatin1String( "hstore" ) || fieldTypeName == QLatin1String( "json" ) || fieldTypeName == QLatin1String( "jsonb" ) )
1788 {
1789 fieldType = QVariant::Map;
1790 fieldSubType = QVariant::String;
1791 fieldSize = -1;
1792 }
1793 else if ( fieldTypeName == QLatin1String( "bool" ) )
1794 {
1795 // enum
1796 fieldType = QVariant::Bool;
1797 fieldSize = -1;
1798 }
1799 else
1800 {
1801 // be tolerant in case of views: this might be a field used as a key
1802 const QgsPostgresProvider::Relkind type = relkind();
1803 if ( ( type == QgsPostgresProvider::Relkind::View || type == QgsPostgresProvider::Relkind::MaterializedView )
1804 && parseUriKey( mUri.keyColumn( ) ).contains( fieldName ) )
1805 {
1806 // Assume it is convertible to text
1807 fieldType = QVariant::String;
1808 fieldSize = -1;
1809 }
1810 else
1811 {
1812 QgsMessageLog::logMessage( tr( "Field %1 ignored, because of unsupported type %2" ).arg( fieldName, fieldTType ), tr( "PostGIS" ) );
1813 continue;
1814 }
1815 }
1816
1817 if ( isArray )
1818 {
1819 fieldTypeName = '_' + fieldTypeName;
1820 fieldSubType = fieldType;
1821 fieldType = ( fieldType == QVariant::String ? QVariant::StringList : QVariant::List );
1822 fieldSize = -1;
1823 }
1824 }
1825 else if ( fieldTType == QLatin1String( "e" ) )
1826 {
1827 // enum
1828 fieldType = QVariant::String;
1829 fieldSize = -1;
1830 }
1831 else
1832 {
1833 QgsMessageLog::logMessage( tr( "Field %1 ignored, because of unsupported type %2" ).arg( fieldName, fieldTType ), tr( "PostGIS" ) );
1834 continue;
1835 }
1836
1837 if ( fields.contains( fieldName ) )
1838 {
1839 QgsMessageLog::logMessage( tr( "Duplicate field %1 found\n" ).arg( fieldName ), tr( "PostGIS" ) );
1840 return false;
1841 }
1842
1843 fields << fieldName;
1844
1845 if ( isDomain )
1846 {
1847 //field was defined using domain, so use domain type name for fieldTypeName
1848 fieldTypeName = originalFormattedFieldType;
1849 }
1850
1851 // If this is an identity field with constraints and there is no default, let's look for a sequence:
1852 // we might have a default value created by a sequence named <table>_<field>_seq
1853 if ( ! identityMap[tableoid ][ attnum ].isEmpty()
1854 && notNullMap[tableoid][ attnum ]
1855 && uniqueMap[tableoid][attnum]
1856 && defValMap[tableoid][attnum].isEmpty() )
1857 {
1858 const QString seqName { mTableName + '_' + fieldName + QStringLiteral( "_seq" ) };
1859 const QString seqSql = QStringLiteral( "SELECT c.oid "
1860 " FROM pg_class c "
1861 " LEFT JOIN pg_namespace n "
1862 " ON ( n.oid = c.relnamespace ) "
1863 " WHERE c.relkind = 'S' "
1864 " AND c.relname = %1 "
1865 " AND n.nspname = %2" )
1866 .arg( quotedValue( seqName ),
1867 quotedValue( mSchemaName ) );
1868 QgsPostgresResult seqResult( connectionRO()->PQexec( seqSql ) );
1869 if ( seqResult.PQntuples() == 1 )
1870 {
1871 defValMap[tableoid][attnum] = QStringLiteral( "nextval(%1::regclass)" ).arg( quotedIdentifier( seqName ) );
1872 }
1873 }
1874
1875 mDefaultValues.insert( mAttributeFields.size(), defValMap[tableoid][attnum] );
1876
1877 QgsField newField = QgsField( fieldName, fieldType, fieldTypeName, fieldSize, fieldPrec, fieldComment, fieldSubType );
1878
1879 QgsFieldConstraints constraints;
1880 if ( notNullMap[tableoid][attnum] || ( mPrimaryKeyAttrs.size() == 1 && mPrimaryKeyAttrs[0] == fieldName ) || identityMap[tableoid][attnum] != ' ' )
1881 constraints.setConstraint( QgsFieldConstraints::ConstraintNotNull, QgsFieldConstraints::ConstraintOriginProvider );
1882 if ( uniqueMap[tableoid][attnum] || ( mPrimaryKeyAttrs.size() == 1 && mPrimaryKeyAttrs[0] == fieldName ) || identityMap[tableoid][attnum] != ' ' )
1883 constraints.setConstraint( QgsFieldConstraints::ConstraintUnique, QgsFieldConstraints::ConstraintOriginProvider );
1884 newField.setConstraints( constraints );
1885
1886 mIdentityFields.insert( mAttributeFields.size(), identityMap[tableoid][attnum][0].toLatin1() );
1887 mAttributeFields.append( newField );
1888 }
1889
1890 return true;
1891 }
1892
1893 /* static */
parseUriKey(const QString & key)1894 QStringList QgsPostgresRasterProvider::parseUriKey( const QString &key )
1895 {
1896 if ( key.isEmpty() ) return QStringList();
1897
1898 QStringList cols;
1899
1900 // remove quotes from key list
1901 if ( key.startsWith( '"' ) && key.endsWith( '"' ) )
1902 {
1903 int i = 1;
1904 QString col;
1905 while ( i < key.size() )
1906 {
1907 if ( key[i] == '"' )
1908 {
1909 if ( i + 1 < key.size() && key[i + 1] == '"' )
1910 {
1911 i++;
1912 }
1913 else
1914 {
1915 cols << col;
1916 col.clear();
1917
1918 if ( ++i == key.size() )
1919 break;
1920
1921 Q_ASSERT( key[i] == ',' );
1922 i++;
1923 Q_ASSERT( key[i] == '"' );
1924 i++;
1925 col.clear();
1926 continue;
1927 }
1928 }
1929
1930 col += key[i++];
1931 }
1932 }
1933 else if ( key.contains( ',' ) )
1934 {
1935 cols = key.split( ',' );
1936 }
1937 else
1938 {
1939 cols << key;
1940 }
1941
1942 return cols;
1943 }
1944
relkind() const1945 QgsPostgresProvider::Relkind QgsPostgresRasterProvider::relkind() const
1946 {
1947 if ( mIsQuery || !connectionRO() )
1948 return QgsPostgresProvider::Relkind::Unknown;
1949
1950 QString sql = QStringLiteral( "SELECT relkind FROM pg_class WHERE oid=regclass(%1)::oid" ).arg( quotedValue( mQuery ) );
1951 QgsPostgresResult res( connectionRO()->PQexec( sql ) );
1952 QString type = res.PQgetvalue( 0, 0 );
1953
1954 QgsPostgresProvider::Relkind kind = QgsPostgresProvider::Relkind::Unknown;
1955
1956 if ( type == 'r' )
1957 {
1958 kind = QgsPostgresProvider::Relkind::OrdinaryTable;
1959 }
1960 else if ( type == 'i' )
1961 {
1962 kind = QgsPostgresProvider::Relkind::Index;
1963 }
1964 else if ( type == 's' )
1965 {
1966 kind = QgsPostgresProvider::Relkind::Sequence;
1967 }
1968 else if ( type == 'v' )
1969 {
1970 kind = QgsPostgresProvider::Relkind::View;
1971 }
1972 else if ( type == 'm' )
1973 {
1974 kind = QgsPostgresProvider::Relkind::MaterializedView;
1975 }
1976 else if ( type == 'c' )
1977 {
1978 kind = QgsPostgresProvider::Relkind::CompositeType;
1979 }
1980 else if ( type == 't' )
1981 {
1982 kind = QgsPostgresProvider::Relkind::ToastTable;
1983 }
1984 else if ( type == 'f' )
1985 {
1986 kind = QgsPostgresProvider::Relkind::ForeignTable;
1987 }
1988 else if ( type == 'p' )
1989 {
1990 kind = QgsPostgresProvider::Relkind::PartitionedTable;
1991 }
1992
1993 return kind;
1994 }
1995
determinePrimaryKey()1996 bool QgsPostgresRasterProvider::determinePrimaryKey()
1997 {
1998
1999 // check to see if there is an unique index on the relation, which
2000 // can be used as a key into the table. Primary keys are always
2001 // unique indices, so we catch them as well.
2002
2003 QString sql;
2004
2005 mPrimaryKeyAttrs.clear();
2006
2007 if ( !mIsQuery )
2008 {
2009 sql = QStringLiteral( "SELECT count(*) FROM pg_inherits WHERE inhparent=%1::regclass" ).arg( quotedValue( mQuery ) );
2010 QgsDebugMsgLevel( QStringLiteral( "Checking whether %1 is a parent table" ).arg( sql ), 4 );
2011 QgsPostgresResult res( connectionRO()->PQexec( sql ) );
2012 bool isParentTable( res.PQntuples() == 0 || res.PQgetvalue( 0, 0 ).toInt() > 0 );
2013
2014 sql = QStringLiteral( "SELECT indexrelid FROM pg_index WHERE indrelid=%1::regclass AND (indisprimary OR indisunique) ORDER BY CASE WHEN indisprimary THEN 1 ELSE 2 END LIMIT 1" ).arg( quotedValue( mQuery ) );
2015 QgsDebugMsgLevel( QStringLiteral( "Retrieving first primary or unique index: %1" ).arg( sql ), 4 );
2016
2017 res = connectionRO()->PQexec( sql );
2018 QgsDebugMsgLevel( QStringLiteral( "Got %1 rows." ).arg( res.PQntuples() ), 4 );
2019
2020 QStringList log;
2021
2022 // no primary or unique indices found
2023 if ( res.PQntuples() == 0 )
2024 {
2025 QgsDebugMsgLevel( QStringLiteral( "Relation has no primary key -- investigating alternatives" ), 4 );
2026
2027 // Two options here. If the relation is a table, see if there is
2028 // an oid column that can be used instead.
2029 // If the relation is a view try to find a suitable column to use as
2030 // the primary key.
2031
2032 const QgsPostgresProvider::Relkind type = relkind();
2033
2034 if ( type == QgsPostgresProvider::Relkind::OrdinaryTable || type == QgsPostgresProvider::Relkind::PartitionedTable )
2035 {
2036 QgsDebugMsgLevel( QStringLiteral( "Relation is a table. Checking to see if it has an oid column." ), 4 );
2037
2038 mPrimaryKeyAttrs.clear();
2039 mPrimaryKeyType = PktUnknown;
2040
2041 if ( connectionRO()->pgVersion() >= 100000 )
2042 {
2043 // If there is an generated id on the table, use that instead,
2044 sql = QStringLiteral( "SELECT attname FROM pg_attribute WHERE attidentity IN ('a','d') AND attrelid=regclass(%1) LIMIT 1" ).arg( quotedValue( mQuery ) );
2045 res = connectionRO()->PQexec( sql );
2046 if ( res.PQntuples() == 1 )
2047 {
2048 // Could warn the user here that performance will suffer if
2049 // attribute isn't indexed (and that they may want to add a
2050 // primary key to the table)
2051 mPrimaryKeyAttrs << res.PQgetvalue( 0, 0 );
2052 }
2053 }
2054
2055 if ( mPrimaryKeyType == PktUnknown )
2056 {
2057 // If there is an oid on the table, use that instead,
2058 sql = QStringLiteral( "SELECT attname FROM pg_attribute WHERE attname='oid' AND attrelid=regclass(%1)" ).arg( quotedValue( mQuery ) );
2059
2060 res = connectionRO()->PQexec( sql );
2061 if ( res.PQntuples() == 1 )
2062 {
2063 // Could warn the user here that performance will suffer if
2064 // oid isn't indexed (and that they may want to add a
2065 // primary key to the table)
2066 mPrimaryKeyType = PktOid;
2067 mPrimaryKeyAttrs << QStringLiteral( "oid" );
2068 }
2069 }
2070
2071 if ( mPrimaryKeyType == PktUnknown )
2072 {
2073 sql = QStringLiteral( "SELECT attname FROM pg_attribute WHERE attname='ctid' AND attrelid=regclass(%1)" ).arg( quotedValue( mQuery ) );
2074
2075 res = connectionRO()->PQexec( sql );
2076 if ( res.PQntuples() == 1 )
2077 {
2078 mPrimaryKeyType = PktTid;
2079 QgsMessageLog::logMessage( tr( "Primary key is ctid - changing of existing features disabled (%1; %2)" ).arg( mRasterColumn, mQuery ) );
2080 // TODO: set capabilities to RO when writing will be implemented
2081 mPrimaryKeyAttrs << QStringLiteral( "ctid" );
2082 }
2083 }
2084
2085 if ( mPrimaryKeyType == PktUnknown )
2086 {
2087 QgsMessageLog::logMessage( tr( "The table has no column suitable for use as a key. QGIS requires a primary key, a PostgreSQL oid column or a ctid for tables." ), tr( "PostGIS" ) );
2088 }
2089 }
2090 else if ( type == QgsPostgresProvider::Relkind::View || type == QgsPostgresProvider::Relkind::MaterializedView
2091 || type == QgsPostgresProvider::Relkind::ForeignTable )
2092 {
2093 determinePrimaryKeyFromUriKeyColumn();
2094 }
2095 else
2096 {
2097 QgsMessageLog::logMessage( tr( "Unexpected relation type." ), tr( "PostGIS" ) );
2098 }
2099 }
2100 else
2101 {
2102 // have a primary key or unique index
2103 QString indrelid = res.PQgetvalue( 0, 0 );
2104 sql = QStringLiteral( "SELECT attname, attnotnull, data_type FROM pg_index, pg_attribute "
2105 "JOIN information_schema.columns ON (column_name = attname AND table_name = %1 AND table_schema = %2) "
2106 "WHERE indexrelid=%3 AND indrelid=attrelid AND pg_attribute.attnum=any(pg_index.indkey)" )
2107 .arg( quotedValue( mTableName ) )
2108 .arg( quotedValue( mSchemaName ) )
2109 .arg( indrelid );
2110
2111 QgsDebugMsgLevel( "Retrieving key columns: " + sql, 4 );
2112 res = connectionRO()->PQexec( sql );
2113 QgsDebugMsgLevel( QStringLiteral( "Got %1 rows." ).arg( res.PQntuples() ), 4 );
2114
2115 bool mightBeNull = false;
2116 QString primaryKey;
2117 QString delim;
2118
2119 mPrimaryKeyType = PktFidMap; // map by default, will downgrade if needed
2120 for ( int i = 0; i < res.PQntuples(); i++ )
2121 {
2122 const QString name = res.PQgetvalue( i, 0 );
2123 if ( res.PQgetvalue( i, 1 ).startsWith( 'f' ) )
2124 {
2125 QgsMessageLog::logMessage( tr( "Unique column '%1' doesn't have a NOT NULL constraint." ).arg( name ), tr( "PostGIS" ) );
2126 mightBeNull = true;
2127 }
2128
2129 primaryKey += delim + quotedIdentifier( name );
2130 delim = ',';
2131
2132 QgsPostgresPrimaryKeyType pkType { QgsPostgresPrimaryKeyType::PktUnknown };
2133 const QString fieldTypeName { res.PQgetvalue( i, 2 ) };
2134
2135 if ( fieldTypeName == QLatin1String( "oid" ) )
2136 {
2137 pkType = QgsPostgresPrimaryKeyType::PktOid;
2138 }
2139 else if ( fieldTypeName == QLatin1String( "integer" ) )
2140 {
2141 pkType = QgsPostgresPrimaryKeyType::PktInt;
2142 }
2143 else if ( fieldTypeName == QLatin1String( "bigint" ) )
2144 {
2145 pkType = QgsPostgresPrimaryKeyType::PktUint64;
2146 }
2147 else if ( fieldTypeName == QLatin1String( "text" ) )
2148 {
2149 pkType = QgsPostgresPrimaryKeyType::PktFidMap;
2150 }
2151 // Always use PktFidMap for multi-field keys
2152 mPrimaryKeyType = i ? QgsPostgresPrimaryKeyType::PktFidMap : pkType;
2153 mPrimaryKeyAttrs << name;
2154 }
2155
2156 if ( mightBeNull || isParentTable )
2157 {
2158 QgsMessageLog::logMessage( tr( "Ignoring key candidate because of NULL values or inherited table" ), tr( "PostGIS" ) );
2159 mPrimaryKeyType = PktUnknown;
2160 mPrimaryKeyAttrs.clear();
2161 }
2162 }
2163 }
2164 else
2165 {
2166 determinePrimaryKeyFromUriKeyColumn();
2167 }
2168
2169 if ( mPrimaryKeyAttrs.size() == 0 )
2170 {
2171 QgsMessageLog::logMessage( tr( "Could not find a primary key for PostGIS raster table %1" ).arg( mQuery ), tr( "PostGIS" ) );
2172 mPrimaryKeyType = PktUnknown;
2173 }
2174
2175 return mPrimaryKeyType != PktUnknown;
2176 }
2177
2178
2179
determinePrimaryKeyFromUriKeyColumn()2180 void QgsPostgresRasterProvider::determinePrimaryKeyFromUriKeyColumn()
2181 {
2182 mPrimaryKeyAttrs.clear();
2183 const QString keyCandidate { mUri.keyColumn() };
2184 QgsPostgresPrimaryKeyType pkType { QgsPostgresPrimaryKeyType::PktUnknown };
2185 const QString sql = QStringLiteral( "SELECT data_type FROM information_schema.columns "
2186 "WHERE column_name = %1 AND table_name = %2 AND table_schema = %3" )
2187 .arg( keyCandidate, mTableName, mSchemaName );
2188 QgsPostgresResult result( connectionRO()->PQexec( sql ) );
2189 if ( PGRES_TUPLES_OK == result.PQresultStatus() )
2190 {
2191 const QString fieldTypeName { result.PQgetvalue( 0, 0 ) };
2192
2193 if ( fieldTypeName == QLatin1String( "oid" ) )
2194 {
2195 pkType = QgsPostgresPrimaryKeyType::PktOid;
2196 }
2197 else if ( fieldTypeName == QLatin1String( "integer" ) )
2198 {
2199 pkType = QgsPostgresPrimaryKeyType::PktInt;
2200 }
2201 else if ( fieldTypeName == QLatin1String( "bigint" ) )
2202 {
2203 pkType = QgsPostgresPrimaryKeyType::PktUint64;
2204 }
2205 mPrimaryKeyAttrs.push_back( mUri.keyColumn() );
2206 mPrimaryKeyType = pkType;
2207 }
2208 }
2209
pkSql()2210 QString QgsPostgresRasterProvider::pkSql()
2211 {
2212 Q_ASSERT_X( ! mPrimaryKeyAttrs.isEmpty(), "QgsPostgresRasterProvider::pkSql()", "No PK is defined!" );
2213 if ( mPrimaryKeyAttrs.count( ) > 1 )
2214 {
2215 QStringList pkeys;
2216 for ( const QString &k : std::as_const( mPrimaryKeyAttrs ) )
2217 {
2218 pkeys.push_back( quotedIdentifier( k ) );
2219 }
2220 return pkeys.join( ',' ).prepend( '(' ).append( ')' );
2221 }
2222 return quotedIdentifier( mPrimaryKeyAttrs.first() );
2223 }
2224
dataComment() const2225 QString QgsPostgresRasterProvider::dataComment() const
2226 {
2227 return mDataComment;
2228 }
2229
findOverviews()2230 void QgsPostgresRasterProvider::findOverviews()
2231 {
2232 const QString sql = QStringLiteral( "SELECT overview_factor, o_table_schema, o_table_name, o_raster_column "
2233 "FROM raster_overviews WHERE r_table_schema = %1 AND r_table_name = %2" ).arg( quotedValue( mSchemaName ),
2234 quotedValue( mTableName ) );
2235
2236 //QgsDebugMsg( QStringLiteral( "Raster overview information sql: %1" ).arg( sql ) );
2237 QgsPostgresResult result( connectionRO()->PQexec( sql ) );
2238 if ( PGRES_TUPLES_OK == result.PQresultStatus() )
2239 {
2240 for ( int i = 0; i < result.PQntuples(); ++i )
2241 {
2242 bool ok;
2243 const unsigned int overViewFactor { static_cast< unsigned int>( result.PQgetvalue( i, 0 ).toInt( & ok ) ) };
2244 if ( ! ok )
2245 {
2246 QgsMessageLog::logMessage( tr( "Cannot convert overview factor '%1' to int" ).arg( result.PQgetvalue( i, 0 ) ), QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
2247 return;
2248 }
2249 const QString schema { result.PQgetvalue( i, 1 ) };
2250 const QString table { result.PQgetvalue( i, 2 ) };
2251 if ( table.isEmpty() || schema.isEmpty() )
2252 {
2253 QgsMessageLog::logMessage( tr( "Table or schema is empty" ), QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
2254 return;
2255 }
2256 mOverViews[ overViewFactor ] = QStringLiteral( "%1.%2" ).arg( quotedIdentifier( schema ) ).arg( quotedIdentifier( table ) );
2257 }
2258 }
2259 else
2260 {
2261 QgsMessageLog::logMessage( tr( "Error fetching overviews information: %1" ).arg( result.PQresultErrorMessage() ), QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
2262 }
2263 if ( mOverViews.isEmpty() )
2264 {
2265 QgsMessageLog::logMessage( tr( "No overviews found, performances may be affected for %1" ).arg( mQuery ), QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Info );
2266 }
2267 }
2268
xSize() const2269 int QgsPostgresRasterProvider::xSize() const
2270 {
2271 return static_cast<int>( mWidth );
2272 }
2273
ySize() const2274 int QgsPostgresRasterProvider::ySize() const
2275 {
2276 return static_cast<int>( mHeight );
2277 }
2278
sourceDataType(int bandNo) const2279 Qgis::DataType QgsPostgresRasterProvider::sourceDataType( int bandNo ) const
2280 {
2281 if ( bandNo <= mBandCount && static_cast<unsigned long>( bandNo ) <= mDataTypes.size() )
2282 {
2283 return mDataTypes[ static_cast<unsigned long>( bandNo - 1 ) ];
2284 }
2285 else
2286 {
2287 QgsMessageLog::logMessage( tr( "Data type is unknown" ), QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
2288 return Qgis::DataType::UnknownDataType;
2289 }
2290 }
2291
xBlockSize() const2292 int QgsPostgresRasterProvider::xBlockSize() const
2293 {
2294 if ( mInput )
2295 {
2296 return mInput->xBlockSize();
2297 }
2298 else
2299 {
2300 return static_cast<int>( mWidth );
2301 }
2302 }
2303
yBlockSize() const2304 int QgsPostgresRasterProvider::yBlockSize() const
2305 {
2306 if ( mInput )
2307 {
2308 return mInput->yBlockSize();
2309 }
2310 else
2311 {
2312 return static_cast<int>( mHeight );
2313 }
2314 }
2315
bandStatistics(int bandNo,int stats,const QgsRectangle & extent,int sampleSize,QgsRasterBlockFeedback * feedback)2316 QgsRasterBandStats QgsPostgresRasterProvider::bandStatistics( int bandNo, int stats, const QgsRectangle &extent, int sampleSize, QgsRasterBlockFeedback *feedback )
2317 {
2318 Q_UNUSED( feedback )
2319 QgsRasterBandStats rasterBandStats;
2320 const auto constMStatistics = mStatistics;
2321 initStatistics( rasterBandStats, bandNo, stats, extent, sampleSize );
2322 for ( const QgsRasterBandStats &stats : constMStatistics )
2323 {
2324 if ( stats.contains( rasterBandStats ) )
2325 {
2326 QgsDebugMsgLevel( QStringLiteral( "Using cached statistics." ), 4 );
2327 return stats;
2328 }
2329 }
2330
2331 QString tableToQuery { mQuery };
2332 const double pixelsRatio { static_cast<double>( sampleSize ) / ( mWidth * mHeight ) };
2333 double statsRatio { pixelsRatio };
2334
2335 // Decide if overviews can be used here
2336 if ( subsetString().isEmpty() && ! mIsQuery && mIsTiled && extent.isEmpty() )
2337 {
2338 const unsigned int desiredOverviewFactor { static_cast<unsigned int>( 1.0 / sqrt( pixelsRatio ) ) };
2339 const auto ovKeys { mOverViews.keys( ) };
2340 QList<unsigned int>::const_reverse_iterator rit { ovKeys.rbegin() };
2341 for ( ; rit != ovKeys.rend(); ++rit )
2342 {
2343 if ( *rit <= desiredOverviewFactor )
2344 {
2345 tableToQuery = mOverViews[ *rit ];
2346 // This should really be: *= *rit * *rit;
2347 // but we are already approximating, let's get decent statistics
2348 statsRatio = 1;
2349 QgsDebugMsgLevel( QStringLiteral( "Using overview for statistics read: %1" ).arg( tableToQuery ), 3 );
2350 break;
2351 }
2352 }
2353 }
2354
2355 // Query the backend
2356 QString where { extent.isEmpty() ? QString() : QStringLiteral( "WHERE %1 && ST_GeomFromText( %2, %3 )" )
2357 .arg( quotedIdentifier( mRasterColumn ) )
2358 .arg( quotedValue( extent.asWktPolygon() ) )
2359 .arg( mCrs.postgisSrid() ) };
2360
2361 if ( ! subsetString().isEmpty() )
2362 {
2363 where.append( where.isEmpty() ? QStringLiteral( "WHERE %1" ).arg( subsetString() ) :
2364 QStringLiteral( " AND %1" ).arg( subsetString() ) );
2365 }
2366
2367 const QString sql = QStringLiteral( "SELECT (ST_SummaryStatsAgg( %1, %2, TRUE, %3 )).* "
2368 "FROM %4 %5" ).arg( quotedIdentifier( mRasterColumn ) )
2369 .arg( bandNo )
2370 .arg( std::max<double>( 0, std::min<double>( 1, statsRatio ) ) )
2371 .arg( tableToQuery, where );
2372
2373 QgsPostgresResult result( connectionRO()->PQexec( sql ) );
2374
2375 if ( PGRES_TUPLES_OK == result.PQresultStatus() && result.PQntuples() == 1 )
2376 {
2377 // count | sum | mean | stddev | min | max
2378 rasterBandStats.sum = result.PQgetvalue( 0, 1 ).toDouble( );
2379 rasterBandStats.mean = result.PQgetvalue( 0, 2 ).toDouble( );
2380 rasterBandStats.stdDev = result.PQgetvalue( 0, 3 ).toDouble( );
2381 rasterBandStats.minimumValue = result.PQgetvalue( 0, 4 ).toDouble( );
2382 rasterBandStats.maximumValue = result.PQgetvalue( 0, 5 ).toDouble( );
2383 rasterBandStats.range = rasterBandStats.maximumValue - rasterBandStats.minimumValue;
2384 }
2385 else
2386 {
2387 QgsMessageLog::logMessage( tr( "Error fetching statistics for %1: %2\nSQL: %3" )
2388 .arg( mQuery )
2389 .arg( result.PQresultErrorMessage() )
2390 .arg( sql ),
2391 QStringLiteral( "PostGIS" ), Qgis::MessageLevel::Warning );
2392 }
2393
2394 QgsDebugMsgLevel( QStringLiteral( "************ STATS **************" ), 4 );
2395 QgsDebugMsgLevel( QStringLiteral( "MIN %1" ).arg( rasterBandStats.minimumValue ), 4 );
2396 QgsDebugMsgLevel( QStringLiteral( "MAX %1" ).arg( rasterBandStats.maximumValue ), 4 );
2397 QgsDebugMsgLevel( QStringLiteral( "RANGE %1" ).arg( rasterBandStats.range ), 4 );
2398 QgsDebugMsgLevel( QStringLiteral( "MEAN %1" ).arg( rasterBandStats.mean ), 4 );
2399 QgsDebugMsgLevel( QStringLiteral( "STDDEV %1" ).arg( rasterBandStats.stdDev ), 4 );
2400
2401 mStatistics.append( rasterBandStats );
2402 return rasterBandStats;
2403 }
2404
2405
2406 #ifndef HAVE_STATIC_PROVIDERS
providerMetadataFactory()2407 QGISEXTERN QgsProviderMetadata *providerMetadataFactory()
2408 {
2409 return new QgsPostgresRasterProviderMetadata();
2410 }
2411 #endif
2412
2413
QgsPostgresRasterProviderException(const QString & msg)2414 QgsPostgresRasterProviderException::QgsPostgresRasterProviderException( const QString &msg )
2415 : message( msg )
2416 {}
2417
fields() const2418 QgsFields QgsPostgresRasterProvider::fields() const
2419 {
2420 return mAttributeFields;
2421 }
2422