PG raster temporal, partly revert expose default as filter

qgis · Apr 6, 2020 · f3a76d9 · f3a76d9
1 parent 3f8c28b
commit f3a76d9
Show file tree

Hide file tree

Showing 5 changed files with 85 additions and 37 deletions.
diff --git a/src/gui/raster/qgsrasterlayerproperties.cpp b/src/gui/raster/qgsrasterlayerproperties.cpp
@@ -298,7 +298,6 @@ QgsRasterLayerProperties::QgsRasterLayerProperties( QgsMapLayer *lyr, QgsMapCanv
     const QgsFields fields { mRasterLayer->dataProvider()->fields() };
     mPostgresRasterTemporalFieldComboBox->setFields( fields );
     mPostgresRasterTemporalFieldComboBox->setFilters( QgsFieldProxyModel::Filter::Date |
-        QgsFieldProxyModel::Filter::Time |
         QgsFieldProxyModel::Filter::DateTime |
         QgsFieldProxyModel::Filter::String );
     mPostgresRasterTemporalFieldComboBox->setAllowEmptyFieldName( true );

diff --git a/src/providers/postgres/raster/qgspostgresrasterprovider.cpp b/src/providers/postgres/raster/qgspostgresrasterprovider.cpp
@@ -703,10 +703,32 @@ QString QgsPostgresRasterProvider::subsetString() const
   return mSqlWhereClause;
 }
 
+QString QgsPostgresRasterProvider::defaultTimeSubsetString( const QDateTime &defaultTime ) const
+{
+  if ( defaultTime.isValid( ) &&
+       mTemporalFieldIndex >= 0 &&
+       mAttributeFields.exists( mTemporalFieldIndex ) )
+  {
+    const QgsField temporalField { mAttributeFields.field( mTemporalFieldIndex ) };
+    const QString typeCast { temporalField.type() != QVariant::DateTime ? QStringLiteral( "::timestamp" ) : QString() };
+    const QString temporalFieldName { temporalField.name() };
+    return  { QStringLiteral( "%1%2 = %3" )
+              .arg( quotedIdentifier( temporalFieldName ),
+                    typeCast,
+                    quotedValue( defaultTime.toString( Qt::DateFormat::ISODate ) ) ) };
+  }
+  else
+  {
+    return QString();
+  }
+}
+
 bool QgsPostgresRasterProvider::setSubsetString( const QString &subset, bool updateFeatureCount )
 {
   Q_UNUSED( updateFeatureCount )
-  const QString oldSql { subsetString() };
+
+  const QString oldSql { mSqlWhereClause };
+
   mSqlWhereClause = subset;
   // Recalculate extent and other metadata calling init()
   if ( !init() )
@@ -732,7 +754,10 @@ QString QgsPostgresRasterProvider::subsetStringWithTemporalRange() const
   // Temporal
   if ( mTemporalFieldIndex >= 0 && mAttributeFields.exists( mTemporalFieldIndex ) )
   {
-    const QString temporalFieldName { mAttributeFields.field( mTemporalFieldIndex ).name() };
+    const QgsField temporalField { mAttributeFields.field( mTemporalFieldIndex ) };
+    const QString typeCast { temporalField.type() != QVariant::DateTime ? QStringLiteral( "::timestamp" ) : QString() };
+    const QString temporalFieldName { temporalField.name() };
+
     if ( temporalCapabilities()->hasTemporalCapabilities() )
     {
       QString temporalClause;
@@ -741,42 +766,47 @@ QString QgsPostgresRasterProvider::subsetStringWithTemporalRange() const
       {
         if ( requestedRange.isInstant() )
         {
-          temporalClause = QStringLiteral( "%1::timestamp = %2" ).arg( quotedIdentifier( temporalFieldName ),
-                           quotedValue( requestedRange.begin().toString( Qt::DateFormat::ISODate ) ) );
+          temporalClause = QStringLiteral( "%1%2 = %3" )
+                           .arg( quotedIdentifier( temporalFieldName ),
+                                 typeCast,
+                                 quotedValue( requestedRange.begin().toString( Qt::DateFormat::ISODate ) ) );
         }
         else
         {
           if ( requestedRange.begin().isValid() )
           {
-            temporalClause = QStringLiteral( "%1::timestamp %2 %3" ).arg( quotedIdentifier( temporalFieldName ),
-                             requestedRange.includeBeginning() ? ">=" : ">",
-                             quotedValue( requestedRange.begin().toString( Qt::DateFormat::ISODate ) ) );
+            temporalClause = QStringLiteral( "%1%2 %3 %4" )
+                             .arg( quotedIdentifier( temporalFieldName ),
+                                   typeCast,
+                                   requestedRange.includeBeginning() ? ">=" : ">",
+                                   quotedValue( requestedRange.begin().toString( Qt::DateFormat::ISODate ) ) );
           }
           if ( requestedRange.end().isValid() )
           {
             if ( ! temporalClause.isEmpty() )
             {
               temporalClause.append( QStringLiteral( " AND " ) );
             }
-            temporalClause.append( QStringLiteral( "%1::timestamp %2 %3" ).arg( quotedIdentifier( temporalFieldName ),
-                                   requestedRange.includeEnd() ? "<=" : "<",
-                                   quotedValue( requestedRange.end().toString( Qt::DateFormat::ISODate ) ) ) );
+            temporalClause.append( QStringLiteral( "%1%2 %3 %4" )
+                                   .arg( quotedIdentifier( temporalFieldName ),
+                                         typeCast,
+                                         requestedRange.includeEnd() ? "<=" : "<",
+                                         quotedValue( requestedRange.end().toString( Qt::DateFormat::ISODate ) ) ) );
           }
         }
         return mSqlWhereClause.isEmpty() ? temporalClause : QStringLiteral( "%1 AND (%2)" ).arg( mSqlWhereClause, temporalClause );
       }
-    }
-
-    if ( mTemporalDefaultTime.isValid( ) )
-    {
-      const QString temporalClause { QStringLiteral( "%1::timestamp = %2" ).arg( quotedIdentifier( temporalFieldName ),
-                                     quotedValue( mTemporalDefaultTime.toString( Qt::DateFormat::ISODate ) ) ) };
-      return mSqlWhereClause.isEmpty() ? temporalClause : QStringLiteral( "%1 AND (%2)" ).arg( mSqlWhereClause, temporalClause );
+      const QString defaultTimeSubset { defaultTimeSubsetString( mTemporalDefaultTime ) };
+      if ( ! defaultTimeSubset.isEmpty() )
+      {
+        return mSqlWhereClause.isEmpty() ? defaultTimeSubset : QStringLiteral( "%1 AND (%2)" ).arg( mSqlWhereClause, defaultTimeSubset );
+      }
     }
   }
   return mSqlWhereClause;
 }
 
