[mapserver-users] PostGIS performance

[Alexander Pucher]

Hi Jan,

I've changed the mappostgis.c file and compiled mapserver once again, so 
this works without any problems now.

Maybe I should say some works to my DB set up:

I have two tables in my DB:

'nut3' is a geometry table holding the polygon geometry of europ's NUTS 
3 areas. Each polygon has a unique identifier called 'nurgcd'
'un3rt' is a normal table holding the unemployment rates of the NUTS 
areas, connected to 'nut3' via the 'nurgcd' key.

The query you sent me worked more or less right away.

my DATA entry in the mapfile now looks like this:

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

You can see that I had to include the oid into the query, otherwise it 
didn't work.

The query works from a terminal and returns the geometry coloumn from my 
table nut3. Is it correct that it _only_ returns this coloumn. I thougt 
that the cases (as myclass) should also be in the result set, so that 
the mapfile can refer to it via the CLASSITEM tag?

My classes are like:

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
...

Still, the map I create with this mapfile is empty, i.e no polygons are 
coloured according to the resp. classes. It seems to me that the 
expressions in the various classes don't find matching patterns in the 
result set.

I think it's only a small thing to change to make it work, but I can't 
find it.

mfg
alex

Jan Hartmann wrote:

> 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
>>
>
>
>
>


-- 
________________________________________________________

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