Skip to content

Commit

Permalink
Browse files Browse the repository at this point in the history
PG raster: simplify tests data
  • Loading branch information
elpaso committed Apr 6, 2020
1 parent dccfe5e commit 0f2a70a
Show file tree
Hide file tree
Showing 9 changed files with 47 additions and 77 deletions.
31 changes: 15 additions & 16 deletions tests/src/python/test_provider_postgresraster.py
Expand Up @@ -74,7 +74,6 @@ def setUpClass(cls):
cls._load_test_table('public', 'raster_3035_tiled_composite_pk')
cls._load_test_table('public', 'raster_3035_untiled_multiple_rows')
cls._load_test_table('idro', 'cosmo_i5_snow', 'bug_34823_pg_raster')
cls._load_test_table('public', 'raster_4326_time')

# Fix timing issues in backend
#time.sleep(1)
Expand Down Expand Up @@ -323,48 +322,48 @@ def testTime(self):
def _test_block(rl, expected_block, expected_single):

self.assertTrue(rl.isValid())
block = rl.dataProvider().block(2, rl.extent(), 2, 2)
block = rl.dataProvider().block(1, rl.extent(), 2, 2)
data = []
for i in range(2):
for j in range(2):
data.append(int(block.value(i, j)))
self.assertEqual(data, expected_block)

