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:
https://github.com/mapserver/mapserver/wiki/Raster-Layers:-GDAL-PostGISRaster-Tricks Cheers Thomas On Wed, Mar 6, 2019 at 6:29 AM Jeff McKenna <jmcke...@gatewaygeomatics.com> wrote: > Great news Thomas. I also think (for good karma) all of your steps and > hints for PGraster could be added to a new wiki page in the "Tricks, > Tips, and HowTos" section of https://github.com/mapserver/mapserver/wiki > > (that way people don't have to troll through thousands of email messages > to find your important tip) > > thanks! > > -jeff > > > > On 2019-03-05 7:02 PM, Thomas Horner wrote: > > 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 <mailto: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 <mailto: > mapserver-users@lists.osgeo.org> > > https://lists.osgeo.org/mailman/listinfo/mapserver-users > > > > > -- > Jeff McKenna > MapServer Consulting and Training Services > https://gatewaygeomatics.com/ > _______________________________________________ > 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
