[mapserver-users] error with SQL query

[Humberto Cereser Ibanez]

Hi Luca,

Em Ter, 2013-11-12 às 13:59 +0100, Luca Delucchi escreveu:
> Hi all,
> 
> I'm trying to use a "complex" SQL query on my layer but it return an
> error.
> My definition on the mapfile is
> 
> grid_5x5.geom, grid_5x5.gid, count(occurrence.specie) AS count FROM
> (occurrence JOIN grid_5x5 ON (st_intersects(occurrence.wkb_geometry,
> grid_5x5.geom))) WHERE specie='ursarc' GROUP BY grid_5x5.gid as
> subquery USING UNIQUE grid_5x5.gid USING UNIQUE 25832
> 
My guess is for rewrite your definition: 
...
FROM occurrence JOIN grid_5x5 
ON st_intersects(occurrence.wkb_geometry, grid_5x5.geom) 
and specie='ursarc'
...
Also, if you have spatial index for wkb_geometry and geom, the &&
operator will decrease resource consumption. Your query would be
something like that:
ON st_intersects(occurrence.wkb_geometry, grid_5x5.geom)
and (occurrence.wkb_geometry && grid_5x5.geom)
and specie='ursarc'

> and it works well on postgis.
> The error returned is
> 
> msDrawMap(): Image handling error. Failed to draw layer named
> 'orsobruno'. msPostGISLayerWhichShapes(): Query error. Failed to build
> query SQL. msPostGISBuildSQL(): General error message. Failed to build
> SQL 'where'.
> 
> Someone can help me?
> 
> thanks
> 
> -- 
> ciao
> Luca
> 
> 
Arrivederci,

Humberto Cereser Ibanez