[postgis-users] reusing MapServer query-string

[heiko]

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