Skip to content

Commit

Permalink
Merge pull request #33685 from elpaso/pgraster-pkeys
Browse files Browse the repository at this point in the history 
Native PostGIS raster data provider
  • Loading branch information
@elpaso
elpaso committed Jan 17, 2020
2 parents a91b5ce + 1682f76 commit 5af1306
Show file tree
Hide file tree
Showing 18 changed files with 2,933 additions and 65 deletions.
25 changes: 13 additions & 12 deletions python/plugins/db_manager/db_plugins/postgis/plugin.py
View file
Expand Up @@ -319,21 +319,23 @@ def info(self):

return PGRasterTableInfo(self)

def gdalUri(self, uri=None):
def uri(self, uri=None):
"""Returns the datasource URI for postgresraster provider"""

if not uri:
uri = self.database().uri()
service = (u'service=\'%s\'' % uri.service()) if uri.service() else ''
schema = (u'schema=\'%s\'' % self.schemaName()) if self.schemaName() else ''
dbname = (u'dbname=\'%s\'' % uri.database()) if uri.database() else ''
host = (u'host=%s' % uri.host()) if uri.host() else ''
user = (u'user=%s' % uri.username()) if uri.username() else ''
passw = (u'password=%s' % uri.password()) if uri.password() else ''
port = (u'port=%s' % uri.port()) if uri.port() else ''

schema = self.schemaName() if self.schemaName() else 'public'
table = '"%s"."%s"' % (schema, self.name)

if not dbname:
# GDAL postgisraster driver *requires* ad dbname
# See: https://trac.osgeo.org/gdal/ticket/6910
# TODO: cache this ?
# postgresraster provider *requires* a dbname
connector = self.database().connector
r = connector._execute(None, "SELECT current_database()")
dbname = (u'dbname=\'%s\'' % connector._fetchone(r)[0])
Expand All @@ -346,20 +348,19 @@ def gdalUri(self, uri=None):
col = u'column=\'%s\'' % fld.name
break

gdalUri = u'PG: %s %s %s %s %s %s mode=2 %s %s table=\'%s\'' % \
(service, dbname, host, user, passw, port, schema, col, self.name)
uri = u'%s %s %s %s %s %s %s table=%s' % \
(service, dbname, host, user, passw, port, col, table)

return gdalUri
return uri

def mimeUri(self):
# QGIS has no provider for PGRasters, let's use GDAL
uri = u"raster:gdal:{}:{}".format(self.name, re.sub(":", r"\:", self.gdalUri()))
uri = u"raster:postgresraster:{}:{}".format(self.name, re.sub(":", r"\:", self.uri()))
return uri

def toMapLayer(self):
from qgis.core import QgsRasterLayer, QgsContrastEnhancement, QgsDataSourceUri, QgsCredentials

rl = QgsRasterLayer(self.gdalUri(), self.name)
rl = QgsRasterLayer(self.uri(), self.name, "postgresraster")
if not rl.isValid():
err = rl.error().summary()
uri = QgsDataSourceUri(self.database().uri())
Expand All @@ -372,7 +373,7 @@ def toMapLayer(self):
if ok:
uri.setUsername(username)
uri.setPassword(password)
rl = QgsRasterLayer(self.gdalUri(uri), self.name)
rl = QgsRasterLayer(self.uri(uri), self.name)
if rl.isValid():
break

Expand Down
22 changes: 14 additions & 8 deletions python/plugins/db_manager/db_plugins/postgis/plugin_test.py
View file
Expand Up @@ -43,7 +43,13 @@ class TestDBManagerPostgisPlugin(unittest.TestCase):
@classmethod
def setUpClass(self):
self.old_pgdatabase_env = os.environ.get('PGDATABASE')
self.testdb = os.environ.get('QGIS_PGTEST_DB') or 'qgis_test'
# QGIS_PGTEST_DB contains the full connection string and not only the DB name!
QGIS_PGTEST_DB = os.environ.get('QGIS_PGTEST_DB')
if not QGIS_PGTEST_DB is None:
test_uri = QgsDataSourceUri(QGIS_PGTEST_DB)
self.testdb = test_uri.database()
else:
self.testdb = 'qgis_test'
os.environ['PGDATABASE'] = self.testdb

# Create temporary service file
Expand All @@ -68,16 +74,16 @@ def tearDownClass(self):

# See https://github.com/qgis/QGIS/issues/24525

