[mapserver-users] Help debugging PostGIS raster connection

[Jeff McKenna]

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 
> <jmckenna at gatewaygeomatics.com <mailto:jmckenna at 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 at lists.osgeo.org <mailto:mapserver-users at lists.osgeo.org>
>     https://lists.osgeo.org/mailman/listinfo/mapserver-users
> 


-- 
Jeff McKenna
MapServer Consulting and Training Services
https://gatewaygeomatics.com/