[mapserver-users] Blank PostGIS Layer

[Ben Madin]

G'day all,

I am having generally excellent results with MapServer 5.2, but I am  
having a problem with a postgis layer not displaying, and no  
suggestion that it shouldn't from the logs.

I imported a shapefile :

shp2pgsql -s 4326 /Users/19022662/Geodata/healthmapper/LA/roads roads  
| psql prices

in postgres I gave permission for the mapserver user to select from it :

grant select on roads to gms_mapserver;

My map layer :

	LAYER
		STATUS DEFAULT
		DEBUG 4
		NAME "Roads"
		GROUP "Background"
		METADATA
			layer_title "Roads"
		END
		CONNECTIONTYPE postgis
		CONNECTION "host=localhost port=5432 dbname=prices password=badluck  
user=gms_mapserver"
		DATA "the_geom FROM (
			SELECT  gid, the_geom
			FROM    roads
			) AS foo USING UNIQUE gid USING SRID=4326"
#		DATA 'healthmapper/LA/roads'
     		TYPE    LINE
     		PROJECTION
     		    "init=epsg:4326"
     		END
     		TRANSPARENCY 100
     		CLASS
     		  NAME      "ROADS"
     		  OUTLINECOLOR 255 255 255
     		  symbol 'dashed'
     		  COLOR		255 0 0
     		END
	END

and I don't get the layer (even with everything else turned off);

Other postGIS polygon layers are showing fine.

If I load the data directly from the shapefile, it is fine

If I connect to the postgis layer from QGIS, it is fine.

Here is the dump from the error console

[Wed Dec 17 15:44:42 2008].96166 msPOSTGISLayerOpen called  
datastatement: the_geom FROM (
			SELECT  gid, the_geom
			FROM    roads
			) AS foo USING UNIQUE gid USING SRID=4326
[Wed Dec 17 15:44:42 2008].96664 MSPOSTGISLayerOpen -- shared  
connection not available.
[Wed Dec 17 15:44:42 2008].101270  
msConnPoolRegister(Roads,host=localhost port=5432 dbname=prices  
password=badluck user=gms_mapserver,0x101900400)
[Wed Dec 17 15:44:42 2008].101288 msPOSTGISLayerFreeItemInfo called
[Wed Dec 17 15:44:42 2008].101314 msPOSTGISLayerWhichShapes called
[Wed Dec 17 15:44:42 2008].101328 msPOSTGISLayerParseData: unique  
column = gid, srid='4326', geom_column_name = the_geom, table_name=(
			SELECT  gid, the_geom
			FROM    roads
			) AS foo
[Wed Dec 17 15:44:42 2008].101574 query_string_0_6:DECLARE mycursor  
BINARY CURSOR FOR SELECT  
asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text from (
			SELECT  gid, the_geom
			FROM    roads
			) AS foo WHERE the_geom && setSRID('BOX3D(102.054844143416  
18.0123207848761,102.992352286832 18.7152227848761)'::BOX3D, 4326 )
[Wed Dec 17 15:44:42 2008].175520 msPOSTGISLayerFreeItemInfo called
[Wed Dec 17 15:44:42 2008].175539 msPOSTGISLayerClose datastatement:  
the_geom FROM (
			SELECT  gid, the_geom
			FROM    roads
			) AS foo USING UNIQUE gid USING SRID=4326
[Wed Dec 17 15:44:42 2008].175543 msPOSTGISLayerClose -- closing  
query_result
[Wed Dec 17 15:44:42 2008].175930  
msConnPoolRelease(Roads,host=localhost port=5432 dbname=prices  
password=badluck user=gms_mapserver,0x101900400)
[Wed Dec 17 15:44:42 2008].175940 msConnPoolClose(host=localhost  
port=5432 dbname=prices password=badluck user=gms_mapserver,0x101900400)
[Wed Dec 17 15:44:42 2008].176004 msDrawMap(): Layer 8 (Roads), 0.080s
[Wed Dec 17 15:44:42 2008].215812 msDrawMap(): Drawing Label Cache,  
0.000s
[Wed Dec 17 15:44:42 2008].215824 msDrawMap() total time: 0.121s
[Wed Dec 17 15:44:42 2008].227155 msSaveImage() total time: 0.011s
[Wed Dec 17 15:44:42 2008].230642 msSaveImage() total time: 0.001s
[Wed Dec 17 15:44:42 2008].234008 mapserv request processing time  
(loadmap not incl.): 0.139s
[Wed Dec 17 15:44:42 2008].234016 msFreeMap(): freeing map at  
0x10180e200.
[Wed Dec 17 15:44:42 2008].234020 msPOSTGISLayerClose datastatement:  
the_geom FROM (
			SELECT  gid, the_geom
			FROM    roads
			) AS foo USING UNIQUE gid USING SRID=4326
[Wed Dec 17 15:44:42 2008].234023 msPOSTGISLayerClose -- layerinfo is   
NULL

If I copy and paste the query, I get data (I have no way of knowing if  
it is OK) :

| 16899
   
\001 
\007 
\000 
\000 
\000 
\001 
\000 
\000 
\000 
\001 
\005 
\000 
\000 
\000\001\000\000\000\001\002\000\000\000\037\000\000\000\000\000\000@ 
\023\225Y@\000\000\000\300

etc (I just copied some of it)

There is no difference between cgi mapserv and shp2img.

Any thoughts much appreciated!!

cheers

Ben




-- 

Ben Madin
REMOTE INFORMATION

t : +61 8 9192 5455
f : +61 8 9192 5535
m : 0448 887 220
Broome   WA   6725

ben at remoteinformation.com.au



							Out here, it pays to know...