[mapserver-users] PostGIS performance

[Alexander Pucher]

Hi,

this time I want to use the mapserv CGI simlpy to generate a choroplethe 
map for me, by sending a predefined mapfile to the mapserv executable 
and store the map in a tmp directory.

I want to show 4 classes of unemployment, colored depending on values in 
a db-table. The unemployment table is linked to the geometry table on 
the fly using FILTER in the mapfile.

To to so, I load the respective layer 4 times from PostGIS, and assign 
FILTERs to the them to select the relevant polygons.

Everything works fine, the map is created, only that it takes about 25 
sec. :-(

I played around a bit and found out that the repeated CONNECTIONs causes 
the delay.

Did I miss something in accessing layers fromPostGIS? With shapefiles, I 
would have used class expressions to "divide" the layer into classes, 
how can I do this without drawing the same layer over and over again 
when using PostGIS? AFAIK, I can't use the class expressions in this 
case, because I have to link two tables.

I added the mapfile for corrections ;-)

Regards,
alex



...

############################
  # Start of layer definitions
  ############################
 
  LAYER
    NAME "nut3"
    STATUS ON
    TYPE POLYGON
   
    DATA "the_geom from nut3"
    CONNECTIONTYPE POSTGIS
    CONNECTION "user=pucher password=xxx dbname=geodb host=localhost"
    METADATA
      "DESCRIPTION"    "NUTS3 Area polygons"
    END
   
   
    CLASSITEM "nurgcd"
   
    CLASS
      NAME "No Data"
      COLOR 210 210 210
    END
   
 
  END
 #------------------------------------------------------
  LAYER
    NAME "nut3"
    STATUS ON
    TYPE POLYGON
   
    DATA "the_geom from nut3"
    CONNECTIONTYPE POSTGIS
    CONNECTION "user=pucher password=xxx dbname=geodb host=localhost"
    METADATA
      "DESCRIPTION"    "NUTS3 Area polygons"
    END
   
    FILTER "nut3.nurgcd=un3rt.geo and un3rt.unit > 16"
   
    CLASSITEM "nurgcd"
    CLASS
      NAME "unempl. > 16%"
      COLOR 255 0 0 
    END
  END
   #------------------------------------------------------
  LAYER
    NAME "nut3"
    STATUS ON
    TYPE POLYGON
   
    DATA "the_geom from nut3"
    CONNECTIONTYPE POSTGIS
    CONNECTION "user=pucher password=xxx dbname=geodb host=localhost"
    METADATA
      "DESCRIPTION"    "NUTS3 Area polygons"
    END
   
    FILTER "nut3.nurgcd=un3rt.geo and un3rt.unit > 10 and un3rt.unit < 16"
   
    CLASSITEM "nurgcd"
    CLASS
      NAME "unempl. 10-16%"
      COLOR 250 100 100 
    END
  END
   #------------------------------------------------------
  LAYER
    NAME "nut3"
    STATUS ON
    TYPE POLYGON
   
    DATA "the_geom from nut3"
    CONNECTIONTYPE POSTGIS
    CONNECTION "user=pucher password=xxx dbname=geodb host=localhost"
    METADATA
      "DESCRIPTION"    "NUTS3 Area polygons"
    END
   
    FILTER "nut3.nurgcd=un3rt.geo and un3rt.unit > 5 and un3rt.unit < 10"
   
    CLASSITEM "nurgcd"
    CLASS
      NAME "unempl. 5-10%"
      COLOR 250 200 200 
    END
  END
   #------------------------------------------------------
  LAYER
    NAME "nut3"
    STATUS ON
    TYPE POLYGON
   
    DATA "the_geom from nut3"
    CONNECTIONTYPE POSTGIS
    CONNECTION "user=pucher password=xxx dbname=geodb host=localhost"
    METADATA
      "DESCRIPTION"    "NUTS3 Area polygons"
    END
   
    FILTER "nut3.nurgcd=un3rt.geo and un3rt.unit > 0 and un3rt.unit < 5"
   
    CLASSITEM "nurgcd"
    CLASS
      NAME "unempl. <5%"
      COLOR 250 220 220 
    END
  END
   #------------------------------------------------------
  LAYER
    NAME "nut3lin"
    STATUS ON
    DATA "the_geom from nut3_arc"
    #DESCRIPTION "nut3lin"
    TYPE LINE
    CONNECTIONTYPE POSTGIS
    CONNECTION "user=pucher password=xxx dbname=geodb host=localhost"
    METADATA
      "DESCRIPTION"    "NUTS3 boundaries lines"
    END
    CLASS
      #NAME "Nuts3 Gebiete"
      COLOR 0 0 0
    END
  END
END

-- 
________________________________________________________

Institut fuer Geographie und Regionalforschung
Universitaet Wien
Kartografie und Geoinformation

Departement of Geography and Regional Research
University of Vienna
Cartography and GIS

Universitaetstr. 7, A-1010 Wien, AUSTRIA

Tel: (+43 1) 4277 48644
Fax: (+43 1) 4277 48649
E-mail: alexander.pucher at univie.ac.at

FTP: ftp://ftp.gis.univie.ac.at
WWW: http://www.gis.univie.ac.at/karto
--------------------------------------------------------
Atlas of Eastern and Southeastern Europe: http://www.aos.ac.at
--------------------------------------------------------

M$ is not the answer. M$ is the question!
No is the answer -- Eric Naggum