<div dir="ltr"><div dir="ltr">These findings, along with my entire solution for querying rows of a table with a raster column based on timestamp in the WMS GET parameter, has been added to the MapServer wiki Tips and Tricks section which links to this page:<br><br><div><a href="https://github.com/mapserver/mapserver/wiki/Raster-Layers:-GDAL-PostGISRaster-Tricks">https://github.com/mapserver/mapserver/wiki/Raster-Layers:-GDAL-PostGISRaster-Tricks</a><br></div><div><br>Cheers<br>Thomas</div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Mar 6, 2019 at 6:29 AM Jeff McKenna <<a href="mailto:jmckenna@gatewaygeomatics.com">jmckenna@gatewaygeomatics.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Great news Thomas. I also think (for good karma) all of your steps and <br>
hints for PGraster could be added to a new wiki page in the "Tricks, <br>
Tips, and HowTos" section of <a href="https://github.com/mapserver/mapserver/wiki" rel="noreferrer" target="_blank">https://github.com/mapserver/mapserver/wiki</a><br>
<br>
(that way people don't have to troll through thousands of email messages <br>
to find your important tip)<br>
<br>
thanks!<br>
<br>
-jeff<br>
<br>
<br>
<br>
On 2019-03-05 7:02 PM, Thomas Horner wrote:<br>
> Great! Thanks Jeff. Turns out it was the single quotes in other parts <br>
> of the connection string... Not sure why. Possibly special characters <br>
> in the password or username? After removing them, I no longer have <br>
> connection issues. With that being solved, removing the WHERE clause at <br>
> least returns a blank image instead of an error.<br>
> <br>
> I then brought back the WHERE clause, which returned an error despite <br>
> working in GDAL.<br>
> This WHERE clause returns a single raster result in GDAL with the exact <br>
> same connection string as above:<br>
> gdalinfo "PG:.. where='timestamp=\'2019-03-05 00:00:00\''"<br>
> <br>
> However, no matter the combination of string literals, escape <br>
> characters, and whatnot, I could not get MapServer to handle this clause <br>
> without error.<br>
> <br>
> But wait! Enter Postgres's dollar-quoted string constants:<br>
> where='timestamp=$$2019-03-05 00:00:00$$'<br>
> <br>
> The DATA connection string now works. Hope this helps anyone in the future.<br>
> <br>
> Regards,<br>
> Thomas<br>
> <br>
> On Tue, Mar 5, 2019 at 3:24 PM Jeff McKenna <br>
> <<a href="mailto:jmckenna@gatewaygeomatics.com" target="_blank">jmckenna@gatewaygeomatics.com</a> <mailto:<a href="mailto:jmckenna@gatewaygeomatics.com" target="_blank">jmckenna@gatewaygeomatics.com</a>>> <br>
> wrote:<br>
> <br>
> Hi Thomas,<br>
> <br>
> I would try the following:<br>
> <br>
> - remove all single quotes from your DATA statement (most times they<br>
> are<br>
> unnecessary for my use, and cause me problems)<br>
> - remove the WHERE clause<br>
> - also try to access another table from your database, but not raster,<br>
> using that same DATA connection statement (I bet the problem has<br>
> nothing<br>
> to do with the raster table, but instead is your connection parameters)<br>
> - verify that remote connections can access your database (check your<br>
> file pg_hba.conf)<br>
> <br>
> hope that brings ideas and opens doors...<br>
> <br>
> -jeff<br>
> <br>
> <br>
> <br>
> -- <br>
> Jeff McKenna<br>
> MapServer Consulting and Training Services<br>
> <a href="https://gatewaygeomatics.com/" rel="noreferrer" target="_blank">https://gatewaygeomatics.com/</a><br>
> <br>
> <br>
> On 2019-03-04 2:38 PM, Thomas Horner wrote:<br>
> > I have a PostGIS connection string that works fine in GDAL, as I<br>
> get a<br>
> > raster response when running<br>
> > ---<br>
> > gdalinfo "PG:host=... port=5432 user='...' password='...'<br>
> > dbname='weather_models' schema='rasters'<br>
> > table='wpc_snow_50pct_1551268800' column='rast' mode=1<br>
> > where='timestamp=\'2019-03-02 00:00:00\''"<br>
> > ---<br>
> ><br>
> > On this very same server, I have cgi-mapserver running, and the<br>
> output<br>
> > from /usr/lib/cgi-bin/mapserv/ -v is<br>
> > ---<br>
> > MapServer version 7.2.1 OUTPUT=PNG OUTPUT=JPEG OUTPUT=KML<br>
> SUPPORTS=PROJ<br>
> > SUPPORTS=AGG SUPPORTS=FREETYPE SUPPORTS=CAIRO SUPPORTS=SVG_SYMBOLS<br>
> > SUPPORTS=RSVG SUPPORTS=ICONV SUPPORTS=FRIBIDI SUPPORTS=WMS_SERVER<br>
> > SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT<br>
> > SUPPORTS=WCS_SERVER SUPPORTS=SOS_SERVER SUPPORTS=FASTCGI<br>
> > SUPPORTS=THREADS SUPPORTS=GEOS SUPPORTS=PBF INPUT=JPEG INPUT=POSTGIS<br>
> > INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE<br>
> > ---<br>
> ><br>
> > The problem is, when I use that same PG:... connection string as the<br>
> > DATA for a LAYER in MapServer, I get an unhelpful error: Unable to<br>
> > access file. Corrupt, empty or missing file '<snip of PG connection<br>
> > string>' Couldn't establish a database connection.<br>
> ><br>
> > Despite turning DEBUG level to 5, and CPL_DEBUG to ON in the MAP<br>
> block<br>
> > of my mapfile, I don't get any helpful debug information<br>
> whatsoever in<br>
> > my error log -- just the connection string and "Couldn't establish a<br>
> > database connection." I have tried a variety of where clauses and<br>
> other<br>
> > tables and get the same result. I have no idea if the actual<br>
> error is<br>
> > coming from GDAL or MapServer, despite all those strings working<br>
> fine<br>
> > when running them directly via GDAL.<br>
> ><br>
> > By the way, it's the same error whether I use 'timestamp'= or<br>
> timestamp=.<br>
> ><br>
> > Here is the mapfile:<br>
> > ---<br>
> > MAP<br>
> > CONFIG "MS_ERRORFILE" "/map/error.txt"<br>
> > CONFIG "CPL_DEBUG" "ON"<br>
> > DEBUG 5<br>
> > PROJECTION<br>
> > "init=epsg:4326"<br>
> > END<br>
> > LAYER<br>
> > NAME wpc_snow_50pct<br>
> > TYPE RASTER<br>
> > STATUS ON<br>
> > VALIDATION<br>
> > "timestamp" ".*"<br>
> > END<br>
> > PROJECTION<br>
> > "init=epsg:4326"<br>
> > END<br>
> > METADATA<br>
> > "wms_title" "wpc_snow_50pct"<br>
> > "wms_srs" "EPSG:4326"<br>
> > "wms_extent" "-108.5 41 -104.5 36"<br>
> > "wms_enable_request" "*"<br>
> > END<br>
> > DATA "PG:host=... port=5432 dbname='weather_models' user='...'<br>
> > password='...' schema='rasters' table='wpc_snow_50pct_1551268800'<br>
> > column='rast' mode=1 where='timestamp=\'2019-03-02 00:00:00\''"<br>
> > PROCESSING "BANDS=1"<br>
> > END<br>
> > END<br>
> > ---<br>
> ><br>
> > Any help is appreciated!<br>
> ><br>
> > Thanks,<br>
> > Thomas<br>
> ><br>
> ><br>
> _______________________________________________<br>
> mapserver-users mailing list<br>
> <a href="mailto:mapserver-users@lists.osgeo.org" target="_blank">mapserver-users@lists.osgeo.org</a> <mailto:<a href="mailto:mapserver-users@lists.osgeo.org" target="_blank">mapserver-users@lists.osgeo.org</a>><br>
> <a href="https://lists.osgeo.org/mailman/listinfo/mapserver-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/mapserver-users</a><br>
> <br>
<br>
<br>
-- <br>
Jeff McKenna<br>
MapServer Consulting and Training Services<br>
<a href="https://gatewaygeomatics.com/" rel="noreferrer" target="_blank">https://gatewaygeomatics.com/</a><br>
_______________________________________________<br>
mapserver-users mailing list<br>
<a href="mailto:mapserver-users@lists.osgeo.org" target="_blank">mapserver-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/mapserver-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/mapserver-users</a></blockquote></div>