Re: Help debugging PostGIS raster connection

Great work Thomas!  thank you!  -jeff



On 2019-03-06 1:08 PM, Thomas Horner wrote:
> 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 
> <[email protected] <mailto:[email protected]>> 
> 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
>      > <[email protected]
>     <mailto:[email protected]>
>     <mailto:[email protected]
>     <mailto:[email protected]>>>
>      > 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
>      > [email protected]
>     <mailto:[email protected]>
>     <mailto:[email protected]
>     <mailto:[email protected]>>
>      > https://lists.osgeo.org/mailman/listinfo/mapserver-users
>      >
> 
> 
>     -- 
>     Jeff McKenna
>     MapServer Consulting and Training Services
>     https://gatewaygeomatics.com/
>     _______________________________________________
>     mapserver-users mailing list
>     [email protected] <mailto:[email protected]>
>     https://lists.osgeo.org/mailman/listinfo/mapserver-users
> 
> 
> _______________________________________________
> mapserver-users mailing list
> [email protected]
> https://lists.osgeo.org/mailman/listinfo/mapserver-users
> 


-- 
Jeff McKenna
MapServer Consulting and Training Services
https://gatewaygeomatics.com/
_______________________________________________
mapserver-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/mapserver-users