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
