[mapserver-users] PostGIS performance

[Jan Hartmann]

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

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"

Perhaps this solves it, although I thought you could pass number values 
unquoted.

Jan

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