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