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

[Pierre Racine]

>From what I know there is no way this should work. You have to read the raster layer as a GDAL layer. This is what is done in PostGIS in Action.

Pierre

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
> bounces at postgis.refractions.net] On Behalf Of David Quinn
> Sent: Thursday, February 16, 2012 3:14 PM
> To: PostGIS Users Discussion
> Subject: [postgis-users] Mapserver/Postgis connection and WMS query
> 
> 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