[mapserver-users] Mapserver 7.0.5, PostGIS and Query Errors

[James Colannino]

Hi everyone,

I have a problem that's got me stumped.

I have the following layer defined in a mapfile:

LAYER

    NAME "aggregates"
    TYPE POLYGON
    CONNECTIONTYPE POSTGIS
    CONNECTION "host=localhost port=5432 dbname=*** user=*** password=***"
    DATA "geom FROM (SELECT b.geom AS geom, a.peril_id, a.portfolio_id,
a.tiv, a.thematic_tiv, a.aggregates_id FROM schema.aggregates a INNER JOIN
gisdata.adm_1 b on a.gid = b.gid) AS c using SRID=4326 using unique
aggregates_id"

    PROJECTION
        "proj=longlat"
        "ellps=WGS84"
        "datum=WGS84"
        "no_defs"
    END

END

This works perfectly on Mapserver 6.4, but on 7.0.5 it's failing (for what
it's worth, I compiled 7.0.5 myself because my OS didn't have binary
packages for it that supported php5-mapscript.) I turned on debugging and
watched what kind of errors I was getting whenever I made tile requests.
Here's what I found:

[Wed Jun  7 03:42:43 2017].502983 msDrawMap(): rendering using outputformat
named png (AGG/PNG).
[Wed Jun  7 03:42:43 2017].503018 msDrawMap(): WMS/WFS set-up and query,
0.000s
[Wed Jun  7 03:42:43 2017].523359 msPostGISLayerWhichShapes(): Error
(ERROR:  syntax error at or near ")"
LINE 1: ...2300166285,129.462890606979 36.6292300166285))',4326) and ()
                                                              ^
) executing query: select "thematic_tiv",encode(ST_
AsBinary(ST_Force2D("geom"),'NDR'),'hex') as geom,"aggregates_id" from
(SELECT b.geom AS geom, a.peril_id, a.portfolio_id, a.tiv, a.thematic_tiv,
a.aggregates_id FROM portfolios.aggregates a INNER JOIN gisdata.adm_1 b on
a.gid = b.gid) AS c where geom && ST_GeomFromText('POLYGON((129.462890606979
36.6292300166285,129.462890606979 68.6423395678331,179.999999974944
68.6423395678331,179.999999974944 36.6292300166285,129.462890606979
36.6292300166285))',4326) and ()
[Wed Jun  7 03:42:43 2017].523377 msPostGISLayerWhichShapes(): Query error.
Error executing query. Check server logs
[Wed Jun  7 03:42:43 2017].523434 msDrawMap(): Image handling error. Failed
to draw layer named 'area_aggregates_tiv'.
[Wed Jun  7 03:42:43 2017].523927 msFreeMap(): freeing map at
0x560aa573b6c0.

That "and ()" at the end of the query causing the syntax error is very
strange. It's like it's adding on a blank filter. I tried making the query
as simple as possible, so I created another table that contained all the
required information and changed the DATA line to:

DATA "geom from portfolios.area_aggregates_join using SRID=4326 using
unique aggregates_id"

However, I still got the same type of error. I'm completely stumped. Does
anyone have an idea what could be happening? I've spent the past 4 hours
Googling with no success.

Thank you!
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20170607/1ca9793c/attachment.htm>