[mapserver-users] Help debugging PostGIS raster connection

[Thomas Horner]

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>
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
> https://lists.osgeo.org/mailman/listinfo/mapserver-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20190305/94fb5df5/attachment.htm>