I'm playing with a few different options of storing raster data via PostGIS to visualize (eventually in a web app).
The out-db or out-of-database storage seems the best option, but I can't seem to get it to work outside of PostGIS. Functions within PostGIS (like ST_Histogram) output fine results, but taking it outside fails.
First I tried MapServer since that's how I eventually want to handle it.
LAYER
NAME test
TYPE raster
STATUS ON
DATA "PG:host=website.com port=1234
dbname='raster_dev' user='username' password='password'
schema='public' table='rasters_outdb' where='\'rid\'=1'"
PROJECTION
"init=epsg:3310"
END
PROCESSING "NODATA=252"
PROCESSING "RESAMPLE=NEAREST"
END
Results in this error:
msDrawMap(): Image handling error. Failed to draw layer named 'test'. drawGDAL(): Unable to access file. GDALDatasetRasterIO() failed: IReadBlock failed at X offset 0, Y offset 0
If I change table to rasters_indb (the in-db storage option), the map/png loads fine.
So I tried loading it up in QGIS. Again, in-db works fine. Out-db, I get a blank, black raster, with nan for min/max values -- however it does seem to occupy the right location and size. Progress?
I did a lot of googling and I seem to get mixed results on whether it works, whether GDAL can handle out-db or not, and most of it outdated. So I'm not sure where I stand here. Am I trying something that just isn't supported yet or am I doing something above wrong?
I'm on QGIS 2.12, MapServer 7, PostgreSQL 9.4, PostGIS 2.1
1 Answer 1
I have had success reading vectors from Postgis. I haven't tried Raster access, but maybe some of this will help? For starters, check out PostGIS Support in MapServer
I dont have access to your database/schema and I've never stored a raster in Postgis... but you could try something like this:
LAYER
NAME test
TYPE raster
STATUS ON
CONNECTIONTYPE POSTGIS
CONNECTION "host=website.com port=1234 user=username dbname=raster_dev password=password"
# Turn on Mapserver Connection pool for performance
# http://blog.cleverelephant.ca/2008/10/mapserverpostgis-performance-tips.html
PROCESSING "CLOSE_CONNECTION=DEFER"
DATA "SELECT table.rgb_bytes FROM table WHERE table.id=1 and ST_Intersects(table.geom, !BOX!)"
PROJECTION
"init=epsg:3310"
END
PROCESSING "NODATA=252"
PROCESSING "RESAMPLE=NEAREST"
END
If your Postgis database acts as a tileindex, create a LAYER with TYPE vector and your query in DATA includes location, the path to the image on disk.
LAYER
NAME tindex_layer
TYPE vector
STATUS OFF
CONNECTIONTYPE POSTGIS
CONNECTION "host=website.com port=1234 user=username dbname=raster_dev password=password"
PROCESSING "CLOSE_CONNECTION=DEFER"
DATA "geom FROM (SELECT geom,id,location FROM table WHERE ST_Intersects(geom, !BOX!) AS subquery USING UNIQUE id USING SRID=4326
END
Then you make another raster layer with the tileindex, like so:
LAYER
NAME raster_layer
TYPE raster
STATUS ON
TILEINDEX tindex_layer
END