Improve performance in Mapserver on Postgis-Layer using FILTER (workaround for the TOAST-Problem)

[Leonhard Dietze]

Hi,

I have several layers with rather large (detailed)
Polygon-geometries in PostGIS. While searching for
possible solutions to improve the performance of
my mapping-application I read about the TOAST-
Problem. (I am using MapServer version 4.6.1 and
Postgres 8.0.1)

After further research I read about a possible
Workaround for TOAST in PostGIS: I created a new
column in my Postgres-Table where only the
BoundingBox (bbox) is stored and use this as a
filter-attribute while selecting the geometry
(See:
http://postgis.refractions.net/docs/ch05.html#id2789728
If I do this manually, then the query only takes
40% of the time needed before.

Unfortunately I do not know if (or how) it is
possible to tell the mapserver to use this "bbox"
for the filter. If I use the parameter
  'FILTER "bbox"'
in the map-file then this is inserted at the wrong
position in the SQL-Query String. The full
SQL-String can be found below together with the
explanation of the parameters.

The normal FILTER within the SQL-String looks as
follows:
"SELECT ... WHERE staat_geom_test && setSRID('BOX3D(137493 4817476,616506
5222523)'::BOX3D, 4326 );"

And with the FILTER-param from the map-file:
SELECT ... WHERE (bbox) and (staat_geom_test && setSRID('BOX3D(137493
4817476,616506 5222523)'::BOX3D,4326) );

But it should look like this:
SELECT ... WHERE bbox && setSRID('BOX3D(137493 4817476,616506 5222523)'::
BOX3D, 4326 );

Can somebody tell me if this is possible or
do I have to dig into the source code?

Thanks in advance.
Leo


--------------------------------------------
FULL SQL-Query String:

- "staat_geom_test" is the column with
  the full geometry
- "bbox" is the column with the BoundingBox

SELECT
h::text,asbinary(force_collection(force_2d(staat_geom_test)),'XDR'),gid::
text


from u_g1_1820 as foo WHERE bbox &&
setSRID('BOX3D(137493.827160494
4817476.39796659,616506.172839506
5222523.60203341)'::BOX3D, 4326 );

--------------------------------------------
Excerpt from the map-file

  LAYER
        CONNECTIONTYPE postgis
        NAME          laender_33n_1820
        CONNECTION    "user=xxxx
       password=xxxx dbname=hgis_germany host=xxx"
        DATA          "staat_geom_test from
u_g1_1820 as foo using unique gid using srid=4326"
        STATUS        OFF
        TYPE          POLYGON
        TRANSPARENCY  95
        DUMP          true
        CLASSITEM  h
        CLASS
              EXPRESSION /2/
              NAME    "Länder innen 1820"
              COLOR   255 235 190
              OUTLINECOLOR 150 150 150
              SIZE    8
              TEMPLATE "templates/tpl_combined.html"
        END
        CLASS
              EXPRESSION /5/
              NAME    "Länder 1820 besond."
              COLOR 204 204 204
              OUTLINECOLOR  235 235 235
              SIZE    8
              TEMPLATE "templates/tpl_combined.html"
        END
        CLASS
              NAME    "Länder aussen 1820"
              COLOR   235 235 235
              OUTLINECOLOR  235 235 235
              SIZE    8
              TEMPLATE "templates/tpl_combined.html"
        END
        METADATA
              "title"     "Länder 1820"
              "wms_name"  "laender_33n_1820"
              "wms_title" "Länder 1820"
              "wms_srs"   "EPSG:4326"
              "wms_server_version"  "1.1.0"
              "wms_format"   "image/png"
        END
  END



* * * * * * * * * * * * * * * * * * * *
Institut i3mainz
Fachhochschule Mainz
Leonhard Dietze
Holzstraße 36
55116 Mainz
Tel.: 06131-2859-686
dietze at geoinform.fh-mainz.de
* * * * * * * * * * * * * * * * * * * *