[postgis-users] Mapserver/Postgis connection and WMS query

[David Quinn]

Hi List,

(I did send a very similar question to the mapserver list two days ago but
I didn't hear any comments or suggestions. This is a problem between
mapserver and postgis).

I'm using mapserver to display some rasters as WMS layers using Openlayers.
At the moment I have all my rasters in a web-readable folder and it works
fine. I want to try accessing the rasters using postgis, but I'm not sure
exactly how to do this. I've read through this
http://mapserver.org/input/vector/postgis.html and the relevant part of
PostGIS in Action. My original map file (which worked) looked like this:

LAYER
NAME "new_york_pop"
DATA    "/home//htdocs/data/new_york_pop.tif"
STATUS  OFF
TYPE    RASTER
METADATA
"wms_title"  "WMS new_york Raster"
"wms_srs"    "EPSG:900913"
END
END

I tried changing this to the following:

LAYER
NAME "new_york_pop"
TYPE    RASTER
CONNECTIONTYPE POSTGIS
CONNECTION "host=18.00.00.00 dbname=raster_analysis user=postgres
password=mypassword port=5434"
DATA "SELECT ST_AsTIFF(ST_Clip(r.rast, 1, p.geom, NULL, TRUE)) FROM
us_pop_800 AS r, cities AS p WHERE ST_Intersects(r.rast,  p.geom) AND
p.name= 'new_york'"
STATUS  OFF
METADATA
"wms_title"  "WMS new_york Raster"
"wms_srs"    "EPSG:900913"
END
END

I want to clip my raster to the polygon and to display the clipped raster
using a WMS query. The SQL query runs fine within the SQL console of
Postgresql and returns a few tiffs, but it does not work on mapserver. My
mapserver error log shows the following:

[Tue Feb 14 12:16:29 2012].918681 msDrawMap(): Image handling error. Failed
to draw layer named 'new_york_pop'.
[Tue Feb 14 12:16:29 2012].917535 msDrawMap(): rendering using outputformat
named png (AGG/PNG).
[Tue Feb 14 12:16:29 2012].918757 msDrawRasterLayerLow(): Unable to access
file. Corrupt, empty or missing file 'SELECT ST_AsTIFF(ST_Clip(r.rast, 1,
p.geom, NULL, TRUE)) FROM us_pop_raster AS r, cities AS p WHERE
ST_Intersects(r.rast, p.geom) AND p.name = 'new_york'' for layer
'new_york_pop'.
[Tue Feb 14 12:16:29 2012].918766 msDrawMap(): Image handling error. Failed
to draw layer named 'new_york_pop'.

I would appreciate any suggestions for debugging this. I have connected to
the postgresql database using a php file from the same location so
mapserver should have the correct permissions to connect (and my port is
5434, rather than the usual 5432).

Thank you,
David
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20120216/d92cac2f/attachment.html>