[mapserver-users] Postgis Connection Error

[Jan Hartmann]

Hello Horst,

In a subselect, you have to add explicitly the OID field (select oid,* 
from ...). OID is an invisible field in every table that is used by the 
PostGIS connector. It is not automatically included when you specify * 
in a select clause.

Perhaps the use of subselects could be added to the regular 
documentation? It's really a great feature of the MapServer-PostGIS 
interface, but it's not especially intuitive to use.

Cheers,

Jan Hartmann
Department of Geography
University of Amsterdam
jhart at frw.uva.nl



Düster Horst wrote:
> Hi,
>  
> I try to use a subselection in the DATA block to retrieve data from PostGIS
> with the following map-file snip:
>  
> ....
> LAYER
>   CONNECTIONTYPE POSTGIS
>   CONNECTION 'PG Connection'
>   DATA "wkb_geometry from ( select * from liegen where archive=0 ) as
> result"
>   TYPE POLYGON
> ...
>  
>  
> MapServer 3.6.2 responses with the following error message:
> msPOSTGISLayerWhichShapes(): Query error. prep_DB:Error executing POSTGIS
> DECLARE statement (0.6 failed - retried 0.5 and it failed too). DECLARE
> mycursor BINARY CURSOR FOR SELECT
> Nummer::text,asbinary(force_collection(force_2d(wkb_geometry)),'NDR'),OID::t
> ext from ( select * from liegen where archive=0 ) as result WHERE
> wkb_geometry && setSRID('BOX3D(604311.575758 229285,604974.424242
> 229815)'::BOX3D, find_srid('','liegenden','wkb_geometry') ) 
>  
> Some discussions dealed with this method an it should work. Perhaps there
> was discussed a solution but the mail archive does'n work.
> Any hint????
>  
> Best regards
>  
> Dr. Horst Düster 
> Leiter 
> Abteilung SO!GIS Koordination
> Rötihof 
> CH-4500 Solothurn 
> 
> Tel.: 032 627 25 32 
> Fax: 032 627 22 14
> 
> horst.duester at bd.so.ch <mailto:horst.duester at bd.so.ch>  
>  <http://www.sogis.so.ch/> www.sogis.so.ch
> 
> 
>  
> 
>