4

I have the following layer defined in a mapfile:

            LAYER

                NAME "aggregates"
                #STATUS OFF
                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 in Mapserver 6.4. However, when I try to use this with Mapserver 7, I get the following errors logged when I try to generate tiles:

            [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.

I'm sure something's changed in the way I'm supposed to configure the mapfile, but I can't figure out what it is.

EDIT: I've tried eliminating the joins by creating a table with all the necessary data. In this case, I have:

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

Yet I still get the same error. That "and ()" at the end of the final query that's causing the syntax error is bothering me.

  • Are you sure that is the query that is failing. Your debug output doesn't seem to match. For example in your debug output "thematic_tiv" is the only field that shows up. Only thing I can think of that might be an issue is if you are passing in params. I don't see that though but I think you'd need to put in validation for them - see mapserver.org/cgi/runsub.html – LR1234567 Jun 7 '17 at 6:14
  • It appears to me that the debug output matches. I'll take a look at the link you provided. Thanks. – Crankycyclops Jun 7 '17 at 6:18
2

I figured this out. The mapfile wasn't the only thing influencing the rendering of tiles, there were also modifications to the configuration through PHP and Mapscript.

Whereas in Mapscript 6.4 I could do the following:

            $layer->setFilter('column=value')

I had to do this in Mapscript 7.0.5:

            $layer->setProcessing('NATIVE_FILTER=column=value')
0

Thank you for the question and the hint in your answer. I am also searching for the error you posted but without MapScript. But I also got the feeling that it has something to do with the FILTER resp missing FILTER Parameter in my Postgis-Layer ... syntax error at or near ")"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.