[mapserver-users] PostGIS performance

[Alexander Pucher]

I think the problem is in the use of the EXPRESSION with a PostGIS 
layer. In the documentation I found a line, saying that CLASSITEM is not 
used in a PostGIS layer. One has to use the field name in the EXPRESSION 
tag!!

I presume it means the DB filed name of the query result set ?!?

So instead of using

CLASSITEM "myclass"
CLASS
   EXPRESSION "1"
   COLOR 255 0 0
  END

I might to do something like

#CLASSITEM "myclass"
CLASS
   EXPRESSION "myclass = 1"
   COLOR 255 0 0
  END

...but still no correct result.

I'm not sure if I use the EXPRESSION is correct in this sense. Has 
anybody already used CLASSES with EXPRESSIONs in combination with 
PostGIS and can tell me the right syntax?

Best regards,
alex


Jan Hartmann wrote:

> Your mapfile looks good and I double-checked my own mapfiles, so this 
> is the right way. However, are you using MapServer CGI? In that case 
> you have to set "STATUS DEFAULT" in the layer section; "STATUS ON" 
> leaves the layer invisible. This really should be put somewhere in the 
> docs with extra big capitals. -:)
>
> Jan
>
> Alexander Pucher wrote:
>
>> Hi Jan,
>>
>> everytime I read one of your mails I think yes, yes, this must be 
>> it...but still no success ;-(
>>
>> Jan Hartmann wrote:
>>
>>> Hello Alex,
>>>
>>> Try the inner select from a terminal (select nut3. ...). This should 
>>> give back records with three fields, so you can check whether your 
>>> selection criteria are right. If I read the description of your data 
>>> right I would say that the where-statement would have to be:
>>>
>>> where nut3.nurgcd = un3rt.nurgcd
>>
>>
>>
>> Sorry my fault: the correct version _is_ where nut3.nurgcd = 
>> un3rt.geo so the line in the DATA statement is correct.
>> The inner selecion works fine with three fields, the_geom, oid and 
>> myclass. Also the complete query works fine from a terminal, as I 
>> told already.
>>
>>> However, if your original line is right and you get back records for 
>>> all classes with the interactive select,  something is wrong with 
>>> the MapServer interface. Try quoting the SQL case statements:
>>>
>>> case when ... then "1"
>>
>>
>>
>> Tried it out with no success. It' funny that when I remove all but 
>> the first class and comment out the EXPRESSION line like this,
>>
>> CLASS
>>   # EXPRESSION "1"
>>    COLOR 255 0 0
>>   END
>>
>> it should draw all polygons in red, but still it doesn't. It seems to 
>> ignore the whole layer!!
>>
>> Is there something wrong with my EXPRESSIONs? I even tried it with 
>> 'one' instead of '1' and changed the regex to /one/ . Also tried '1' 
>> and ([myclass] = 1)
>>
>> Is there something stupid missing in my LAYERs section of the mapfile?
>>
>> Regards,
>> alex
>>
>> ############################
>>  # Start of layer definitions
>>  ############################
>>
>>  LAYER
>>    NAME "nut3"
>>    STATUS ON
>>    TYPE POLYGON
>>      CONNECTIONTYPE POSTGIS
>>    CONNECTION "user=xxxx password=xxxx dbname=xxxx host=localhost"
>>      DATA "the_geom from (select nut3.the_geom,nut3.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"
>>  
>>      METADATA
>>      "DESCRIPTION"    "NUTS3 Area polygons"
>>    END #METADATA
>>       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
>>    END #LAYER
>>
>>
>>  LAYER
>>    NAME "nut3lin"
>>    STATUS ON
>>    DATA "the_geom from nut3_arc"
>>    #DESCRIPTION "nut3lin"
>>    TYPE LINE
>>    CONNECTIONTYPE POSTGIS
>>    CONNECTION "user=pucher password=mapserver dbname=geodb 
>> host=localhost"
>>    METADATA
>>      "DESCRIPTION"    "NUTS3 boundaries lines"
>>    END #MEATDATA
>>      CLASS
>>      #NAME "Nuts3 Gebiete"
>>      COLOR 0 0 0
>>    END #CLASS
>>  END #LAYER
>>
>> END #MAP
>>
>>>
>>>
>>> Perhaps this solves it, although I thought you could pass number 
>>> values unquoted.
>>>
>>> Jan
>>
>>
>>
>>
>>
>
>
>
>


-- 
________________________________________________________

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