block = rl.dataProvider().block(2, rl.extent(), 1, 1)
self.assertEqual(block.value(0, 0), expected_single)
block = rl.dataProvider().block(1, rl.extent(), 1, 1)
self.assertEqual(int(block.value(0, 0)), 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')
rl = QgsRasterLayer(self.dbconn + " sslmode=disable table={table} schema={schema} temporalDefaultTime='2020-04-01T00:00:00' temporalFieldIndex='1'".format(table='raster_3035_untiled_multiple_rows', schema='public'), 'pg_layer', 'postgresraster')
self.assertEqual(rl.subsetString(), "")

_test_block(rl, [161, 218, 113, 142], 226)
_test_block(rl, [136, 142, 145, 153], 153)

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')
rl = QgsRasterLayer(self.dbconn + " sslmode=disable table={table} schema={schema} temporalDefaultTime='2020-04-05T00:00:00' temporalFieldIndex='1'".format(table='raster_3035_untiled_multiple_rows', schema='public'), 'pg_layer', 'postgresraster')
self.assertEqual(rl.subsetString(), "")

_test_block(rl, [227, 254, 179, 206], 254)
_test_block(rl, [136, 142, 161, 169], 169)

# 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')
rl = QgsRasterLayer(self.dbconn + " sslmode=disable table={table} schema={schema} sql=\"data\" = '2020-04-01'".format(table='raster_3035_untiled_multiple_rows', schema='public'), 'pg_layer', 'postgresraster')
self.assertEqual(rl.subsetString(), '"data" = \'2020-04-01\'')

_test_block(rl, [161, 218, 113, 142], 226)
_test_block(rl, [136, 142, 145, 153], 153)

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')
rl = QgsRasterLayer(self.dbconn + " sslmode=disable table={table} schema={schema} sql=\"data\" = '2020-04-05'".format(table='raster_3035_untiled_multiple_rows', schema='public'), 'pg_layer', 'postgresraster')
self.assertEqual(rl.subsetString(), '"data" = \'2020-04-05\'')

_test_block(rl, [227, 254, 179, 206], 254)
_test_block(rl, [136, 142, 161, 169], 169)

# 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')
rl = QgsRasterLayer(self.dbconn + " sslmode=disable table={table} schema={schema} temporalDefaultTime='2020-04-01T00:00:00' temporalFieldIndex='2'".format(table='raster_3035_untiled_multiple_rows', schema='public'), 'pg_layer', 'postgresraster')
self.assertEqual(rl.subsetString(), '')

_test_block(rl, [161, 218, 113, 142], 226)
_test_block(rl, [136, 142, 145, 153], 153)

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')
rl = QgsRasterLayer(self.dbconn + " sslmode=disable table={table} schema={schema} temporalDefaultTime='2020-04-05T00:00:00' temporalFieldIndex='2'".format(table='raster_3035_untiled_multiple_rows', schema='public'), 'pg_layer', 'postgresraster')
self.assertEqual(rl.subsetString(), '')

_test_block(rl, [227, 254, 179, 206], 254)
_test_block(rl, [136, 142, 161, 169], 169)


if __name__ == '__main__':
Expand Down
@@ -1,6 +1,8 @@

CREATE SCHEMA IF NOT EXISTS idro;

DROP TABLE IF EXISTS idro.cosmo_i5_snow CASCADE;

--
-- Name: cosmo_i5_snow; Type: TABLE; Schema: idro; Owner: -
--
Expand Down
Expand Up @@ -2,6 +2,9 @@
--
-- in-db float 32 raster with no constraints
--

DROP TABLE IF EXISTS "public"."raster_3035_no_constraints" CASCADE;

CREATE TABLE "raster_3035_no_constraints" ("rid" serial PRIMARY KEY,"rast" raster);
INSERT INTO "raster_3035_no_constraints" ("rast") VALUES ('0100000100000000000000394000000000000039C000000000D9204F41000000008F8B424100000000000000000000000000000000DB0B0000060005004A003C1CC66A610843880B0E431CC2194306342543B7633C43861858436E0A1143BBAD194359612743A12B334317BE4343DECE59432B621B43F0E42843132B3843AC824043E6CF48436E465A435C4D2D430FA63D43F87A4843B5494A4349454E4374F35B43906E41433AB54C43B056504358575243B1EC574322615F43'::raster);

Expand Down
Expand Up @@ -2,6 +2,7 @@
--
-- in-db float32 tiled raster with composite PK
--
DROP TABLE IF EXISTS "public"."raster_3035_tiled_composite_pk" CASCADE;

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")
Expand Down
Expand Up @@ -2,6 +2,10 @@
--
-- in-db float32 tiled raster with no overviews (for WHERE testing)
--

DROP TABLE IF EXISTS "public"."raster_3035_tiled_no_overviews" CASCADE;


CREATE TABLE "public"."raster_3035_tiled_no_overviews" ("rid" serial PRIMARY KEY,"rast" raster,"category" text);
INSERT INTO "public"."raster_3035_tiled_no_overviews" ("rast","category") VALUES ('0100000100000000000000394000000000000039C000000000D9204F41000000008F8B424100000000000000000000000000000000DB0B0000020002004A003C1CC66A610843880B0E436E0A1143BBAD1943'::raster,'cat1');
INSERT INTO "public"."raster_3035_tiled_no_overviews" ("rast","category") VALUES ('0100000100000000000000394000000000000039C000000000F2204F41000000008F8B424100000000000000000000000000000000DB0B0000020002004A003C1CC61CC219430634254359612743A12B3343'::raster,'cat1');
Expand Down
Expand Up @@ -3,6 +3,9 @@
-- in-db float32 tiled raster with no overviews and no PK
--

DROP TABLE IF EXISTS "public"."raster_3035_tiled_no_pk" CASCADE;


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');
Expand Down
@@ -1,19 +1,25 @@
--
-- in-db float32 untiled raster with multiple rows
-- also used to test temporal capabilities
--

CREATE TABLE "public"."raster_3035_untiled_multiple_rows" ("pk" SERIAL PRIMARY KEY, "rast" raster
DROP TABLE IF EXISTS "public"."raster_3035_untiled_multiple_rows";

CREATE TABLE "public"."raster_3035_untiled_multiple_rows" (
"pk" SERIAL PRIMARY KEY,
"rast" raster,
"data" timestamp,
"data_text" varchar
);
INSERT INTO "public"."raster_3035_untiled_multiple_rows" ("rast", "pk")
VALUES ('0100000100000000000000394000000000000039C000000000D9204F41000000008F8B424100000000000000000000000000000000DB0B0000020002004A003C1CC66A610843880B0E436E0A1143BBAD1943'::raster, 1);
INSERT INTO "public"."raster_3035_untiled_multiple_rows" ("rast", "pk")
VALUES ('0100000100000000000000394000000000000039C000000000D9204F41000000008F8B424100000000000000000000000000000000DB0B0000020002004A003C1CC66A610843880B0E436E0A2143BBAD2943'::raster, 2);

INSERT INTO "public"."raster_3035_untiled_multiple_rows" ("rast", "pk", "data", "data_text")
VALUES ('0100000100000000000000394000000000000039C000000000D9204F41000000008F8B424100000000000000000000000000000000DB0B0000020002004A003C1CC66A610843880B0E436E0A1143BBAD1943'::raster, 1, '2020-04-01', '2020-04-01');
INSERT INTO "public"."raster_3035_untiled_multiple_rows" ("rast", "pk", "data", "data_text")
VALUES ('0100000100000000000000394000000000000039C000000000D9204F41000000008F8B424100000000000000000000000000000000DB0B0000020002004A003C1CC66A610843880B0E436E0A2143BBAD2943'::raster, 2, '2020-04-05', '2020-04-05');
-- offset row
INSERT INTO "public"."raster_3035_untiled_multiple_rows" ("rast", "pk")
VALUES ('0100000100000000000000394000000000000039C000000000D9204F41000000008F8B424100000000000000000000000000000000DB0B0000020002004A003C1CC66A610843880B0E436E0A2143BBAD2943'::raster, 3);
INSERT INTO "public"."raster_3035_untiled_multiple_rows" ("rast", "pk", "data", "data_text")
VALUES ('0100000100000000000000394000000000000039C000000000D9204F41000000008F8B424100000000000000000000000000000000DB0B0000020002004A003C1CC66A610843880B0E436E0A2143BBAD0942'::raster, 3, '2020-04-06', '2020-04-06');

CREATE INDEX ON "public"."raster_3035_untiled_multiple_rows" USING gist (st_convexhull("rast"));
ANALYZE "public"."raster_3035_untiled_multiple_rows";
SELECT AddRasterConstraints('public','raster_3035_untiled_multiple_rows','rast',TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE);

53 changes: 0 additions & 53 deletions tests/testdata/provider/postgresraster/raster_4326_time.sql

This file was deleted.

5 changes: 5 additions & 0 deletions tests/testdata/provider/postgresraster/raster_tiled_3035.sql
@@ -1,6 +1,11 @@
--
-- in-db 1 band float 32 raster
--

DROP TABLE IF EXISTS "public"."raster_tiled_3035" CASCADE;
DROP TABLE IF EXISTS "o_2_raster_tiled_3035" CASCADE;
DROP TABLE IF EXISTS "o_4_raster_tiled_3035" CASCADE;

CREATE TABLE "raster_tiled_3035" ("rid" serial PRIMARY KEY,"rast" raster,"filename" text);
CREATE TABLE "o_2_raster_tiled_3035" ("rid" serial PRIMARY KEY,"rast" raster,"filename" text);
CREATE TABLE "o_4_raster_tiled_3035" ("rid" serial PRIMARY KEY,"rast" raster,"filename" text);
Expand Down

0 comments on commit 0f2a70a

Please sign in to comment.