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 [EMAIL PROTECTED] * * * * * * * * * * * * * * * * * * * *
