[raster] raster2pgqsql option for defining a grouping ID (like filename but integer)

[rmorelli]

I've been dealing with loading a bunch of rasters in a table using -F option. When coming to play with mapserver postgis raster LAYER definition(s) (ie DATA statement) I've found that using WHERE clause on the "filename" field is really a mess because escaping doesn't work (1).

Querying an integer data type field (as in the 2nd example in ​http://postgis.net/docs/RT_FAQ.html#idp61699792) works smoothly.

So, could you consider adding a cli option in order to add a new column of integer data type? Maybe casting filename (text) into id (int)? eg. my workaround has been: adding a new integer column and then use "UPDATE raster.dem SET id=CAST(substring(filename FROM '_(.*?)\.') AS integer);"

Hope this was clear. Regards Raffaele

PS really don't know if this should be also reported to mapserver devs, anyway knwoledge is useful.

---

(1) DATA "PG:host=localhost port=5432 dbname='xxx' user='xxx' password='xxx' schema='raster' table='dem' where='filename=\'dem20_12.tif\ mode='2'"

[robe]

rmorelli — I think there should be a way to escape text. I don't like this workaround because there are other cases where people will want to do text filters.

I know I mentioned the above. Have you tried using:

as an escape. That's the new way of escaping in PostgreSQL and the \' was a legacy way which may be turned off in new PostgreSQL.

So syntax would be:

 DATA "PG:host=localhost port=5432 dbname='xxx' user='xxx' password='xxx' schema='raster' table='dem' where='filename=''dem20_12.tif''' mode='2'" 

Also you above was missing an apostrophe so wouldn't have worked anyway — might have been my fault when I sent to postgis-users I might have left out the other slash

So correct using slashes would be

DATA "PG:host=localhost port=5432 dbname='xxx' user='xxx' password='xxx' schema='raster' table='dem' where='filename=\'dem20_12.tif\'' mode='2'" 

I don't like this proposes since it seems more a work around to something that is either an issue or just a misunderstanding with the GDAL driver. I'd much prefer updating the documentation to reflect how to use strings.

[rmorelli]

Replying to robe:

rmorelli — I think there should be a way to escape text. I don't like this workaround because there are other cases where people will want to do text filters.

Me too

I know I mentioned the above. Have you tried using:

as an escape. That's the new way of escaping in PostgreSQL and the \' was a legacy way which may be turned off in new PostgreSQL.

So syntax would be:

 DATA "PG:host=localhost port=5432 dbname='xxx' user='xxx' password='xxx' schema='raster' table='dem' where='filename=''dem20_12.tif''' mode='2'" 

It doesn't work here (PostgreSQL 9.3 - Postgis 2.1.4) shp2img reports (again):

msDrawMap(): Image handling error. Failed to draw layer named 'dem20'. <br> msDrawRasterLayerLow(): Unable to access file. Corrupt, empty or missing file 'PG:host=localhost port=5432 dbname='xxx' user=xxx password=xxx schema='raster' table='dem' where='filename='dem20_1.tif mode='2 for layer 'dem20'. Error browsing database for PostGIS Raster properties <br>

Also you above was missing an apostrophe so wouldn't have worked anyway — might have been my fault when I sent to postgis-users I might have left out the other slash

Sorry, typo error from shell cut and paste and adjust.

So correct using slashes would be

DATA "PG:host=localhost port=5432 dbname='xxx' user='xxx' password='xxx' schema='raster' table='dem' where='filename=\'dem20_12.tif\'' mode='2'" 

I don't like this proposes since it seems more a work around to something that is either an issue or just a misunderstanding with the GDAL driver. I'd much prefer updating the documentation to reflect how to use strings.

Well, I agree with that and quite sure it's an issue.

[robe]

I guess I'll have to try this myself. I'm pretty sure I've had success with this in the past so might be something buggy with new gdal. BTW your escape still is wrong. Not sure if its a cut and paste error again. You wrote:

\'dem20_12.tif\

When it should be:

\'dem20_12.tif\'

[robe]

oh let me add more for clarity — its' easy to get tripped up with the numerous '

The where part should look like this. Please verify you have that. I put an extra space between the ending filename ' and end of where for clarity

where='filename=\'dem20_12.tif\' '

[rmorelli]

Replying to robe:

oh let me add more for clarity — its' easy to get tripped up with the numerous '

The where part should look like this. Please verify you have that. I put an extra space between the ending filename ' and end of where for clarity

where='filename=\'dem20_12.tif\' '

Let's sort this out. I know what you are talking about and I use a mapfile sintax file to edit mapfiles in vim. My quoting is correct but I didn't use code block formatting in this editor so let's check with and without for your clarity:

With:

DATA "PG:host=localhost port=5432 dbname='cen_confini' user=catasto password=admin schema='raster' table='dem' where='filename=\'dem20_1.tif\'' mode='2'"

without: DATA "PG:host=localhost port=5432 dbname='cen_confini' user=catasto password=admin schema='raster' table='dem' where='filename=\'dem20_1.tif\ mode='2'"

[robe]

rmorelli,

Which version of GDAL (also Mapserver though I suspect its a GDAL issue) and PostgreSQL are you using?

[rmorelli]

Replying to robe:

rmorelli,

Which version of GDAL (also Mapserver though I suspect its a GDAL issue) and PostgreSQL are you using?

PostgreSQL 9.3.4 Postgis 2.1.4 Gdal 1.10.1 Mapserver 6.4.1

if it does matters, I run debian testing on amd64.

/r

[robe]

I just tried using gdal_translate (packaged with 6.5dev from here ​http://www.gisinternals.com/sdk/) (​http://www.gisinternals.com/sdk/PackageList.aspx?file=release-1600-gdal-mapserver.zip) which should be in theory similar command as what Mapserver uses for loading postgis raster

gdal_translate -of jpeg PG:"host='localhost' port='5432'
dbname='somedb' user='postgres' password='whatever' schema='aerials' table='o_2_boston' where='filename=\'22528915.sid.jpg\' ' mode='2'" c:\test.jpg

and this worked fine on PostGIS 2.1.3, PostgreSQL 9.2 database. So maybe something with mapserver then. Will take me longer to set that up, but at least doesn't seem anything wrong with latest gdal (and I'm pretty sure I've used similar older gdal to do those exports). Can you if not too much trouble turn logging on your database and include output of query mapserver is running.

[rmorelli]

I gave the relevat output of mapserver error log above, BTW here is the complete capture with log level set to 5

[Mon Nov 10 16:48:41 2014].280037 msDrawMap(): rendering using outputformat named png (AGG/PNG).
[Mon Nov 10 16:48:41 2014].280097 msDrawMap(): WMS/WFS set-up and query, 0.000s
[Mon Nov 10 16:48:41 2014].280122 msDrawRasterLayerLow(dem20): entering.
[Mon Nov 10 16:48:41 2014].298835 msDrawRasterLayerLow(): Unable to access file. Corrupt, empty or missing file 'PG:host=localhost port=5432 dbname='xxx' user=xxx password=xxx schema='raster' table='dem' where='filename='dem20_1.tif'' mode='2'' for layer 'dem20'. Error browsing database for PostGIS Raster properties
[Mon Nov 10 16:48:41 2014].298861 msDrawMap(): Image handling error. Failed to draw layer named 'dem20'.
[Mon Nov 10 16:48:41 2014].299182 msFreeMap(): freeing map at 0x1b06a80

This is the output when running gdal_translate as you did:

gdal_translate -of jpeg PG:"host='localhost' port='5432' dbname='xxx' user='xxx' password='xxx' schema='raster' table='dem' where='filename='dem20_1.tif'' mode='2' " test.jpg
Warning 1: Cannot find information about raster.dem table in raster_columns view. The raster table loading would take a lot of time. Please, execute AddRasterConstraints PostGIS function to register this table as raster table in raster_columns view. This will save loading time.
ERROR 1: Error browsing database for PostGIS Raster properties
GDALOpen failed - 1
Error browsing database for PostGIS Raster properties

[rmorelli]

gdal_translate with escape character

gdal_translate -of jpeg PG:"host='localhost' port='5432' dbname='xxx' user='xxx' password='xxx' schema='raster' table='dem' where='filename=\'dem20_1.tif\'' mode='2' " test.jpg
Warning 1: Cannot find information about raster.dem table in raster_columns view. The raster table loading would take a lot of time. Please, execute AddRasterConstraints PostGIS function to register this table as raster table in raster_columns view. This will save loading time.
row number 0 is out of range 0..-1
Errore di segmentazione

[robe]

The gdal_translate error is probably because its looking in raster_columns and can't find useful meta data (though the table should be registered in that view but probably with not any useful information — do you see it listed in raster_columns view).

I did run AddRasterConstraints on my table with the -C option during load, but you should have similar issues using a number for file if you are getting that. Does gdal_translate work with your integer id?

Anyrate to get rid of that as documented here: ​http://postgis.net/docs/manual-dev/RT_AddRasterConstraints.html

SELECT AddRasterConstraints('raster', 'dem', 'rast');

now it looks like mapserver is stripping off the escape \ making the gdal query invalid

[rmorelli]

Replying to robe:

The gdal_translate error is probably because its looking in raster_columns and can't find useful meta data (though the table should be registered in that view but probably with not any useful information — do you see it listed in raster_columns view).

I did run AddRasterConstraints on my table with the -C option during load, but you should have similar issues using a number for file if you are getting that. Does gdal_translate work with your integer id?

I did run AddRasterConstraints and yes, integer id works like a charm.

Anyrate to get rid of that as documented here: ​http://postgis.net/docs/manual-dev/RT_AddRasterConstraints.html

SELECT AddRasterConstraints('raster', 'dem', 'rast');

now it looks like mapserver is stripping off the escape \ making the gdal query invalid

I suspected it was a mapserver issue so filed a bug report there ​https://github.com/mapserver/mapserver/issues/5030

[robe]

Milestone renamed