def test_rasterTableGdalURI(self):
def test_rasterTableURI(self):

def check_rasterTableGdalURI(expected_dbname):
def check_rasterTableURI(expected_dbname):
tables = database.tables()
raster_tables_count = 0
for tab in tables:
if tab.type == Table.RasterType:
raster_tables_count += 1
gdalUri = tab.gdalUri()
m = re.search(' dbname=\'([^ ]*)\' ', gdalUri)
uri = tab.uri()
m = re.search(' dbname=\'([^ ]*)\' ', uri)
self.assertTrue(m)
actual_dbname = m.group(1)
self.assertEqual(actual_dbname, expected_dbname)
Expand All @@ -87,7 +93,7 @@ def check_rasterTableGdalURI(expected_dbname):

# We need to make sure a database is created with at
# least one raster table !
self.assertEqual(raster_tables_count, 1)
self.assertTrue(raster_tables_count > 1)

obj = QObject() # needs to be kept alive

Expand All @@ -107,7 +113,7 @@ def check_rasterTableGdalURI(expected_dbname):
self.assertEqual(uri.database(), expected_dbname)
self.assertEqual(uri.service(), '')

check_rasterTableGdalURI(expected_dbname)
check_rasterTableURI(expected_dbname)

# Test for service-only URI
# See https://github.com/qgis/QGIS/issues/24526
Expand All @@ -122,7 +128,7 @@ def check_rasterTableGdalURI(expected_dbname):
self.assertEqual(uri.database(), '')
self.assertEqual(uri.service(), 'dbmanager')

check_rasterTableGdalURI(expected_dbname)
check_rasterTableURI(expected_dbname)

# See https://github.com/qgis/QGIS/issues/24732
def test_unicodeInQuery(self):
Expand Down
9 changes: 9 additions & 0 deletions src/core/qgsgenericspatialindex.h
View file
Expand Up @@ -130,6 +130,15 @@ class CORE_EXPORT QgsGenericSpatialIndex
return true;
}

/**
* Returns TRUE if the index contains no items.
*/
bool isEmpty( ) const
{
QMutexLocker locker( &mMutex );
return mIdToData.isEmpty();
}

private:

