Skip to content

Commit

Permalink
PG raster support composite PKs
Browse files Browse the repository at this point in the history
  • Loading branch information
@elpaso
elpaso committed Jan 13, 2020
1 parent 555c6c2 commit 483ed77
Show file tree
Hide file tree
Showing 6 changed files with 76 additions and 17 deletions.
21 changes: 14 additions & 7 deletions src/providers/postgres/raster/qgspostgresrasterprovider.cpp
View file
Expand Up @@ -394,12 +394,12 @@ bool QgsPostgresRasterProvider::readBlock( int bandNo, const QgsRectangle &viewE
"FROM %2 WHERE %4 %3 IN " )
.arg( quotedIdentifier( mRasterColumn ) )
.arg( tableToQuery )
.arg( quotedIdentifier( mPrimaryKeyAttrs.first() ) )
.arg( quotedIdentifier( pkSql() ) )
.arg( whereAnd )};

const QString indexSql { QStringLiteral( "SELECT %1, (ST_Metadata( %2 )).* FROM %3 "
"WHERE %6 %2 && ST_GeomFromText( %5, %4 )" )
.arg( quotedIdentifier( mPrimaryKeyAttrs.first() ) )
.arg( quotedIdentifier( pkSql() ) )
.arg( quotedIdentifier( mRasterColumn ) )
.arg( tableToQuery )
.arg( mCrs.postgisSrid() )
Expand Down Expand Up @@ -1320,15 +1320,12 @@ bool QgsPostgresRasterProvider::determinePrimaryKey()
QgsMessageLog::logMessage( tr( "Could not find a primary key for PostGIS raster table %1" ).arg( mQuery ), tr( "PostGIS" ) );
mPrimaryKeyType = PktUnknown;
}
else if ( mPrimaryKeyAttrs.size() != 1 )
{
QgsMessageLog::logMessage( tr( "Multiple keys are not supported for PostGIS rasters table %1" ).arg( mQuery ), tr( "PostGIS" ) );
mPrimaryKeyType = PktUnknown;
}

return mPrimaryKeyType != PktUnknown;
}



void QgsPostgresRasterProvider::determinePrimaryKeyFromUriKeyColumn()
{
mPrimaryKeyAttrs.clear();
Expand Down Expand Up @@ -1361,6 +1358,16 @@ void QgsPostgresRasterProvider::determinePrimaryKeyFromUriKeyColumn()
}
}

QString QgsPostgresRasterProvider::pkSql()
{
Q_ASSERT( ! mPrimaryKeyAttrs.isEmpty() );
if ( mPrimaryKeyAttrs.count( ) > 1 )
{
return mPrimaryKeyAttrs.join( ',' ).prepend( '(' ).append( ')' );
}
return mPrimaryKeyAttrs.first();
}


void QgsPostgresRasterProvider::findOverviews()
{
Expand Down
5 changes: 5 additions & 0 deletions src/providers/postgres/raster/qgspostgresrasterprovider.h
View file
Expand Up @@ -178,6 +178,11 @@ class QgsPostgresRasterProvider : public QgsRasterDataProvider
*/
void determinePrimaryKeyFromUriKeyColumn();

/**
* Returns the SQL frament to retrieve the PK from the raster table
*/
QString pkSql();

};

