[mapserver-users] Help debugging PostGIS raster connection
Thomas Horner
haevitetty at gmail.com
Wed Mar 6 09:08:55 PST 2019
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 <jmckenna at gatewaygeomatics.com>
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
> > <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/
> _______________________________________________
> 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/20190306/496009e2/attachment-0001.html>
More information about the mapserver-users
mailing list