[mapserver-users] PostGIS performance

[Jan Hartmann]

Alexander,

You are right: MapServer opens a separate PostGIS connection for every 
layer, so your approach is inefficient. You can do it much more 
elegantly by using just one layer and putting all your filter conditions 
into the DATA statement. You do this by creating a computed PostGIS 
variable and giving it a name. This temporary variable can be used as a 
CLASSITEM by MapServer. Note that this is a very powerful way of 
creating classified maps, as you can make your computations as complex 
as you want, with variables from all tables in your database.


DATA "the_geom from
(select nut3.the_geom,
     case when un3rt.unit > 16 then 1
          when un3rt.unit > 10 and un3rt.unit < 16 then 2
          when un3rt.unit >  5 and un3rt.unit < 10 then 3
          when un3rt.unit >  0 and un3rt.unit <  5 then 4
          else 5
     end as myclass
     from nut3,un3rt
     where nut3.nurgcd=un3rt.geo
) as foo"


CLASSITEM myclass
CLASS
     EXPRESSION 1
     COLOR 255 0 0
END
CLASS
     EXPRESSION 2
     COLOR 250 100 100
END
CLASS
     EXPRESSION 3
     COLOR 250 200 200
END
CLASS
     EXPRESSION 4
     COLOR 250 220 220
END
CLASS
     EXPRESSION 5
     COLOR 210 210 210
END


Jan Hartmann
Department of Geography
University of Amsterdam
jhart at frw.uva.nl


Alexander Pucher wrote:
> 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
>