[mapserver-users] PostGIS performance

[Alexander Pucher]

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