struct QgsPostgresRasterProviderException: public std::exception
Expand Down
13 changes: 6 additions & 7 deletions src/providers/postgres/raster/qgspostgresrastershareddata.cpp
View file
Expand Up @@ -123,12 +123,11 @@ QgsPostgresRasterSharedData::TilesResponse QgsPostgresRasterSharedData::tiles( c

for ( int row = 0; row < dataResult.PQntuples(); ++row )
{
bool ok;
// Note: if we change tile id type we need to sync this
const int tileId { dataResult.PQgetvalue( row, 0 ).toInt( &ok ) };
if ( ! ok )
const TileIdType tileId { dataResult.PQgetvalue( row, 0 ) };
if ( tileId.isEmpty() )
{
QgsMessageLog::logMessage( QObject::tr( "TileID (%1) could not be converted to integer while fetching tile data from backend.\nSQL: %2" )
QgsMessageLog::logMessage( QObject::tr( "TileID (%1) is empty while fetching tile data from backend.\nSQL: %2" )
.arg( dataResult.PQgetvalue( row, 0 ) )
.arg( sql ), QObject::tr( "PostGIS" ), Qgis::Critical );
}
Expand Down Expand Up @@ -170,7 +169,7 @@ QgsPostgresRasterSharedData::TilesResponse QgsPostgresRasterSharedData::tiles( c
}


QgsPostgresRasterSharedData::Tile const *QgsPostgresRasterSharedData::setTileData( unsigned int overviewFactor, int tileId, const QByteArray &data )
QgsPostgresRasterSharedData::Tile const *QgsPostgresRasterSharedData::setTileData( unsigned int overviewFactor, TileIdType tileId, const QByteArray &data )
{
Q_ASSERT( ! data.isEmpty() );
if ( mTiles.find( overviewFactor ) == mTiles.end() ||
Expand Down Expand Up @@ -203,7 +202,7 @@ bool QgsPostgresRasterSharedData::fetchTilesIndex( const QgsGeometry &requestPol
for ( int i = 0; i < result.PQntuples(); ++i )
{
// rid | upperleftx | upperlefty | width | height | scalex | scaley | skewx | skewy | srid | numbands
const TileIdType tileId { result.PQgetvalue( i, 0 ).toInt( ) };
const TileIdType tileId { result.PQgetvalue( i, 0 ) };
const double upperleftx { result.PQgetvalue( i, 1 ).toDouble() };
const double upperlefty { result.PQgetvalue( i, 2 ).toDouble() };
const long int tileWidth { result.PQgetvalue( i, 3 ).toLong( ) };
Expand Down Expand Up @@ -246,7 +245,7 @@ bool QgsPostgresRasterSharedData::fetchTilesIndex( const QgsGeometry &requestPol
return true;
}

QgsPostgresRasterSharedData::Tile::Tile( int tileId, int srid, QgsRectangle extent, double upperLeftX, double upperLeftY, long width, long height, double scaleX, double scaleY, double skewX, double skewY, int numBands )
QgsPostgresRasterSharedData::Tile::Tile( const QgsPostgresRasterSharedData::TileIdType tileId, int srid, QgsRectangle extent, double upperLeftX, double upperLeftY, long width, long height, double scaleX, double scaleY, double skewX, double skewY, int numBands )
: tileId( tileId )
, srid( srid )
, extent( extent )
Expand Down
6 changes: 3 additions & 3 deletions src/providers/postgres/raster/qgspostgresrastershareddata.h
View file
Expand Up @@ -36,7 +36,7 @@ class QgsPostgresRasterSharedData
public:

//! Type for tile IDs, must be in sync with DB tile id extraction logic
using TileIdType = int;
using TileIdType = QString;

//! Tile data and metadata for a single band
struct TileBand
Expand Down Expand Up @@ -137,14 +137,14 @@ class QgsPostgresRasterSharedData

bool fetchTilesData( unsigned int overviewFactor, const QList<TileIdType> &tileIds );
bool fetchTilesIndex( const QgsGeometry &requestPolygon, const TilesRequest &request );
Tile const *setTileData( unsigned int overviewFactor, int tileId, const QByteArray &data );
Tile const *setTileData( unsigned int overviewFactor, TileIdType tileId, const QByteArray &data );

// Note: cannot be a smart pointer because spatial index cannot be copied
//! Tile caches, index is the overview factor (1 is the full resolution data)
std::map<unsigned int, QgsGenericSpatialIndex<Tile>*> mSpatialIndexes;

//! Memory manager for owned tiles (and for tileId access)
std::map<unsigned int, std::map< TileIdType, std::unique_ptr<Tile>>> mTiles;
std::map<unsigned int, std::map<TileIdType, std::unique_ptr<Tile>>> mTiles;

//! Keeps track of loaded index bounds
std::map<unsigned int, QgsGeometry> mLoadedIndexBounds;
Expand Down
14 changes: 14 additions & 0 deletions tests/src/python/test_provider_postgresraster.py
View file
Expand Up @@ -127,6 +127,20 @@ def testWhereCondition(self):

self.assertAlmostEqual(rl.dataProvider().identify(rl.extent().center(), QgsRaster.IdentifyFormatValue).results()[1], 223.38, 2)

def testNoPk(self):
"""Read raster with no PK"""

rl = QgsRasterLayer(self.dbconn + ' sslmode=disable srid=3035 table="public"."raster_3035_tiled_no_pk"' +
'sql=', 'test', 'postgresraster')
self.assertTrue(rl.isValid())

def testCompositeKey(self):
"""Read raster with composite pks"""

rl = QgsRasterLayer(self.dbconn + ' sslmode=disable srid=3035 table="public"."raster_3035_tiled_composite_pk"' +
'sql=', 'test', 'postgresraster')
self.assertTrue(rl.isValid())


if __name__ == '__main__':
unittest.main()
34 changes: 34 additions & 0 deletions tests/testdata/provider/testdata_pg_raster.sql
View file
Expand Up @@ -65,3 +65,37 @@ CREATE INDEX ON "public"."raster_3035_tiled_no_overviews" USING gist (st_convexh
ANALYZE "public"."raster_3035_tiled_no_overviews";
SELECT AddRasterConstraints('public','raster_3035_tiled_no_overviews','rast',TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE);

-- in-db float32 tiled raster with no overviews and no PK

CREATE TABLE "public"."raster_3035_tiled_no_pk" ("rast" raster,"category" text);
INSERT INTO "public"."raster_3035_tiled_no_pk" ("rast","category") VALUES ('0100000100000000000000394000000000000039C000000000D9204F41000000008F8B424100000000000000000000000000000000DB0B0000020002004A003C1CC66A610843880B0E436E0A1143BBAD1943'::raster,'cat1');
INSERT INTO "public"."raster_3035_tiled_no_pk" ("rast","category") VALUES ('0100000100000000000000394000000000000039C000000000F2204F41000000008F8B424100000000000000000000000000000000DB0B0000020002004A003C1CC61CC219430634254359612743A12B3343'::raster,'cat1');
INSERT INTO "public"."raster_3035_tiled_no_pk" ("rast","category") VALUES ('0100000100000000000000394000000000000039C0000000000B214F41000000008F8B424100000000000000000000000000000000DB0B0000020002004A003C1CC6B7633C438618584317BE4343DECE5943'::raster,'cat1');
INSERT INTO "public"."raster_3035_tiled_no_pk" ("rast","category") VALUES ('0100000100000000000000394000000000000039C000000000D9204F4100000000768B424100000000000000000000000000000000DB0B0000020002004A003C1CC62B621B43F0E428435C4D2D430FA63D43'::raster,'cat1');
INSERT INTO "public"."raster_3035_tiled_no_pk" ("rast","category") VALUES ('0100000100000000000000394000000000000039C000000000F2204F4100000000768B424100000000000000000000000000000000DB0B0000020002004A003C1CC6132B3843AC824043F87A4843B5494A43'::raster,'cat1');
INSERT INTO "public"."raster_3035_tiled_no_pk" ("rast","category") VALUES ('0100000100000000000000394000000000000039C0000000000B214F4100000000768B424100000000000000000000000000000000DB0B0000020002004A003C1CC6E6CF48436E465A4349454E4374F35B43'::raster,'cat1');
INSERT INTO "public"."raster_3035_tiled_no_pk" ("rast","category") VALUES ('0100000100000000000000394000000000000039C000000000D9204F41000000005D8B424100000000000000000000000000000000DB0B0000020001004A003C1CC6906E41433AB54C43'::raster,'cat1');
INSERT INTO "public"."raster_3035_tiled_no_pk" ("rast","category") VALUES ('0100000100000000000000394000000000000039C000000000F2204F41000000005D8B424100000000000000000000000000000000DB0B0000020001004A003C1CC6B056504358575243'::raster,'cat1');
INSERT INTO "public"."raster_3035_tiled_no_pk" ("rast","category") VALUES ('0100000100000000000000394000000000000039C0000000000B214F41000000005D8B424100000000000000000000000000000000DB0B0000020001004A003C1CC6B1EC574322615F43'::raster,'cat2');
CREATE INDEX ON "public"."raster_3035_tiled_no_pk" USING gist (st_convexhull("rast"));
ANALYZE "public"."raster_3035_tiled_no_pk";
SELECT AddRasterConstraints('public','raster_3035_tiled_no_pk','rast',TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE);

-- in-db float32 tiled raster with composite PK

CREATE TABLE "public"."raster_3035_tiled_composite_pk" ("pk1" INT NOT NULL, "pk2" INT NOT NULL, "rast" raster,"category" text,
CONSTRAINT unique_pk UNIQUE ("pk1", "pk2")
);
INSERT INTO "public"."raster_3035_tiled_composite_pk" ("rast","category", "pk1", "pk2") VALUES ('0100000100000000000000394000000000000039C000000000D9204F41000000008F8B424100000000000000000000000000000000DB0B0000020002004A003C1CC66A610843880B0E436E0A1143BBAD1943'::raster,'cat1', 1, 1);
INSERT INTO "public"."raster_3035_tiled_composite_pk" ("rast","category", "pk1", "pk2") VALUES ('0100000100000000000000394000000000000039C000000000F2204F41000000008F8B424100000000000000000000000000000000DB0B0000020002004A003C1CC61CC219430634254359612743A12B3343'::raster,'cat1', 1, 2);
INSERT INTO "public"."raster_3035_tiled_composite_pk" ("rast","category", "pk1", "pk2") VALUES ('0100000100000000000000394000000000000039C0000000000B214F41000000008F8B424100000000000000000000000000000000DB0B0000020002004A003C1CC6B7633C438618584317BE4343DECE5943'::raster,'cat1', 1, 3);
INSERT INTO "public"."raster_3035_tiled_composite_pk" ("rast","category", "pk1", "pk2") VALUES ('0100000100000000000000394000000000000039C000000000D9204F4100000000768B424100000000000000000000000000000000DB0B0000020002004A003C1CC62B621B43F0E428435C4D2D430FA63D43'::raster,'cat1', 2, 1);
INSERT INTO "public"."raster_3035_tiled_composite_pk" ("rast","category", "pk1", "pk2") VALUES ('0100000100000000000000394000000000000039C000000000F2204F4100000000768B424100000000000000000000000000000000DB0B0000020002004A003C1CC6132B3843AC824043F87A4843B5494A43'::raster,'cat1', 2, 2);
INSERT INTO "public"."raster_3035_tiled_composite_pk" ("rast","category", "pk1", "pk2") VALUES ('0100000100000000000000394000000000000039C0000000000B214F4100000000768B424100000000000000000000000000000000DB0B0000020002004A003C1CC6E6CF48436E465A4349454E4374F35B43'::raster,'cat1', 2, 3);
INSERT INTO "public"."raster_3035_tiled_composite_pk" ("rast","category", "pk1", "pk2") VALUES ('0100000100000000000000394000000000000039C000000000D9204F41000000005D8B424100000000000000000000000000000000DB0B0000020001004A003C1CC6906E41433AB54C43'::raster,'cat1', 3, 1);
INSERT INTO "public"."raster_3035_tiled_composite_pk" ("rast","category", "pk1", "pk2") VALUES ('0100000100000000000000394000000000000039C000000000F2204F41000000005D8B424100000000000000000000000000000000DB0B0000020001004A003C1CC6B056504358575243'::raster,'cat1', 3, 1);
INSERT INTO "public"."raster_3035_tiled_composite_pk" ("rast","category", "pk1", "pk2") VALUES ('0100000100000000000000394000000000000039C0000000000B214F41000000005D8B424100000000000000000000000000000000DB0B0000020001004A003C1CC6B1EC574322615F43'::raster,'cat2', 3, 3);
CREATE INDEX ON "public"."raster_3035_tiled_composite_pk" USING gist (st_convexhull("rast"));
ANALYZE "public"."raster_3035_tiled_composite_pk";
SELECT AddRasterConstraints('public','raster_3035_tiled_composite_pk','rast',TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE);

0 comments on commit 483ed77

Please sign in to comment.