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

[Stephen Woodbridge]

Try adding FILTER "t" and see if that fixes the issue. If it does, 
please write it up as a bug.

-Steve W

On 6/7/2017 3:27 AM, Jörg Thomsen (WhereGroup) wrote:
> Hi,
> 
> had the same problem a few weeks ago. The solution was to fix the FILTER
> or EXPRESSION within the classes. I don't remember exactly...
> 
> Jörg
> 
> 
> 
> 
> Am 07.06.2017 um 08:45 schrieb 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!
>>
>>
>> _______________________________________________
>> mapserver-users mailing list
>> mapserver-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/mapserver-users
>>
> 
> 
> Viele Grüße,
> Jörg Thomsen
> 


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus