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
