5

When I want to export a GTiff from my PostGIS DB with this code:

gdal_translate -of GTiff PG":host='localhost' port:'5432' dbname='landslide' user='postgres' password='pass' schema='public' table='dgm' mode='2'" test.tiff

I get the following error:

Illegal filename. character <'> not allowed.Illegal filename. character <'> not allowed.ERROR 4: `PG:host='localhost' port:'5432' dbname='landslide' user='postgres' password='pass' schema='public' table='dgm' mode='2'' does not exist in the file system, and is not recognised as a supported dataset name.

GDALOpen failed - 4 `PG:host='localhost' port:'5432' dbname='landslide' user='postgres' password='pass' schema='public' table='dgm' mode='2'' does not exist in the file system, and is not recognised as a supported dataset name.

I followed the instructions from gdal.

Is the syntax incorrect? I tried to change the quotes, but no success.

3
  • I am trying to do a lo_export of a raster file in postgis, and finally came to SELECT lo_export(oid,'filesystempath'); But When I check the filesystem there is nothing there... anybody knows why? and how can I get it create the TIF file?
    – negroscuro
    Apr 26 '18 at 12:04
  • Post this as a question, not as an answer
    – Iridium
    Apr 26 '18 at 12:19
  • If you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context. - From Review
    – BERA
    Apr 26 '18 at 12:25
3

I would suggest you to use this alternative way to do the same:

  1. To export raster as TIFF, export it to a lob(large object) and return it’s oid using the query:

    SELECT oid, lowrite(lo_open(oid, 131072), tiff) As num_bytes
    FROM
    ( VALUES (lo_create(0),
    ST_Astiff( (SELECT rast FROM raster_table WHERE rid = 1) )
    ) ) As v(oid,tiff);
    
  2. Use the PostgreSQL \lo_export command to output your raster as a tiff file:

    \lo_export 147303 '/tmp/myraster.tiff'
    
  3. As you probably don't use the lob anymore you should probably unlink (remove) it by calling:

    SELECT lo_unlink(147303);
    

Later you can GeoReference it using gdal_translate:

gdal_translate /tmp/myraster.tiff -of GTiff -a_srs 'PROJCS[AUTHORITY["EPSG","900913"]]' -a_nodata 0 /tmp/myrastergeo.tiff

Source: How to: Create a Heatmap Raster in PostGIS and Render in GeoServer

3

The problem is you wrote

PG":

And you should write

PG:"

But you can avoid single quotes too. They shouldn't be necessary.

By the way, I'm the developer of the driver. I strongly recommend you to use the last GDAL version from trunk. It includes some great improvements that make the raster fast enough for data visualization in QGIS or MapServer (I'm actually interested in testing that and get feedback).

8
  • So the GDAL PostGIS Raster driver documentation needs to be updated accordingly to the PostgreSQL / PostGIS driver. This led Stefan (and probably others) to a wrong connection string. Nov 21 '13 at 18:00
  • 1
    OMG, absolutely. I've just fixed it. That was a silly and annoying mistake. Sorry for that. Nov 21 '13 at 21:35
  • I installed gdal 1.9.0-3 for linux. But gdal_translate --version shows me GDAL 1.6.0dev, FWTools 2.0.6, released 2008/02/03. I used 1.6 before. How can get rid of the old version?
    – Stefan
    Nov 22 '13 at 11:31
  • 'gdal-config --prefix' tells you the PATH where default GDAL library (1.6 in your case) is installed. Probably, $HOME/FWTools. You can get rid of that folder. Once done, the default GDAL should be 1.9. You may need to update your PATH variable, to ensure it doesn't contain references to the deleted FWTools folder. Nov 22 '13 at 14:27
  • Do I have to use FWTools again in addition to the newer GDAL? Exporting database data as a shapefile with ogr2ogr doesn't work for me without FWTools. And pgsql2shp shortens the attribute fields to a ten string when I want export data (and especially with queries).
    – Stefan
    Nov 25 '13 at 10:39
2

Because -of GTiff and the quotes may be omitted (there aren't spaces), and double quotes after PG usually goes after the colon (like stated here), it should be:

gdal_translate PG:"host=localhost port:5432 dbname=landslide user=postgres password=pass schema=public table=dgm mode=2" test.tiff
8
  • The same error occurs when I try your code.
    – Stefan
    Nov 20 '13 at 15:27
  • And what happens with gdalinfo? Same error? Nov 20 '13 at 15:44
  • Yes, same error. Could be something wrong with the raster table?
    – Stefan
    Nov 20 '13 at 15:48
  • Can you load your PG raster table in QGIS using the same parameters? Nov 20 '13 at 15:51
  • This is another problem. I don't know how to load the raster in QGIS (I'm using 2.0). In the PostGIS Layer list is a raster columns entry, but I don't know how to us it.
    – Stefan
    Nov 20 '13 at 15:57
1

gdal_translate needs field called rid. So, we must have rid field. Check this at:

st_clip and gdal_translate: "Error browsing database for PostGIS Raster properties" - Why?

0

I know that this is an old question. The question and answers came up when I was searching for a solution to a similar problem. All the examples using raster2pgsql or gdal show single quotes around the database table or especially the column name. The desire is to keep the column name lower cased. Both the table creation and the load of the data was without error using single quotes. The symptoms of the problem developed when I tried to view the data with QGIS = nothing, or gdalinfo the data = errors. What happened is that the single quoted rast column name became 'rast' in the database. At that point, even if you use afalciano's answer of

gdal_translate PG:"host=localhost port:5432 dbname=landslide user=postgres password=pass schema=public table=dgm mode=2" test.tiff

to read the loaded data, the damage has been done. You need to rename the column from say 'rast' to rast in the database. The generated constraint names did not matter. After the rename sans the quotes, QGIS version 2.4.0-Chugiak displayed the data and the gdal tools stopped failing and provided me with the desired information.

1
  • I have used following in QGIS 2.18.11 (64 bit). The only difference is use of column = rast inside PG: > gdal_translate PG:"dbname=ecolservicedb host=localhost > user=ecolservice password=ecolservice port=5432 mode=2 schema=public > column=rast table=_ags_473038164" test.tiff
    – EvilInside
    Aug 16 '17 at 16:49
0

I tested this successfully:

gdal_translate -of PNG PG:"host='localhost' port='5432' dbname='postgis_db' user='postgres' schema='someschema' table='some_table' mode='2' where='rid=1'" pngfile.png

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.