[postgis-users] reusing MapServer query-string

[heiko]

Jan Hartmann wrote:

> 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
>>
>>
>> ,
>
Wow, that was a really fast response,

thank you!
I could solve my problem following your advise.
But I also had to install the newest cvs-tarball of postgis.

Best Regards

Heiko