+
 void QgsPostgresRasterProvider::disconnectDb()
 {
   if ( mConnectionRO )

diff --git a/src/providers/postgres/raster/qgspostgresrasterprovider.h b/src/providers/postgres/raster/qgspostgresrasterprovider.h
@@ -129,7 +129,7 @@ class QgsPostgresRasterProvider : public QgsRasterDataProvider
     double mScaleY = 0;
     //! Temporal field index
     int mTemporalFieldIndex = -1;
-    //! Temporal default date
+    //! Temporal default time
     QDateTime mTemporalDefaultTime;
     //! Keep track of fields
     QgsFields mAttributeFields;
@@ -176,6 +176,9 @@ class QgsPostgresRasterProvider : public QgsRasterDataProvider
     //! Subset string with temporal range from request (if any)
     QString subsetStringWithTemporalRange() const;
 
+    //! Subset string with only the temporal default time part
+    QString defaultTimeSubsetString( const QDateTime &defaultTime ) const;
+
     bool hasSufficientPermsAndCapabilities();
     void disconnectDb();
     //! Initialize the raster by fetching metadata and creating spatial indexes.

diff --git a/src/ui/qgsrasterlayerpropertiesbase.ui b/src/ui/qgsrasterlayerpropertiesbase.ui
@@ -301,9 +301,9 @@
               <property name="geometry">
                <rect>
                 <x>0</x>
-                <y>0</y>
+                <y>-188</y>
                 <width>629</width>
-                <height>824</height>
+                <height>914</height>
                </rect>
               </property>
               <layout class="QVBoxLayout" name="verticalLayout_7">
@@ -457,7 +457,7 @@ border-radius: 2px;</string>
                       <widget class="QgsDateTimeEdit" name="mStartStaticDateTimeEdit">
                        <property name="dateTime">
                         <datetime>
-                         <hour>4</hour>
+                         <hour>2</hour>
                          <minute>3</minute>
                          <second>57</second>
                          <year>2020</year>
@@ -501,7 +501,7 @@ border-radius: 2px;</string>
                       <widget class="QgsDateTimeEdit" name="mReferenceDateTimeEdit">
                        <property name="dateTime">
                         <datetime>
-                         <hour>17</hour>
+                         <hour>16</hour>
                          <minute>20</minute>
                          <second>36</second>
                          <year>2020</year>
@@ -589,6 +589,23 @@ border-radius: 2px;</string>
                   <bool>true</bool>
                  </property>
                  <layout class="QGridLayout" name="gridLayout_18">
+                  <item row="0" column="0" colspan="2">
+                   <widget class="QLabel" name="mPostgresRasterTemporalLabel">
+                    <property name="text">
+                     <string>If the dataset contains multiple rasters belonging to a time series, specify the field that contains the time information, the type of the field can be any type that can be converted to a timestamp.</string>
+                    </property>
+                    <property name="wordWrap">
+                     <bool>true</bool>
+                    </property>
+                   </widget>
+                  </item>
+                  <item row="3" column="0">
+                   <widget class="QLabel" name="label_12">
+                    <property name="text">
+                     <string>Default time</string>
+                    </property>
+                   </widget>
+                  </item>
                   <item row="1" column="1">
                    <widget class="QgsFieldComboBox" name="mPostgresRasterTemporalFieldComboBox">
                     <property name="sizePolicy">
@@ -599,33 +616,26 @@ border-radius: 2px;</string>
                     </property>
                    </widget>
                   </item>
-                  <item row="2" column="1">
-                   <widget class="QgsDateTimeEdit" name="mPostgresRasterDefaultTime"/>
-                  </item>
                   <item row="1" column="0">
                    <widget class="QLabel" name="label">
                     <property name="text">
                      <string>Temporal field</string>
                     </property>
                    </widget>
                   </item>
-                  <item row="0" column="0" colspan="2">
-                   <widget class="QLabel" name="mPostgresRasterTemporalLabel">
+                  <item row="3" column="1">
+                   <widget class="QgsDateTimeEdit" name="mPostgresRasterDefaultTime"/>
+                  </item>
+                  <item row="2" column="0" colspan="2">
+                   <widget class="QLabel" name="label_14">
                     <property name="text">
-                     <string>If the dataset contains multiple rasters belonging to a time series, specify the field that contains the time information, the type of the field can be any type that can be converted to a timestamp. A default value can be specified and it will be used when the temporal controller is not requesting any particular time.</string>
+                     <string>&lt;html&gt;&lt;head/&gt;&lt;body&gt;&lt;p&gt;A default value can be specified and it will be used when the temporal controller is not requesting any particular time.&lt;/p&gt;&lt;p&gt;It is recommended to set a default temporal value here instead of using a provider filter because the provider filter will still be active when using the temporal controller while this default value will be ignored.&lt;/p&gt;&lt;/body&gt;&lt;/html&gt;</string>
                     </property>
                     <property name="wordWrap">
                      <bool>true</bool>
                     </property>
                    </widget>
                   </item>
-                  <item row="2" column="0">
-                   <widget class="QLabel" name="label_12">
-                    <property name="text">
-                     <string>Default time</string>
-                    </property>
-                   </widget>
-                  </item>
                  </layout>
                 </widget>
                </item>

diff --git a/tests/src/python/test_provider_postgresraster.py b/tests/src/python/test_provider_postgresraster.py
@@ -318,7 +318,7 @@ def testSetSubsetString(self):
         self.assertEqual(data, [136, 142, 145, 153])
 
     def testTime(self):
-        """Test time series"""
+        """Test time series and time subset string when default value is set"""
 
         def _test_block(rl, expected_block, expected_single):
 
@@ -335,28 +335,34 @@ def _test_block(rl, expected_block, expected_single):
 
         # First check that setting different temporal default values we get different results
         rl = QgsRasterLayer(self.dbconn + " sslmode=disable table={table} schema={schema} temporalDefaultTime='2020-04-01T00:00:00' temporalFieldIndex='2'".format(table='raster_4326_time', schema='public'), 'pg_layer', 'postgresraster')
+        self.assertEqual(rl.subsetString(), "")
 
         _test_block(rl, [161, 218, 113, 142], 226)
 
         rl = QgsRasterLayer(self.dbconn + " sslmode=disable table={table} schema={schema} temporalDefaultTime='2020-04-05T00:00:00' temporalFieldIndex='2'".format(table='raster_4326_time', schema='public'), 'pg_layer', 'postgresraster')
+        self.assertEqual(rl.subsetString(), "")
 
         _test_block(rl, [227, 254, 179, 206], 254)
 
         # Check that manually setting a subsetString we get the same results
         rl = QgsRasterLayer(self.dbconn + " sslmode=disable table={table} schema={schema}  sql=\"data\" = '2020-04-01'".format(table='raster_4326_time', schema='public'), 'pg_layer', 'postgresraster')
+        self.assertEqual(rl.subsetString(), '"data" = \'2020-04-01\'')
 
         _test_block(rl, [161, 218, 113, 142], 226)
 
         rl = QgsRasterLayer(self.dbconn + " sslmode=disable table={table} schema={schema}  sql=\"data\" = '2020-04-05'".format(table='raster_4326_time', schema='public'), 'pg_layer', 'postgresraster')
+        self.assertEqual(rl.subsetString(), '"data" = \'2020-04-05\'')
 
         _test_block(rl, [227, 254, 179, 206], 254)
 
         # Now check if the varchar temporal field works the same
         rl = QgsRasterLayer(self.dbconn + " sslmode=disable table={table} schema={schema} temporalDefaultTime='2020-04-01T00:00:00' temporalFieldIndex='3'".format(table='raster_4326_time', schema='public'), 'pg_layer', 'postgresraster')
+        self.assertEqual(rl.subsetString(), '')
 
         _test_block(rl, [161, 218, 113, 142], 226)
 
         rl = QgsRasterLayer(self.dbconn + " sslmode=disable table={table} schema={schema} temporalDefaultTime='2020-04-05T00:00:00' temporalFieldIndex='3'".format(table='raster_4326_time', schema='public'), 'pg_layer', 'postgresraster')
+        self.assertEqual(rl.subsetString(), '')
 
         _test_block(rl, [227, 254, 179, 206], 254)