I have squared and imported a raster (see for details on the file) in PostgreSQL/PostGIS using raster2pgpsql
:
raster2pgsql -c -F -I -C -r -M -t 1000x1000 contours.dem public.tiles | psql -d postgres -U jlandercy
Processing 1/1: contours.dem
BEGIN
CREATE TABLE
INSERT 0 1
[...]
INSERT 0 1
CREATE INDEX
ANALYZE
NOTICE: Adding SRID constraint
NOTICE: Adding scale-X constraint
NOTICE: Adding scale-Y constraint
NOTICE: Adding blocksize-X constraint
NOTICE: Adding blocksize-Y constraint
NOTICE: Adding alignment constraint
NOTICE: Adding coverage tile constraint required for regular blocking
NOTICE: Adding spatially unique constraint required for regular blocking
NOTICE: Adding number of bands constraint
NOTICE: Adding pixel type constraint
NOTICE: Adding nodata value constraint
NOTICE: Adding out-of-database constraint
NOTICE: Adding maximum extent constraint
----------------------
t
(1 row)
COMMIT
VACUUM
There is no complaint during the insert, but I cannot see the layer in explorer.
Instead in DB Manager I can see the layer, but it makes QGis crash when I try to display it.
I have unique identifier rid
, all 12 constraints have been built, the raster coordinate system is set. DB Manager is able to fetch all metadata:
But I cannot succeed in displaying the raster. Each time I try to add to canvas, QGis hangs (normal) and finally crashes (unexpected) without error message.
Is there a problem with my import or data storage?
Or must I investigate towards PostgreSQL configuration? (Each of the 529 rows weights about 4Mo).
I cannot also import just one tile using filter query in DBManager:
Load button makes some work, but there is no layer created.
Anyway, if I import the file directly, I can see the raster without any problem.
I have the same problem if I simplify the raster and insert it in one piece.
What am I missing to make it work?
Additional information
\d tiles
Table "public.tiles"
Column | Type | Modifiers
----------+---------+-----------------------------------------------------
rid | integer | not null default nextval('tiles_rid_seq'::regclass)
rast | raster |
filename | text |
Indexes:
"tiles_pkey" PRIMARY KEY, btree (rid)
"enforce_spatially_unique_tiles_rast" EXCLUDE USING btree ((rast::geometry) WITH =)
"tiles_st_convexhull_idx" gist (st_convexhull(rast))
Check constraints:
"enforce_coverage_tile_rast" CHECK (st_iscoveragetile(rast, '0100000000000000000000F03F000000000000F0BF3BD576D4A6DB0041595214C6B71F0641000000000000000000000000000000008A7A00001A59BE58'::raster, 1000, 1000))
"enforce_height_rast" CHECK (st_height(rast) = ANY (ARRAY[1000, 718]))
"enforce_max_extent_rast" CHECK (st_envelope(rast) @ '01030000208A7A000001000000050000003BD576D4A6DB0041595214C6C75903413BD576D4A6DB0041595214C6B71F06413BD576D476A40341595214C6B71F06413BD576D476A40341595214C6C75903413BD576D4A6DB0041595214C6C7590341'::geometry)
"enforce_nodata_values_rast" CHECK (_raster_constraint_nodata_values(rast) = '{-340282306073710000000000000000000000000.0000000000}'::numeric[])
"enforce_num_bands_rast" CHECK (st_numbands(rast) = 1)
"enforce_out_db_rast" CHECK (_raster_constraint_out_db(rast) = '{f}'::boolean[])
"enforce_pixel_types_rast" CHECK (_raster_constraint_pixel_types(rast) = '{32BF}'::text[])
"enforce_same_alignment_rast" CHECK (st_samealignment(rast, '0100000000000000000000F03F000000000000F0BF3BD576D4A6DB0041595214C6B71F0641000000000000000000000000000000008A7A000001000100'::raster))
"enforce_scalex_rast" CHECK (round(st_scalex(rast)::numeric, 10) = round(1::numeric, 10))
"enforce_scaley_rast" CHECK (round(st_scaley(rast)::numeric, 10) = round(- 1::numeric, 10))
"enforce_srid_rast" CHECK (st_srid(rast) = 31370)
"enforce_width_rast" CHECK (st_width(rast) = ANY (ARRAY[1000, 810]))
View raster_columns
contains:
airproject=# SELECT * FROM raster_columns
;
-[ RECORD 1 ]----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
r_table_catalog | airproject
r_table_schema | public
r_table_name | tiles
r_raster_column | rast
srid | 31370
scale_x | 1
scale_y | -1
blocksize_x | 1000
blocksize_y | 1000
same_alignment | t
regular_blocking | f
num_bands | 1
pixel_types | {32BF}
nodata_values | {-3.4028230607371e+38}
out_db | {f}
extent | 01030000208A7A000001000000050000003BD576D4A6DB0041595214C6C75903413BD576D4A6DB0041595214C6B71F06413BD576D476A40341595214C6B71F06413BD576D476A40341595214C6C75903413BD576D4A6DB0041595214C6C7590341
spatial_index | t
View raster_overviews
is empty.
Best Answer
It seems QGis has a different mechanism for raster management. I am now using v3.4 LTS (w/ Postgres 10.1 and Postgis 2.4), and the issue is still present. I can say it is at the QGis side the issue resides.
It looks like raster import mechanism lacks somehow connection string to database. This is why rasters are not shown in PostgreSQL sources and we have difficulties to add this kind of layers to canvas.
Solution provided by
@Marvin
works for QGis files, but it is a bit tricky and if you aim to store your project into PostgreSQL you will have to modify theByteA
field which is definitly not a good option.But wait, there is a hack to circonvolve this issue:
Tested with DEM and Orthophoto, it is just about remind QGis for connection credentials.