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

[Pierre Racine]

This is a GDAL PostGIS connection string, not a direct PostGIS connection sting. Check:

http://trac.osgeo.org/gdal/wiki/frmts_wtkraster.html

Make sure gdal_translate is able to export your raster to a tiff before trying the same connection string with MapServer.

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:55 PM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Mapserver/Postgis connection and WMS query
> 
> Pierre,
> 
> What do you mean by reading the raster layer as a GDAL layer? In the PostGIS in
> Action raster example it seemed to just connect to the database, with no query
> using :
> 
> Data "PG:host=localhost ....."
> 
> I tried removing CONNECTION and DATA and using the following:
> 
> DATA "PG:host=18.00.00.00 dbname=raster_analysis user=postgres port=5434
> password=password schema='myschema' table='pop_800'"
> 
> This did not work either. Should I be just connecting to the data, and not trying
> to have a query there also?
> 
> -David
> 
> 
> On Thu, Feb 16, 2012 at 3:31 PM, Pierre Racine <Pierre.Racine at sbf.ulaval.ca>
> wrote:
> 
> 
> 	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 <tel:2012%5D.918681>
> msDrawMap(): Image handling error. Failed
> 	> to draw layer named 'new_york_pop'.
> 	> [Tue Feb 14 12:16:29 2012].917535 <tel:2012%5D.917535>
> msDrawMap(): rendering using
> 	> outputformat named png (AGG/PNG).
> 	> [Tue Feb 14 12:16:29 2012].918757 <tel:2012%5D.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 <tel:2012%5D.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
> 
> 
> 	_______________________________________________
> 	postgis-users mailing list
> 	postgis-users at postgis.refractions.net
> 	http://postgis.refractions.net/mailman/listinfo/postgis-users
> 
>