[GIS] QGIS cannot view raster loaded to PostGIS through raster2pgsql

postgispostgresqlqgisraster

I tried to load a raster GeoTIFF image to a PostGIS database using "raster2pgsql" following the OpenGeo Suite Manual. The command I used is:

C:\>raster2pgsql -s 4283 -I -C -M "C:\Program Files\GeoServer 2.2.5\data_dir\data\gcur\interp_clipped.tif" -F public.myraster | psql -d MyPostGISDB -h localhost -p 5432 -U postgres

The table was successfully created and found in "pgAdmin III" but not accessible through QGIS. When I tried to load PostGIS layers in QGIS, it only showed "raster_columns" and not the "myraster" table. In the PostGIS Manager window in QGIS, this table is marked as plain table and with "No Geometry".

Can anyone give me some hints about it?

Best Answer

Do not use the regular "Add PostGIS Layers", it will not show your raster table. The plugin "Load Postgis Raster to QGIS" was not updated to QGIS v.2.0, so it won't work either.

In QGIS v.2.6 go to menu Database->DB Manager->DB Manager, set the database connection and you will see the raster table. You can drag and drop it or right click and select Add to canvas.

Note: There was a bug on loading PostGIS raster layers to QGIS, which was solved in v.2.6: http://hub.qgis.org/issues/8628 So, update your QGIS to v. >= 2.6

Related Solutions

[GIS] Accessing PostGIS raster data using Query Layer in ArcGIS Desktop

To do this, you'll want to create a separate table containing the vectorized raster or a view. If your raster table is big (lots of rows), you're better using a separate table instead of a view do to the computation occurring every time the view is accessed. The SQL for creating the view could be...

CREATE VIEW myview AS
SELECT
    rid,
    (ST_DumpAsPolygons(rast)).*
FROM ras_test1

You should be able to view that in ArcGIS. It might complain about lacking an primary key, which you could always add if needed.

[GIS] Loading geotiffs into PostGIS using raster2pgsql

There is no limit on the size of a single image in PostGIS raster but you will be limited by other factors, such as how much memory you have in your computer. PostgreSQL does impose a 1GB limit on any one field PostgreSQL limits.

If you have the GDAL utility gdalinfo, could you run gdalinfo on one of your gtiff files and post the output here? Or if the data is publicly available (I assume the Ordinance Survey data is), can you provide a pointer to which tifs you are attempting to load so that I can test it?

gdalinfo MYGEOTIFF.tiff

The file size 71MB is tiny and should not be an issue. I currently load 10m elevation datasets (~500MB and 10812x10812 each) and 1m aerial imagery (~170MB and 5820x7575 each) without any issues.

Best Answer

Related Solutions

[GIS] Accessing PostGIS raster data using Query Layer in ArcGIS Desktop

[GIS] Loading geotiffs into PostGIS using raster2pgsql

Related Question