1

postgis 2.3 postgres 9.5 64 bit windows 10.

I loaded a raster layer correctly into postgis

raster2pgsql.exe -s 3424 -I -C -M R:\Data\LUCZ_2017\ConservationPriorityAreas\data\rasters\abid -F -t 100x100 havdeel3 | psql -U postgres -d raster -h localhost -p 5432

and did a simple conversion to polygon which works correctly.

drop table if exists polys;
create table polys as
select st_polygon(rast)::geometry(multipolygon,3424) geom from havdeel3;

ALTER TABLE polys ADD COLUMN id SERIAL PRIMARY KEY;

This vector layer gets properly displayed in QGIS.

NOW I am trying to do the simplest conversion back to raster

drop table if exists raster_poly;
create table raster_poly as
select id,ST_AsRaster(geom, 50,50,'4BUI') rast from polys;

The table correctly displays but when I try and display it in QGIS it gives me this error

2018-07-10T15:03:35 WARNING  : 
2018-07-10T15:04:47 CRITICAL    Layer is not valid : The layer dbname='scrap' host=localhost port=5432 user='postgres' password='--' sslmode=disable key='r_table_catalog' srid=3424 type=Polygon table="public"."raster_columns" (extent) sql= is not a valid layer and can not be added to the map. Reason: 
2018-07-10T15:05:16 WARNING  : 
2018-07-10T15:11:18 WARNING  : 
2018-07-10T15:11:25 INFO     : Connection to scrap was successful.
2018-07-10T15:12:37 WARNING  : 
2018-07-10T15:13:41 WARNING  : 
2018-07-10T15:17:27 WARNING  : 
2018-07-10T15:20:58 WARNING  : 

I have seen this tutorial http://geospatialelucubrations.blogspot.com/2014/05/a-guide-to-rasterization-of-vector.html but it is not so helpful.

enter image description here

Seems to be a similar question to this List of vector to one raster in PostGIS which was never answered.

I also have tried the answer to this question with my problem and I still get the same error ST_AsRaster for polygon

Reworked query that doesn't work

select id,ST_AsRaster(st_transform(geom,3424), 100,100,ARRAY['8BUI'], 
                  ARRAY[118]) rast from polys

or this doesn't work either

select id,ST_AsRaster(st_buffer(geom,0), 100,100,ARRAY['8BUI'], 
                  ARRAY[118]) rast from polys

But how does this work?

SELECT ST_AsRaster(ST_Buffer(ST_GeomFromText('POLYGON((-30 40, -20 30, -25 20, -23 10, -30 40))', 4326), 50),100,100,ARRAY['8BUI'],ARRAY[118]);
  • What if you do SELECT DISTINCT ST_SRID(ST_AsRaster(geom, 50,50,'4BUI')) FROM polys; How many different SRID do you get? – Pierre Racine Jul 11 '18 at 13:57
  • I think the error message is misleading. It's probably not a problem of SRID but a problem of alignment. You must use the other variant of ST_AsRaster() to make sure all rasters are all aligned identically. – Pierre Racine Jul 11 '18 at 14:05
0

so I do not know why this worked but I used st_dump to break down the vector multipolygons and the conversion back to raster worked and I am able to display it in QGIS

drop table if exists polys;
create table polys as
with poly as(select st_polygon(rast)::geometry(multipolygon,3424) geom 
        from havdeel3
        )
select (st_dump(geom)).geom geom from poly;

ALTER TABLE polys ADD COLUMN id SERIAL PRIMARY KEY;

drop table if exists raster_poly;
create table raster_poly as
select id,ST_AsRaster(geom, 100,100,ARRAY['8BUI'], 
                  ARRAY[118]) rast from polys;

However, the raster does not perfectly match up with the vector layer polys it was formed from, which is something I will ask in a new question

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.