[postgis-users] reusing MapServer query-string

[Jan Hartmann]

Hello Heiko,

I see three problems with your data statement:

1) A general SQL syntax error: table names should precede column names:
---> select flstk.the_geom, flstk.gm .... from flstk

2) A PostgreSQL requirement: subselects have to be given a name:

------> select the_geom from (select .... ) as foo

3) A PostGIS requirement: in a subselect the OID variable has to be 
added explicitly. This is the same problem Horst Düster put on the 
MapServer list a few weeks ago. I couldn't find it in the list archives; 
the MapServer site crashed some time ago, and the mailling list archive 
still seems to have problems. These is the posting from my own mail archive:

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.

So you data statement should be:

DATA "the_geom from
 > (select flstk.the_geom, flstk.oid,flstk.gm, flstk.fl, flstk.fz, 
flstk.fn from flstk) as foo"

For testing purposes, I don't use myself the HEADER/FOOTER/TEMPLATE 
method, but just try to display the PostGIS layer as a regular map with 
  a global TEMPLATE. After you get a basic map on the screen you can 
always experiment further with more intricate MapFiles.

Hope this solves at least part of the problem.

Good luck,

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

PS: Please use the newest version MapServer (3.6.3) It gives much better 
PostGIS error messages.


heiko wrote:
> Hi all,
> I'm a GIS student working on a project using a Postgis/Postgresql Database
> connected with the UMN-MapServer
> 
> Jan Hartmann wrote:
> 
>> / 
> 
> />/ Is it possible to reuse a PostGIS query for Mapserver within 
> PHP-MapScript,
> />/ once the map has been created? The usual way to create a map layer from
> />/ Postgis is:
> />/ />/ $layer->set("connectiontype","POSTGIS");
> />/ $layer->set("connection","host = ... port=... etc");
> />/ $layer->set("data","the_geom from table");
> />/ />/ This works fine, even with complex subselects within the 
> DATA-step, like:
> />/ "the geom from
> />/     (select table1.the_geom, table1.datavar from table1,table2
> />/         where (distance(table1.the_geom,table2.the_geom)) = 0
> />/         and table1.linkvar = table2.linkvar
> />/      )
> />/ "
> />/ />/ This wil produce a map with only the elements of table1 that are 
> overlapping
> />/ with elements of table2 (very nice indeed!). I can even use the 
> values of
> />/ "datavar" to create classes within Mapserver, like
> />
> / I've got a working mapfile containing:
> 
> /LAYER
>    NAME "Flurstuecke"
>    STATUS ON
>    DATA "the_geom from flstk"
>    TYPE POLYGON
>    CONNECTIONTYPE POSTGIS
>    CONNECTION "user=gast dbname=mapserver host=127.0.0.1"
>    TOLERANCE 2
>    HEADER 
> "/usr/local/httpd/htdocs/mapserver_demos/koman/htdocs/flstk_header.html"
>    FOOTER 
> "/usr/local/httpd/htdocs/mapserver_demos/koman/htdocs/flstk_footer.html"
>    CLASS
>      NAME "Flurstuecke"
>      OUTLINECOLOR 0 0 255
>      TEMPLATE 
> "/usr/local/httpd/htdocs/mapserver_demos/koman/htdocs/flstk.html"
>    END
>  END
> 
> Jan if i understand your posting right it should be possible to alter 
> the mapfile like this
> (assuming the fields in the table were correct):
> 
> LAYER
>    NAME "Flurstuecke"
>    STATUS ON
>    DATA "the_geom from
> (select flstk.the_geom, gm.flstk, fl.flstk, fz.flstk, fz.flstk, fn.flstk 
> from flstk)"
>    TYPE POLYGON
>    CONNECTIONTYPE POSTGIS
>    CONNECTION "user=gast dbname=mapserver host=127.0.0.1"
>    TOLERANCE 2
>    HEADER 
> "/usr/local/httpd/htdocs/mapserver_demos/koman/htdocs/flstk_header.html"
>    FOOTER 
> "/usr/local/httpd/htdocs/mapserver_demos/koman/htdocs/flstk_footer.html"
>    CLASS
>      NAME "Flurstuecke"
>      OUTLINECOLOR 0 0 255
>      TEMPLATE 
> "/usr/local/httpd/htdocs/mapserver_demos/koman/htdocs/flstk.html"
>    END
>  END
> 
> and getting the selected fields in a manner mapserver could understand 
> them.
> Doing so i received following mapserver 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 
> asbinary(force_collection(force_2d(the_geom
> from (select flstk.the_geom, gm.flstk, fl.flstk, fz.flstk, fz.flstk, 
> fn.flstk)),'NDR'),OID::text
> from flstk) WHERE the_geom from (select flstk.the_geom, gm.flstk, fl.flstk,
> fz.flstk, fz.flstk, fn.flstk && setSRID('BOX3D(3478900 6080400,3479700
> 6081200)'::BOX3D, find_srid('','flstk)','the_geom from (select 
> flstk.the_geom,
> gm.flstk, fl.flstk, fz.flstk, fz.flstk, fn.flstk') )
> 
> any help welcome
> 
> Best Regards
> 
> Heiko Kehlenbrink
> 
> 
>