std::unique_ptr< SpatialIndex::ISpatialIndex > createSpatialIndex( SpatialIndex::IStorageManager &storageManager )
Expand Down
49 changes: 48 additions & 1 deletion src/providers/postgres/CMakeLists.txt
View file
Expand Up @@ -41,6 +41,7 @@ INCLUDE_DIRECTORIES(
${CMAKE_SOURCE_DIR}/src/core/geometry
${CMAKE_SOURCE_DIR}/src/core/metadata
${CMAKE_SOURCE_DIR}/src/core/symbology
${CMAKE_SOURCE_DIR}/src/core/raster
${CMAKE_SOURCE_DIR}/src/gui
${CMAKE_SOURCE_DIR}/src/gui/auth
${CMAKE_SOURCE_DIR}/external
Expand All @@ -55,6 +56,8 @@ INCLUDE_DIRECTORIES(SYSTEM
${POSTGRES_INCLUDE_DIR}
${QCA_INCLUDE_DIR}
${QTKEYCHAIN_INCLUDE_DIR}
${GDAL_INCLUDE_DIR}
${CMAKE_CURRENT_SOURCE_DIR}
)

IF (WITH_GUI)
Expand Down Expand Up @@ -91,38 +94,82 @@ IF (WITH_GUI)
ADD_DEPENDENCIES(postgresprovider_gui_a ui)
ENDIF (WITH_GUI)

#################################################################
# Postgres Raster

SET(PGRASTER_SRCS
raster/qgspostgresrasterprovider.cpp
raster/qgspostgresrastershareddata.cpp
raster/qgspostgresrasterutils.cpp
qgspostgresconn.cpp
qgspostgresconnpool.cpp
)

# static library
ADD_LIBRARY (postgresrasterprovider_a STATIC ${PGRASTER_SRCS} ${PG_HDRS})
TARGET_LINK_LIBRARIES (postgresrasterprovider_a
${POSTGRES_LIBRARY}
${Qt5Xml_LIBRARIES}
${Qt5Core_LIBRARIES}
${Qt5Svg_LIBRARIES}
${Qt5Network_LIBRARIES}
${Qt5Sql_LIBRARIES}
${Qt5Concurrent_LIBRARIES}
${Qt5Gui_LIBRARIES}
${Qt5Widgets_LIBRARIES}
)

#################################################################

IF (FORCE_STATIC_PROVIDERS)
# for (external) mobile apps to be able to pick up provider for linking
INSTALL (TARGETS postgresprovider_a ARCHIVE DESTINATION ${QGIS_PLUGIN_DIR})
INSTALL (TARGETS postgresrasterprovider_a ARCHIVE DESTINATION ${QGIS_PLUGIN_DIR})
IF (WITH_GUI)
INSTALL (TARGETS postgresprovider_gui_a ARCHIVE DESTINATION ${QGIS_PLUGIN_DIR})
ENDIF (WITH_GUI)
ELSE (FORCE_STATIC_PROVIDERS)
# dynamically loaded module
ADD_LIBRARY(postgresprovider MODULE ${PG_SRCS} ${PG_GUI_SRCS} ${PG_HDRS})
ADD_LIBRARY(postgresrasterprovider MODULE ${PGRASTER_SRCS} ${PG_HDRS})

TARGET_LINK_LIBRARIES(postgresprovider
${POSTGRES_LIBRARY}
qgis_core
)

TARGET_LINK_LIBRARIES(postgresrasterprovider
${POSTGRES_LIBRARY}
qgis_core
)

IF (WITH_GUI)
TARGET_LINK_LIBRARIES (postgresprovider
qgis_gui
)
ADD_DEPENDENCIES(postgresprovider ui)
ADD_DEPENDENCIES(postgresprovider ui)
ENDIF (WITH_GUI)


# clang-tidy
IF(CLANG_TIDY_EXE)
SET_TARGET_PROPERTIES(
postgresprovider PROPERTIES
CXX_CLANG_TIDY "${DO_CLANG_TIDY}"
)
SET_TARGET_PROPERTIES(
postgresrasterprovider PROPERTIES
CXX_CLANG_TIDY "${DO_CLANG_TIDY}"
)
ENDIF(CLANG_TIDY_EXE)

INSTALL(TARGETS postgresprovider
RUNTIME DESTINATION ${QGIS_PLUGIN_DIR}
LIBRARY DESTINATION ${QGIS_PLUGIN_DIR})

INSTALL(TARGETS postgresrasterprovider
RUNTIME DESTINATION ${QGIS_PLUGIN_DIR}
LIBRARY DESTINATION ${QGIS_PLUGIN_DIR})


ENDIF (FORCE_STATIC_PROVIDERS)
3 changes: 2 additions & 1 deletion src/providers/postgres/qgspgsourceselect.cpp
View file
Expand Up @@ -533,7 +533,8 @@ void QgsPgSourceSelect::addButtonClicked()
{
for ( const auto &u : qgis::as_const( rasterTables ) )
{
emit addRasterLayer( u.second, u.first, QLatin1String( "gdal" ) );
// Use "gdal" to proxy rasters to GDAL provider, or "postgresraster" for native PostGIS raster provider
emit addRasterLayer( u.second, u.first, QLatin1String( "postgresraster" ) );
}
}

Expand Down
2 changes: 1 addition & 1 deletion src/providers/postgres/qgspostgresconn.cpp
View file
Expand Up @@ -1687,7 +1687,7 @@ void QgsPostgresConn::retrieveLayerTypes( QVector<QgsPostgresLayerProperty *> &l
if ( layerProperty.isRaster )
{
QString sql = QStringLiteral( "SELECT %3, "
"array_agg(DISTINCT 'RASTER:' || ST_SRID( %1 ))"
"array_agg(DISTINCT ST_SRID( %1 ) || ':RASTER')"
" FROM %2" )
.arg( quotedIdentifier( layerProperty.geometryColName ) )
.arg( table )
Expand Down
21 changes: 4 additions & 17 deletions src/providers/postgres/qgspostgresdataitems.cpp
View file
Expand Up @@ -26,7 +26,6 @@
#include "qgsprojectstorageregistry.h"
#include "qgsvectorlayer.h"
#include "qgssettings.h"
#include "providers/gdal/qgsgdaldataitems.h"
#include <QMessageBox>
#include <climits>

Expand Down Expand Up @@ -312,7 +311,7 @@ bool QgsPGConnectionItem::handleDrop( const QMimeData *data, const QString &toSc

// ---------------------------------------------------------------------------
QgsPGLayerItem::QgsPGLayerItem( QgsDataItem *parent, const QString &name, const QString &path, QgsLayerItem::LayerType layerType, const QgsPostgresLayerProperty &layerProperty )
: QgsLayerItem( parent, name, path, QString(), layerType, QStringLiteral( "postgres" ) )
: QgsLayerItem( parent, name, path, QString(), layerType, layerProperty.isRaster ? QStringLiteral( "postgresraster" ) : QStringLiteral( "postgres" ) )
, mLayerProperty( layerProperty )
{
mCapabilities |= Delete;
Expand Down Expand Up @@ -410,6 +409,7 @@ QVector<QgsDataItem *> QgsPGSchemaItem::createChildren()
continue;

if ( !layerProperty.geometryColName.isNull() &&
//!layerProperty.isRaster &&
( layerProperty.types.value( 0, QgsWkbTypes::Unknown ) == QgsWkbTypes::Unknown ||
layerProperty.srids.value( 0, std::numeric_limits<int>::min() ) == std::numeric_limits<int>::min() ) )
{
Expand All @@ -424,20 +424,7 @@ QVector<QgsDataItem *> QgsPGSchemaItem::createChildren()
for ( int i = 0; i < layerProperty.size(); i++ )
{
QgsDataItem *layerItem = nullptr;
if ( ! layerProperty.isRaster )
{
layerItem = createLayer( layerProperty.at( i ) );
}
else
{
const QString connInfo = conn->connInfo();
const QString uri = QStringLiteral( "PG: %1 mode=2 schema='%2' column='%3' table='%4'" )
.arg( connInfo )
.arg( layerProperty.schemaName )
.arg( layerProperty.geometryColName )
.arg( layerProperty.tableName );
layerItem = new QgsGdalLayerItem( this, layerProperty.tableName, QString(), uri );
}
layerItem = createLayer( layerProperty.at( i ) );
if ( layerItem )
items.append( layerItem );
}
Expand Down Expand Up @@ -479,7 +466,7 @@ QgsPGLayerItem *QgsPGSchemaItem::createLayer( QgsPostgresLayerProperty layerProp
}
else if ( layerProperty.isRaster )
{
tip = tr( "Raster (GDAL)" );
tip = tr( "Raster" );
}
else
{
Expand Down
27 changes: 6 additions & 21 deletions src/providers/postgres/qgspostgresproviderconnection.cpp
View file
Expand Up @@ -117,25 +117,7 @@ QString QgsPostgresProviderConnection::tableUri( const QString &schema, const QS
QgsDataSourceUri dsUri( uri() );
dsUri.setTable( name );
dsUri.setSchema( schema );
if ( tableInfo.flags().testFlag( QgsAbstractDatabaseProviderConnection::TableFlag::Raster ) )
{
const QRegularExpression removePartsRe { R"raw(\s*sql=\s*|\s*table=("[^"]+"\.?)*\s*)raw" };
if ( tableInfo.geometryColumn().isEmpty() )
{
throw QgsProviderConnectionException( QObject::tr( "Raster table '%1' in schema '%2' has no geometry column." )
.arg( name )
.arg( schema ) );
}
return QStringLiteral( "PG: %1 mode=2 schema='%2' table='%4' column='%3'" )
.arg( dsUri.uri( false ).replace( removePartsRe, QString() ) )
.arg( schema )
.arg( tableInfo.geometryColumn() )
.arg( name );
}
else
{
return dsUri.uri( false );
}
return dsUri.uri( false );
}

void QgsPostgresProviderConnection::dropVectorTable( const QString &schema, const QString &name ) const
Expand Down Expand Up @@ -295,9 +277,12 @@ QList<QVariantList> QgsPostgresProviderConnection::executeSqlPrivate( const QStr
const QVariant::Type vType { typeMap.value( colIdx, QVariant::Type::String ) };
QVariant val { res.PQgetvalue( rowIdx, colIdx ) };
// Special case for bools: 'f' and 't'
if ( vType == QVariant::Bool && ! val.isNull() )
if ( vType == QVariant::Bool )
{
val = val.toString() == 't';
if ( ! val.toString().isEmpty() )
{
val = val.toString() == 't';
}
}
else if ( val.canConvert( static_cast<int>( vType ) ) )
{
Expand Down

0 comments on commit 5af1306

Please sign in to comment.